Use formatted excel template on output

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.

2024-02-29_19-33-45.jpg

2024-02-29_19-31-47.jpg

2024-02-29_19-36-17.jpg

Output

Accepted answers

All comments

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

2024-02-29_19-36-17.jpg

flying008

Hi, @marydjc

Have you recently updated your software version?

Bren_Spill2

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

marydjc

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

Bren_Spill2

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?

Gaurav_Dhama_

That preserve formatting is unreliable. You can use custom formatting in your excel. That way once data is populated your cells will be formatted as you require. Custom formatting is not overwritten by Alteryx so it stays.

This will be one of the best approach if you are dumping good amount of data in template. If it’s just some calculations then the method explained by @32bit I would be ideal.

marydjc

@Gaurav_Dhama_ that is what I've been trying to do, but once the output is generated, the formatting (headers, cell color) from the template is missed.

Gaurav_Dhama_

I think I mentioned it wrong. Conditional formatting is what I was referring to. Conditional formatting will stay, any other kind of formatting will get overwritten.

ShubhamAlt

@marydjc

Hello, I am also trying to achieve what you have been trying. Did you finally got any solution ?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703