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

[jordan-shaw]

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.

Scenario	Input 1	Input...	Input 15
1	Yes		10
2	No		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.

Scenario	Input 1	Input...	Input 15	Output 1	Output...	Output 10
1	Yes		10	100		300
2	No		20	200		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 solution]

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 solution]

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.

[jordan-shaw]

Wow, this is great, thanks so much for putting this workflow together! Much appreciated.

I was playing around with it and it looks like this should be enough to get me started. I had 2 questions which are more for me to just further learn.

1. My excel model inputs do not all reside on the same worksheets and are not all grouped together (ex. one input might be in Cell A1 with another in Z21). What I may just do is just make a new Worksheet in excel that has all the reference inputs in one spot, structured similarly to what you had in your example workflow. With that being said if I was to set this up in Alteryx instead, would I essentially have to use multiple Input tools to update each individual input reference in the excel, then run steps 2 and 3 as normal? 
2. I am not too sure I understand the Parallel Block Until Done. I understand that we need to the previous workflow before we move onto the next, but I am not sure what the function of the Sample of First N=1 and Placeholder of 1 do exactly.

Thanks again for all your help!

[csmith11]

1. Your initial approach for solving this in Excel is exactly what I would do. A single sheet that each input references will be the easiest solution. Alternatively, you could write to each Individual Input using Alteryx leveraging the specific range then run steps 2 and 3 as normal. This is the much more difficult approach. You be able to simplify it by using a single Output Tool to write to multiple ranges. But you may have issues related to sheet formatting and header issues depending on your version of Alteryx. The initial approach will be faster to run and easier to set up.
2. The append field tool is configured to append Nothing. The append field tool only helps mange the flow of the workflow. It ensures no other record process until the previously connected "Block Until Done Tool" finishes running the 1st output anchor branch. The 2nd branch then flows to the append tool appending Nothing. 

How to Append Nothing? The sample 1 record is to ensure only 1 record of nothing is appended. The placeholder = 1 is just nonsense data to append because all other fields are dropped after this field is created. You need to append nothing but at least 1 Field needs to go into the S input Anchor of the append tool therefore I added a Placeholder field =1 total nonsense. This field is then Appended but not really because it is deselected on the append tool. Therefore, its appending Nothing.

There is a Parallel Block until Done Macro that uses a very similar approach:

http://www.chaosreignswithin.com/p/macros.html

Cheers!

Let me know if you have any other questions.

Sorry if my response is still very vague.