Hi all,
after 3 years of frequently using Alteryx, this is the first time I was not able to find a already discussed solution for my problem in Alteryx.
What is the problem?
We have thousands of Excel files exported from a software tool. Unfortunately, the Excel files contain such complex data that the excel files need to be opened manually, wait for calculation to be finished, saved and closed before Alteryx can read the values from the files.
Currently, before starting the Alteryx process I use a VBA Macro which performs the following steps for each Excel in the target folder:
1. Open the Excel
2. Calculate and wait
3. Save
4. Close
For a batch of 1,000 Excel files the VBA Macro runtime was approx. 30hours and I try to improve the performance with Alteryx.
There was a similar, but not matching problem discussed here: https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Open-close-and-save-Excel-in-Alteryx/m-p/1199383#M297548
> Implement the VBA Marco in the Alteryx workflow would improve automation, but does not help with the runtime/performance.
This is what ChatGPT provided as a solution - which I unfortunately, were not able to get it running:
Sure, here is an example workflow you can create to incorporate the VBA script into your Alteryx process:
- Use the "Directory" input tool to specify the folder path where the Excel files are located.
- Use the "Dynamic Input" tool to read all Excel files in the folder.
- Use the "Run Command" tool to execute the VBA script. Enter the following command in the "Command" field of the tool:
"C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE" "<FullFilePath>" /x /e
Replace "" with the field that contains the full path to the current Excel file. The "/x" argument opens the file in the background, while the "/e" argument opens Excel in edit mode to calculate the formulas.
- Use the "Output" tool to write the results to an output file.
Use the "Dynamic Output" tool to save the output file.
By using Alteryx, you can also read the values from the Excel files after the formulas have been calculated. You can use the "Input" tool to read the Excel files and then transform and analyze the data with other tools in Alteryx.
I hope that helps!
What is needed:
Any Alteryx macro/run command or similar which can replace the VBA part of open/calc/save/close each Excel.
Unfortunately, I cannot provide an example excel due to sensitive data (the calculations itself in Excel are the IP of our company).
The workflow which reads the data from each Excel is already set-up, due to the problem above all values extracted are "0" in Alteryx.
