Any tips for speeding up a batch workflow that copies excel data?

[jordan-shaw]

Hi all,

I have an Alteryx batch macro that copies inputs from a list of different input scenarios into an excel model, refreshes the excel, and copies the output from the excel model back into alteryx. The model works great however it takes a long time to run when there are a lot of batches. It seems what slows it down the most is my VB script that opens, refreshes, saves and closes the excel file. Wondering if anyone has any suggestions or tips to make this run faster. Or a different way of setting up my workflow entirely.

I made a simplified version of my workflow that I have attached where:

1. The first box overwrites my existing excel file/model with new inputs. This excel file takes inputs and performs a calculation.

2. In order to get the calculation from the excel model I must run a script to open the excel file, refresh it and close it.

3. The output from the calculation gets copied into a new file. - when I run this in batch essentially all my output is copied to a list corresponding to each input scenario. 

At a high level the below circled piece is the part of the workflow that is slow when I have many batches to run. The excel file that I actually use is also a fairly slow file (as there are a ton of formulas and calculations). 

This is the the VB script I run to refresh the file with the new input from the first step.

'Set fso = CreateObject("Scripting.FileSystemObject")
Set xl = CreateObject("Excel.Application")

xl.Visible = False
xl.DisplayAlerts = False
xl.ScreenUpdating = False

Set wb = xl.Workbooks.Open("' + [Engine.WorkflowDirectory] + [FilePath] + '")

wb.RefreshAll
wb.Save
wb.Close


xl.Quit'

Any help or insight is greatly appreciated!

[gabrielvilella]

Well, to make this process faster just remove every step that involves Excel. Make all calculations in Alteryx. Have Alteryx write the final file. 

[jordan-shaw]

That is the plan down the road. The team that owns the excel models is working on that but it will be at least 1 year out before they do that.  So in the meantime this is the best we got ( : 

[jordan-shaw]

Hi all, just bumping an old thread to see if anyone has any other ideas? Thanks 😊 !