Alteryx tool cannot read in excel data that's newly generated by another Alteryx tool

[xqccc]

We have two alteryx tools: one to generate excel packages and another one to read the data from all the packs to summarize. The first one copied data to some of the tabs in excel package and the prebuild formula will calculate other tabs based on the data the first alteryx workflow copied in. And the second alteryx workflow will read from the tabs populated by formulas.

However, for all the excel packages we generated, we need to open those files and close them at least once. Otherwise, the second tool cannot read anything.

Does anyone ever have this issue and how did you solve it? Thanks!

[OTrieger]

@xqccc 
If I understand what you wrote, the thing is the minute that you export data into excel file, and then there are formulas that will use the new data, if you will not open the excel file, the new data will not flow into the cells with the formulas. I only assuming that this is due to the way how Alteryx is saving the data into the excel file. So the minute that you are importing the new file to Alteryx do not get the expected data. It was so long time, I do not remember what was the fix for it.

[alexnajm]

@xqccc you can try a step in between to open the file and close it (so the Formulas refresh) using either Python or possible Run Command. I wouldn't be able to build this solution for you, but I'd also consider replicating your Excel formulas in Alteryx so you can bypass Excel altogether

[abe_ibanez]

@xqccc ,

I have had this same issue in the past. You need Excel to open so that the data will cache and flow through the embedded formulas. The way I have addressed this issue is what @alexnajm mentioned. I normally use the Python tool to open each file, save it, and close it before moving on to the next step of the workflow. I have used openpyxl and xlwings to achieve this. For your example, I think xlwings should suffice.

Here is an example that should work: 

import xlwings

excel_app = xlwings.App(visible=False)
excel_book = excel_app.books.open('path/to/file.xlsx')
excel_book.save()
excel_book.close()
excel_app.quit()

Just make sure that Alteryx is done using the Excel files before Python opens them. I normally use control containers for that. 

Hope this helps

Edit:

If you have multiple Excel files, you can pass a list of them from your workflow into the Python tool and then use a for loop to iterate though the list. 

[Diederik_vanderharst]

@xqccc , see how this underneath goes, using the Python Tool. Make sure you give it just the field with file paths, or just the one file path... Let us know how you came to your solution!.

from ayx import Alteryx
import pandas as pd
import win32com.client
import time

# Read input from Alteryx
df = Alteryx.read("#1")

# Start Excel
excel = win32com.client.Dispatch("Excel.Application")
excel.Visible = False
excel.DisplayAlerts = False

# Loop over each file path
for index, row in df.iterrows():
    file_path = row['FilePath']
    
    try:
        print(f"🔄 Opening: {file_path}")
        
        # Open the workbook
        workbook = excel.Workbooks.Open(file_path)
        
        # Optional: delay to allow connections to settle
        time.sleep(5)
        
        # Optional: Refresh all data connections
        workbook.RefreshAll()
        
        # Optional: wait to ensure refresh completes
        time.sleep(5)
        
        # Save and close
        workbook.Save()
        workbook.Close(SaveChanges=True)
        
        print(f"✅ Refreshed and saved: {file_path}")

    except Exception as e:
        print(f"❌ Error processing {file_path}: {e}")

# Quit Excel
excel.Quit()