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!
Solved! Go to 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.
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.
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.
Thanks again for all your help!
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.