Alteryx Designer Desktop Knowledge Base
Definitive answers from Designer Desktop experts.Alteryx Run Command: Executing Excel Macros (VBA)
Alteryx does a great job of simplifying our business processes, eliminating the need to maintain, document, and use Excel Macros. However, for that one workbook with 100’s (or even 1000’s) of lines of VBA code + months of development behind it, we have a simple way to integrate that Excel Macro within your workflow. This can greatly ease the transition from Excel to Alteryx and save you rework or buy you time to convert the process.
What we will use:
- Alteryx’s Run Command tool
- VB Script to call our Macro
An example can be found attached at the end of this post. Simply extract in Alteryx and Open.
To implement is very simple – place the Run Command tool where you need the Excel Macro to run in your workflow. This example shows the most basic workflow.
The command we will be executing is CScript, which can run Visual Basic Scripts. We will need to provide it a script; a simple one is shown below.
VBA_Example.vbs
Option Explicit ' ----------- dim workbook_path workbook_path = ".\VBA_Example.xlsm" ' Place your workbook file here dim macro_name macro_name = "Macro1" ' Place your macro name here ' ----------- dim file_system dim full_workbook_path set file_system = CreateObject("Scripting.FileSystemObject") full_workbook_path = file_system.GetAbsolutePathName(workbook_path) ' File address housekeeping Dim ExcelProgram Set ExcelProgram = CreateObject("Excel.Application") ' Tell the script what Excel is ExcelProgram.Application.WorkBooks.Open full_workbook_path ' Open your workbook ExcelProgram.Application.Visible = False ' Open it in the background ExcelProgram.Application.Run "'"&full_workbook_path&"'!"¯o_name ' Run your Macro - this tells the Excel running in the background to find this workbook and macro. ExcelProgram.Application.displayalerts = False ' Do not show prompts since we want this to be automated - Could switch to True to get prompts ExcelProgram.Activeworkbook.Save ' Do not forget to save your work ExcelProgram.Activeworkbook.Close ' Close the workbookThis script will call “Macro1” from “VBA_Example.xlsm” – the macro, which is quite simple, puts the current time in the workbook.
Here is how the Run Command needs to be configured to call this script:
Write Source – 2 recommendations here:
- Save off your data (attached example).
- Insert the data you are going to use in the macro, into a tab on the workbook.
Read Results – again 2 recommendations:
- Read the resulting worksheet the Excel Macro modifies (attached example).
- Read the data you originally saved coming into the Run Command.
Command – CScript ; this Windows program executes VBScript files as if they were in the command line
Command Arguments – the filepath to your VBScript that calls the Excel Macro.
Working Directory – this could be left blank; however, it should be set to where the VBScript file is. The example uses the workflow directory since that is where it is.
Run Silent / Run Minimized – this should be set to match the True/False value in the VBScript below:
ExcelProgram.Application.displayalerts = False ' Do not show prompts since we want this to be automated - Could switch to True to get promptsThat’s all there is to it.
