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

Question

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!

Diederik_vanderharst · Answer

@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()

abe_ibanez · Answer

@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.

alexnajm · Answer

@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