Replace VBA to open/calc/save various excels

[nilsoleneumann]

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

> 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:

1. Use the "Directory" input tool to specify the folder path where the Excel files are located.
2. Use the "Dynamic Input" tool to read all Excel files in the folder.
3. 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.

4. Use the "Output" tool to write the results to an output file.

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

[aatalai]

@nilsoleneumann is there an api/connector you can use to get the data straight from the software, rather than needing to download it into excel and then open them?

[nilsoleneumann]

Good idea, but unfortunately this is not possible as the final values are not calculated within the software but in Excel itself.

The "data flow" is as following:
1. Master Data extracted from software to excel (each Excel has approx. 2-5MB file size)

2. Within Excel the formular are referencing the master data and performing complex calculations from formular to formular via various sheets (German Tax calculations)

3. Alteryx Workflow to extract specific data and analysis

4. Upload of Alteryx Workflow result to BI/visualization software

[aatalai]

hmmm, could you get the raw data into alteryx and then perform the calculations done in excel/vba in alteryx and then extract the specific information?

[cjaneczko]

---

@aatalai wrote:

hmmm, could you get the raw data into alteryx and then perform the calculations done in excel/vba in alteryx and then extract the specific information?

---

This sounds like the logical way to go. Only bring in the fields that are needed and recreate the calculations in Alteryx.