community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

Alteryx Run Command: Executing Excel Macros (VBA)

Alteryx
Alteryx
Created on

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.

 

wf1.png

 

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&"'!"&macro_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 workbook

 

This 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:

 

run command.png

 

Write Source – 2 recommendations here:

 

  1. Save off your data (attached example).
  2. Insert the data you are going to use in the macro, into a tab on the workbook.

Read Results – again 2 recommendations:

 

  1. Read the resulting worksheet the Excel Macro modifies (attached example).
  2. 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 prompts

 

That’s all there is to it.

Attachments
Comments
Alteryx Certified Partner
Alteryx Certified Partner

A great write up to bookmark for when your Excel output must have formatting that isn't supported by the Reporting tools. Nice work, @JoeL!

Atom

I've got this tagged to read through closer later. I live in VBA and this should be most useful!

Fireball

Curious to know if this can work in the Alteryx server? I tried something similar but was having issues with the Excel not able to run since the Service Layer is running the workflow in Session 0 in the server environment.