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

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

Temp.xlsx

Accepted answers

DavidP

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

All comments

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'

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?

sblanchard

how do I set up conditional formatting in the Table Tool for column rule to format as percentage?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703