We are aware of an issue with the Search bar. Please use Advanced search for the time being while we troubleshoot. Thanks for your patience as we work on improving the community!

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

Alteryx Run Command: Executing Excel Macros (VBA)

JoeL
Alteryx
Alteryx
Created

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
CharlieS
17 - Castor
17 - Castor

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!

Jordken
7 - Meteor

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

Paulteryx
11 - Bolide

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.

Darryl5280
10 - Fireball

This is common problem with coding with VBScript and Microsoft Office Programs like Excel that it works on your computer, but Microsoft Office Applications like Excel are not installed on a Server, and trying to get an Admin to install Microsoft Office Applications on a Server is like trying to get through a brick wall.

Paulteryx
11 - Bolide

@Darryl5280 Unfortunately that doesn't seem to apply in my case. The server does have Office installed, and it appears to be more of a session level issue with respect to the Alteryx Service layer rather than availability of Office applications. I might reach out to my IT department to see if this is a physics problem with respect Alteryx workflows executing in session 0 not having the ability to run other applications, or if it something they could grant permissions to.

abell_dt
7 - Meteor

@Paulteryx& @Darryl5280  - Is there a way to preserve VBA Code/Macros in .xlsm files on the server such that when the analytic app is done running and the .xlsm file is output to a desktop, the macro can run on workbook open? I've noticed my VBA code is not preserved while testing this on the desktop.

 

This might be a workaround should this solution above not work when running on the server as you seem to have both been stating. This is something I am currently exploring. 

Marcel_Gavrila
8 - Asteroid

Hello,

 

Is this working on Alteryx Server?

sean_bolte
8 - Asteroid

@Darryl5280  - couldn't agree more on the IT assessment. The way I've been able to obtain a way around this is to establish a logical user account tied back into my "usable" account ID since our IT considers Server environments to be non-managed environments (since network pushes to those environments don't happen, and System Admins are expected to act like SA's - and keep the environments updated re patches, etc.). We have then tied Office products to the Logical accounts since we don't in actuality have more than a single user utilizing the product suite. This was done under the auspices of Microsoft support engineers, so as to not toe any grey areas w/ them.  

caseyflynn
5 - Atom

Oh man, I need to spend some time with this.

Zaguls
6 - Meteoroid

Anyone ever get this working on the server side ?  Even with excel installed on the server Alteryx just goes into an infinite loop after the vbs file.

someotherguy
5 - Atom

All my VBA macros are stored in PERSONAL.xlsb, Is it possible to use the macros stored there?

atchani_said1
5 - Atom

Thank you 

Very useful for me

DataGuy77
6 - Meteoroid

Another user trying to figure out if someone has this working on the Alteryx Server Side.  Works like a charm from desktop client.  I have been working closely with out IT team to determine if their is a workable solution.

 

Thanks for any support.

 

 

kureka
6 - Meteoroid

hi, did anybody run VBA stored in another Excel file or directly from vbs script? I think that's what @atchani_said1 needed.

 

I want to autofit columns after the render tool (which creates a new file every time, it doesn't allow to overwrite the output and store vba).

 

The table columns width expands across the render setting -  multiple sheets or across vertical tables. I also couldn't make conditional formatting work in the table tool (with dynamic/ unknown fields). That's why, VBA is essential. Any similar use case examples would be appreciated. Thanks.

 

dsakash
5 - Atom

Dhiren
5 - Atom

Hi, I am new to Alteryx and find this very forum very useful.

I tried using this method and it works when i do not have parameters to pass to VB Script file. When i use arguments, i am getting the strange error message.

Error: Run Command (15): The external program "cscript" returned an error code: 1. Any post which shows how to invoke vb script with more than one arguments?

 

I could pass multiple argument through this command. However can someone please look at the code and advise why the Command Arguments does not accept variables?

ck2019
8 - Asteroid

@kureka - hi i just wondered if you managed to get a macro running to do autofit etc? Something that I was interested in doing, but again wasn't sure how to refer to the dynamic input of each file created unless you do a batch macro of the run command somehow?

kureka
6 - Meteoroid

Hi @ck2019, I couldn't make VBScript to work on VBA saved in another Excel file (triggered by Alteryx). It was running without any errors but it couldn't execute Application.Run for some reason. It should be possible, I just haven't found Visual Basic expert to look into it.

 

The workaround I applied was to run the formatting VBA from a personal workbook instead - the first time end users opened Render Excel output file. 

 

 

someotherguy
5 - Atom

@kureka can you explain more about how the the VBA runs from the personal workbook.  I have two scenarios where I use VBA scripts stored in my personal workbook after various workflows:

  1. auto format column width and height, bold the first row, and set the text size to 9, apply specific format to numbers and currency columns - monthly I run 54 different files thru the same workflow and have to run this VBA for each file
  2. create pivot table with custom formatting for numbers and currency - run weekly/monthly after workflow

 

Subh
6 - Meteoroid

Great Content but Unable to retrieve data from Essbase Hyperion multi-dimensional cubes. The above example is small in nature where it is pulling up time and paste in a defined cell. Can we get 'Boolean' feature on "cscript" where it will pull any how. Any thoughts Re: Alteryx Run Command: Executing Excel Macros (VBA)