How to append the data to excel without changing it's formatting?

[akshayhendre]

Hello All,

Is it possible to write to the excel sheet without changing it's formatting?

Attached file has two sheets, template and output, actually both are the different files at different locations.

Output sheet is a result of some workflow and template is the standard format in which I have to present the data, currently I am manually pasting the output to template sheet.

Is there a way in Alteryx to automate this manual task?

Thank you

[DavidP]

In release 2020.1 you can write data to a sheet while retaining the Excel formatting. This is from the Release notes:

You can now Preserve Formatting on Overwrite when overwriting a sheet or range via the Output Data tool Options. In order to preserve formatting, you have to:

• Select Overwrite Sheet or Range via Output Options.
• Specify cell ranges in the output file path.

Alternatively, you can use the Table tool in the Reporting tool palette and create row and column rules with conditional formatting. Then use a Render tool to generate an excel file.

[akshayhendre]

Thank you @DavidP for your reply.

How do I specify the cell address in output path?

I want the output from cell D4 onward so tried below options but getting an Error:

Output Data (97): Error creating table "Sheet1$D4": Microsoft JET Database Engine: 'Sheet1$D4' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

D:\Alteryx\ABC.xls|||Sheet1$D4

and 

D:\Alteryx\ABC.xls|||'Sheet1$D4'

[DavidP · Accepted solution]

You have to use xlsx format and then you have to specify a cell range, i.e. the start and end of the range. If you don't know the number of rows, just leave the number at the end of the range blank.

Some thing like will write to 5 columns of data as many rows as you have

D:\Alteryx\ABC.xlsx|||Sheet1$D4:H

[danilang]

Hi @akshayhendre ,

As a leader in the Alteryx Community, I have the ability to identify & mark accepted solutions on behalf of community members - and recently did so on this thread. If you have any questions or concerns with the solution(s) I selected please let me know by replying to this post.

As the original author, you also have the ability to mark replies as solutions! Going forward, I’d encourage you to identify the solution or solutions that helped you solve your problem, as it's a big help to other community members. Learn more about Accepted Solutions here.

Thank you!

Dan

[akshayhendre]

I wasn't having an access to my system hence unable to reply @danilang, @DavidP .

Coming back to the problem, currently I am having version 2019.3, any idea how to do it in this version?

I followed the steps given in previous posts but getting an error that 'Sheet Must exists' whereas the sheet is already present the workbook and I have given the path in a same you have suggested.

Thank you

[akshayhendre]

I got what I was looking for in below 4th post of below link, but thanks a lot for your help @DavidP, @danilang .

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Updating-Excel-data-regularly-to-exist...

[seven]

@DavidP is totally right about how to do this in 2020.1.

I feel hesitant here because an upgrade is not possible. Many users have a license through their company and must adhere to whichever version the company uses.

@akshayhendreYou are already maintaining two sheets and that's the first step. You are outputting the data into the Output sheet and that is the second step. The final step is done in Excel to automate this. Since this is a one to one copy paste, you can just use an Excel formula to link the data automatically.

=Output!A2

Your Template sheet's cells will have links to their counterparts in the Output sheet. As the Output sheet gets updated by Alteryx, these formulas will automatically populate the values in the cells in the Template sheet.

[sarahkelly]

@DavidP  Thanks for this tip. I followed your instructions. When I overwrite, the sheet order is now being preserved, but some of the column formatting is not. My set number of decimals stays the same in Excel, but I lose where I have formatted comas, dollar signs and percent signs by column. Do you have any idea what could be going on here?

[jemjem]

How does this work with multiple tabs? I tried it but it seems it asks for one particular tab name