We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

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

NeilFisk
9 - Comet

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

 

Neil-Tsunami_0-1609861198262.png

 

19 REPLIES 19
BGallop
5 - Atom

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:

BGallop_0-1638468402241.png

 

 

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
17 - Castor
17 - Castor

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:

 

DavidP_0-1638921744765.png

 

Example attached

rjepson81
5 - Atom

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
18 - Pollux

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
5 - Atom

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
5 - Atom

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
8 - Asteroid

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

rjepson81
5 - Atom

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

cpearse
6 - Meteoroid

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
5 - Atom

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.

Labels