Input Tool - Reading A .XLSX File - Returning Null On Cell References If Never Opened

[CGIQV]

A bit of a complex situation I'll try to explain.

I have a batch script that takes a .xlsx protected template, copies and duplicates it x # of times. It will rename the file according to data within an Alteryx workflow.

The next step is that each duplicated template has one of the sheets overwritten with unique data. The first sheet in the file is protected with a small section for users to modify. The second sheet is hidden and is populated with data from the workflow. The protected first sheet does cell references to the hidden sheet. This was my workaround to be able to create and populate at a mass level a protected template with all the proper visual formatting.

This all works great.

Except there is one issue I run into later. If I try to read in the file before it has been opened and modified, I'm seeing null values anywhere there was a cell reference. If I simply open the file, do nothing, and save it, the next time Alteryx tries to read it in it is able to see the values. But because the file is being generated from scripts and is never actually opened, it appears the cell references are not yet picked up by Alteryx when it reads the file the first time

Does anyone have any idea how I could possibly read this file without having to open it up, save it, and try again?

[CGIQV]

And additional problem I'm noticing is when the file is opened and a non-header value is updated, the dynamic input tool claims the schema has changed as well.

[PhilippK · Accepted solution]

Hi @CGIQV , 

interesting case. In case you really always have to open the Excel file first, you could think about including a "Run Command" tool within your Alteryx workflow.

Here are some examples:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Using-Events-or-Run-Command-to-open-am...

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Alteryx-Run-Command-Executing-Excel...

best regards

Phil

[CGIQV]

I figured this would likely be the only resolution.  I was curious if anyone else has ever noticed something similar.  But running a script to open and save will work if needed.