Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! 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

 

22 REPLIES 22
pedrodrfaria
13 - Pulsar

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.

pedrodrfaria_0-1609881901292.png

 

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

pedrodrfaria_2-1609881953925.png

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

 

pedrodrfaria_1-1609881942623.png

 

Pedro.

 

DavidP
17 - Castor
17 - Castor

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"

NeilFisk
9 - Comet

David,

 

In the example I posted, The file name is defined in the Write to File or Database field and the tabs (sheet name) are generated in the Take File/Table Name From Field.  The entire string is not contained in the field, only the names for each of the tabs.  I am taking a set of data that contains a lot of records and breaking it into an Excel file that splits those records into separate tabs (sheets).  In this case, all the tabs that I create would have the same formatting applied.  

 

I have attempted to change the Write to File or Database from  .\file.xlsx||||Sheet1 to .\file.xlsx||||Sheet1$A1:Q while leaving the Take File/Table Name the same as it is in the example, but the formatting is not preserved.  Changing the field to include the range will also change the names of the tabs that are created.

 

Regards,

Neil

DavidP
17 - Castor
17 - Castor

Hi @NeilFisk 

 

I'll have to play around with this and get back to you. I would normally use the option to replace the entire path, which contains the path, filename, sheetname and range and generate all of it in a formula tool before the Output data tool.

 

Something like, 

 

[path]+"\"+[filename]+".xlsx|||"+[sheetname]+"$A1:F"

 

One minor point - all these tabs already exist in the Excel file, right? With their formatting set? You're just divying up the data to go to the right sheets?

NeilFisk
9 - Comet

The tabs get created during the first run of this process.  Not only is it broken out into multiple tabs in a worksheet, there are multiple worksheets.  Each of these will be downloaded independently by staff, updated, re-uploaded, and merged back together into one file.  Doing this allows us to then generate tracking to determine how much of the project is complete, who is working what, and potentially where we may have bottlenecks.

abrasch
8 - Asteroid

I was about to ask the very same question as the post's subject. 

 

I'm also wondering it's possible to use the "Preserve Formatting on Overwrite (Range Required)" capability in combination with the "Take File/Table Name From Field" functionality? The hang-up seems to be that one cannot create a valid/dynamic range in the output path that will anticipate/match the result of the Take Name From Field.

 

For instance, if I have data that includes a "Region" field (e.g., A, B, C) and I want to output to a single Excel file with three worksheets, how could I format the root of the output path?
I have tried this:

C:\Users\NAME\Desktop\MyExcelFile.xlsx|||Region_$A1:Z99
with Preserve Formatting checked and Take Name from Field using the Region field.
The hope is to overwrite (but maintain formatting of) the existing worksheets Region_A, Region_B, and Region_C.

This throws an error, because as expected, Alteryx doesn't know to put the A, B, C region after the underscore and before the range's $.

Any suggestions are appreciated!

DavidP
17 - Castor
17 - Castor

Hi @abrasch 

 

What you have to do is to create a field like [Fullpath] before the Output Data tool that contains the entire path and range and use this field with the "Change entire path" option in the Output Data tool.

 

Something like:

 

[Fullpath] = "C:\Users\NAME\Desktop\MyExcelFile.xlsx|||Region_" + [Region] + "$A1:Z99"

 

You can obviously use more dynamic fields in the above formula for the path and/or filename if you don't want it all hardcoded.

abrasch
8 - Asteroid

Thanks for confirming that that is the best option @DavidP!

trettelap
8 - Asteroid

@DavidP Are you able to provide a packaged workflow of this by any chance? I am having trouble getting the formatting to stick and have tried your solution so I want to isolate the issue....

Labels