Can Not Read Excel Data which Contains Formular

[zqlcancer]

In my case I need to use blob tool to create an excel output file based on pre-defined template at first and write data in this file. Then I use another Alteryx tool to read the data of output file.

However I find out that if this pre-defined template file includes formulars which  read data from other tab then Alteryx won't be able to read data from that tab. I need open and save the output file in order for Alteryx to read it.

For example, my attached workflow will generate an output file which include 2 tabs. Alteryx writes data to the tab "raw" and tab "output" is supposed to read data from tab "raw" based on the embedded formular automatically. However if I don't open and save the output file manually, Alteryx won't be able to read any data from tab "output" of the output file.

I am not sure if this is because the output file is generated by Alteryx automatically, hence I need open the file to refresh the data and save it for later processing. But is there any workaround to resolve this issue?

[flying008]

Hi, @zqlcancer 

Because Alteryx only exports data to Excel, it cannot control the calculation method of Excel (automatic or manual), and the calculation result of the formula needs to be refreshed. This process cannot be completed by relying on the Output tool. You can use the Run Command tool or Event to operate Excel to implement the calculation refresh process for the specified file. Later, you can use Alteryx to import this .xlsx file to get the final result data.

# Import Excel COM object
$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $false # Set Excel to be invisible

# Open the file
$Workbook = $Excel.Workbooks.Open("D:\123\abc.xlsx")

# Refresh all data connections
$Workbook.RefreshAll()

# Recalculate all formulas
$Workbook.Calculate()

# Wait for refresh and calculation to complete (optional, adjust the waiting time based on actual situation)
Start-Sleep -Seconds 5

# Save the file
$Workbook.Save()

# Close the file
$Workbook.Close()

# Release COM objects
$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Workbook)