Using Alteryx to change values in an excel model and copy the output

Question

Hi,

I am trying to automate a workflow and would like to use Alteryx instead of VBA to do it if possible.

Basically I have a Cost Model for a product that has been built in excel. This cost model takes about 15 different inputs from the user to calculate 10 different outputs/costs.

When using the cost model I will usually run many different scenarios of those 15 inputs. This can get tedious to manually input all these permutations and then copy and paste their outputs into a table report.

Is it possible to "Input" the scenarios of inputs I would like to run into Alteryx, then have Alteryx take those inputs and run them in the excel file and copy the output from the excel back into a a table in Alteryx.

Essentially I want to use Alteryx to handle all the scenarios of inputs, use excel for the calculations and use Alteryx to report the outputs.

Visual summary:

1. I upload a table with all the scenarios of inputs I want to run.

ScenarioInput 1Input...Input 151Yes 102No 20...

2. Alteryx takes inputs and plugs them into the Excel Cost Model.

3. Alteryx then copies the outputs from the excel model back into Alteryx.

ScenarioInput 1Input...Input 15Output 1Output...Output 101Yes 10100 3002No 20200 350...

Any insights on the possibility or perhaps the tools that would be required would be much appreciated. I know this looks like it may be easier to just run in VBA, however I have simplified the workflow here substantially for explanation purposes. In reality the workflow involves a number of separate cost models all with different kinds inputs and manipulations to the outputs afterwards. I see Alteryx being a better long run solution for automations here and ultimately these cost models will be transitioned from excel to Alteryx anyways.

Thanks!

csmith11 · Accepted Answer

Here is a high-level outline of what you'll likely need to do to accomplish this:

1.Update input Values in Excel Model

2. Refresh Excel File (This is important otherwise next step will copy unchanged results)

Uses VBS Script to Open Excel File and Run Refresh.

3. Read in Model Results

4.Write to Separate Output

Please let me know if you have any questions regarding anything in this example.

Attached is a zip File containing a proof of concept workflow.

csmith11 · Accepted Answer

With some quick changes I was able to tweak the proof of concept to be a little closer to your original request:

Note the results for each record in the below image:

1.Writes to Input Sheet.

2.Writes to VBS Script (This is also Executed, refreshing Excel Model)

3.Writes to Unique Output File

Please let me know if this give you a good starting point for your unique scenario.