Use formatted excel template on output

[marydjc]

Hello,

I am trying to re-use a formatted excel spreadsheet and fill it with the data from the workflow. The output is setting the data on the cell I want to but the format from the template is lost once the Workflow has ran. The configuration I am using I have used it before with other workflows and it worked in the past but it suddenly it stopped working.

Is there a bug in Alteryx Designer preventing to re-use formatted outputs or is it my configuration that needs change? I am sharing my configuration.

Thanks in advance for any tip that would help me solve this issue.

[gawa]

hi @marydjc 

Please make sure that the option "Preserve Formatting an Overwrite" is checked in configuration of Output tool.

[marydjc]

Hi @gawa , that box is checked. Please see attached

[flying008]

Hi, @marydjc 

Have you recently updated your software version?

[Bren_Spill]

Hi @marydjc - looks like you haven't included a range in your file path which is required to use the preserve formatting functionality

[marydjc]

@Bren_Spill I have done that and it is still not working. My directory path also includes this range

[marydjc]

@flying008  yes I have the Version: 2023.1.1.336 Patch: 5

[Bren_Spill]

Given you are changing the entire file path in the output tool - you may need to build the range into the file path being fed into the tool.

Can you provide a packaged workflow for testing?

[32bit]

@marydjc I have been populating Excel templates as a primary output where I'm working since it is the preferred client format. I personally never allow Alteryx to output to an existing formatted worksheet because it causes more problems than it is worth and creates a maintenance nightmare for future updates. Here are the best practices that I have found which have been used for years of error free running and no support calls based on template errors:

• Create a template file with formatted tabs and one or more Alteryx data tabs. I will call that tab "AlteryxOutput" here. I normally try to keep only one tab for Alteryx performance and use creative ways to stack data that can be picked apart using formulas in Excel. You will have problems when you start getting a high worksheet count that Alteryx writes to.
• The AlteryxOutput tab should be data only. No formatting. Never put a formula on this tab or you will corrupt your Excel Calc Chain when Alteryx replaces the worksheet.
• Set Alteryx to replace the entire AlteryxOutput worksheet on outuput.
• All other tabs in the template should refer to AlteryxOutput using formulas or pivot ranges.
• Pivot tables that refer to AlteryxOutput should be set (right click, pivot options, data tab) to uncheck "Save source data with file" and check "Refresh data when opening file." 
  • Also, if using pivots be sure to stage data in AlteryxOutput which will preserve pivot filters from being lost, but not confuse clients with old development data if they open Excel with everything turned off. 

It's true there are other ways such as data connections or using powerquery, etc, but this is the best method I've found with the fewest steps for the client and least amount of file corruption issues and support calls.

[marydjc]

@32bit can you please share an example of your explanation?