Preserver Formatting on Overwrite to Excel with Take File/Table Name From Field

I have a workflow that at the end I output to Excel. I have selected the Output Options to Overwrite Sheet or Range and seen the option now to Preserve Formatting on Overwrite (Range Required). I also am creating multiple sheets using the Take File/Table Name From Field option. Unfortunately, I do not know how to configure the "Range" so that the formatting is preserved. I have conditional formatting in the Excel file that I would like to preserve, as well as more simpler formatting.

A simple example would be greatly appreciated.

Thanks,

Neil

Output

Accepted answers

All comments

pedrodrfaria

Hi @NeilFisk

This should be fairly simple. What is the range you are trying to output to?

1. In the output data select the to specify a range.

2. Specify the range you want to output it to. In this case I chose A10 to C40 in my excel. See below.

3. So it will update these fields highlighted in yellow.

Pedro.

DavidP

Hi @NeilFisk

As @pedrodrfaria illustrated, the range is defined by a cell range.

I note that you also use the Take File/Table name from field option. It may be necessary to specify the cell range in the field where you define the file name.

For instance, in the field where you define the filename, you could specify it in this format:

"c:\pathname\filename.xlsx|||sheetname$A1:F20"

or if you want to define the columns but include all the rows in the dataset:

"c:\pathname\filename.xlsx|||sheetname$A1:F"

BGallop

I have tried following the steps laid out here, but it does not appear be preserving the formatting.

I am outputting a sheet of data into Excel, which has "simple" formatting (like font size/colour, alignment and row height) and conditional formatting.

I am outputting to the Excel file like below:

The data itself is fine, but it does not keep the "simple" formatting. I have tried using a named range, this did not work. Currently using the cell references is the best option because it doesn't overwrite the column headers. I know that Alteryx doesn't paste values, but instead inserts rows into the Excel, but in this case what formatting is supposedly being preserved in this case?

Version: 2020.4.6.36807

Hopefully I'm being an idiot and this has a simple solution that I've just missed, but if someone could provide some insight on this that would be very helpful.

Thanks

Formatting not Preserved Workfow.yxmd

Packaging Data Quality Dashboard v2 - Copy.xlsx

DavidP

Here's a simplified example.

1. Create the template:

a. Create the header row

b. Select the header row an define a named range

c. Apply formatting to the rows and columns

2. Make sure your data set in Alteryx has exactly the same field names as the named range

3. Configure the Output data tool as follows:

Example attached

write to formatted excel.yxzp

rjepson81

I have been searching endlessly for a solution to use Preserve Formatting on Overwrite (Range Required) "and" Take File/Table Name From Field. I have had great success with using each option by itself, but when I try using the two options together the output file has the expected filename, but remains unformatted.

apathetichell

you need to change entire path to get this to work... it should be of the format: filename.xlsx|||sheet$range - where range includes the start and end of the range (ie A1:E5 - with headers or A2:E5 without headers)

rjepson81

I have tried this and still no success. My fear is I'm missing something minor, but can't see what it is. I created a small example to hopefully demonstrate what I'm seeing. The workflow has two outputs, one using the file path for the output file name and preserve formatting, and a second where it simply uses a template with the preserve formatting option. The latter option works just fine, but i can't seem to get the first output file to format correctly. Any insight would be greatly appreciated.

Format Output Test.yxmd

Sampledatafoodsales.xlsx

Office Template.xlsx

File Path Output.xlsx

Office Template Out.xlsx

rjepson81

Apologize, the sheet name shows "Office Supplies" in the template and output files, which doesn't make sense, but functionally this shouldn't matter.

trettelap

@rjepson81 try using a batch macro to feed in the name being changed...worked for me!

Guineapig

Format the Excel range you're outputting to as numbers and it will preserve the formatting when using "take file/table name from field" to output to a range. Would love to know if this works for anyone else or I'm just extra lucky.

jbinnig

Could you provide an example?

I'm using a batch file I run on the pre-run to copy my template to the following file.

Here is my text input value for the filepath, I attach to AppendFields control.

\\<FilePath>\<filename>_{0}.xlsx|||Sheet1$A2:AO20000

I also tried \\<FilePath>\<filename>_{0}.xlsx|||Sheet1$A2:AO

My Output Data is Configured as such.

File Format: Microsoft Excel (*.xlsx)

Output Options: Overwrite Sheet or Range

Append Field: Map By Field Name

Skip Field Names: True

Preserve Formatting on Overwrite (Range Required): checked

Take File/Table Name From Field: checked

Change Entire File Path:

Keep Field in output: tried both values

no dice.

jbinnig

I get a sheet must exist error with your example.

apathetichell

@jbinnig Try starting a new thread - but first - ummm... does your sheet exsit? If you have a template file and are copying it - you don't magically get a new sheet name. It copies the template file sheetname. So if there is not sheet1 there - there won't be a sheet1 in your new file. And you would then get that error that you are seeing.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703