Overwriting Excel formula cells causes Excel file to open as Repaired version

Question

I have a Track Changes tab in my MS Excel file (Output.xlsx) which takes its inputs from another tab "Consolidation" in the same file. The latest update available in another Excel file (Input.xlsx) needs to be copied to the formula filled cells under the respective Quarters in the Track Changes tab. When the workflow tries to copy the data from the Input file to the formula filled cells in the Output file, I get the message prompts as shown below and the Output Excel file opens as a Repaired version.

I have attached the sample Input, Output and workflow for reference.

I am seeking help to ensure that the Output Excel file is intact and does not open as a Repaired version as the same needs to be used downstream for further operations.

P.S: I am using Alteryx Admin Designer Version:2020.2.3.27789, if that may be of any help.

Input.xlsx

Output.xlsx

Track Changes Workflow.yxmd

5abarish · Answer

Hi @danilang,

Yes, I find the Output Tool is not working as intended when it has to replace cells with existing formula.

So, I have found a work around by writing an Excel VBA Macro to overwrite the formula filled cells with values and the macro is triggered via a VB Script from the Alteryx Run Command Tool. This helps to get the job done without corrupting the Excel file.

I am not familiar with Python. I should probably give it a try too.

Thanks for hearing me out, Dan!

Regards,

Saba

danilang · Answer

Hi @5abarish

The Output Tool is very flakey when writing to Excel.  If you're inserting new data, it generally works fine.  If you're making changes to an existing workbook, there's a chance that the file will be corrupted.  I have a specific case at work, where even writing data to one sheet can corrupt the formulas in another.  For workbooks with existing data or complex formatting, I've recently switched from using the Output Tool to writing the output in a Python tool, to using pyopenxl in a Python tool.  The output is consistent and stable.  You can also write to multiple sheets by having multiple inputs stream to the tool and writing each stream to it's own sheet.

Dan

5abarish · Answer

Hi @danilang

Thank you very much for your response on this and suggestion.

I do not want the original formula to remain. My requirement is that the existing formula in that particular row needs to be overwritten with values via the Alteryx workflow.

The intention of this request is to find a solution where even if the existing cell with formula is overwritten with a value via Alteryx, the Output Excel file is intact and does not throw an error and create a repaired version.

Thanks,

Saba