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

[NeilFisk]

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

[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

[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

[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.  

[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! 

[rjepson81]

Thanks, we have suspected the batch macro route may be what we need to use, but had hoped to avoid it.  

[cpearse]

Are you able to provide an example of this? I am currently working on something similar and can't seem to fix the problem.

[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.