Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

Alteryx Run Command: Executing Excel Macros (VBA)

JoeL
Alteryx Alumni (Retired)
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_dup_544
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
8 - Asteroid

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
7 - Meteor

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
9 - Comet

@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
8 - Asteroid

@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) 

MARIV
5 - Atom

So this does not seem to work if your company has safety settings turned on, so that only macros with a digital signature can be run. Has anybody found any workarounds? Even if I add a digital signature to the macro, the macro is not run. 

venkata42
5 - Atom

Hi everyone,

 

Hope you are Safe and Good !

 

I need your suggestion in integrating Excel VBA Macro with Alteryx by using Run Command and without using .vba script file.

Can we call directly the Excel VBA Macro by giving the FIlename and MacroName.

 

Please let me know if any suggestions.

DylanDowrick
8 - Asteroid

Does anyone have any thoughts on how to update the FRED add-in within Excel using Alteryx? Is the Run Command tool sufficient?

aeolus187
8 - Asteroid

@JoeL 

if excel macro come with a popup form for inputting username/pwd, how could we process this in alteryx?

psubramanian
7 - Meteor

Hi @DataGuy77 ,

Any luck trying to execute it on the Server? I will be honest I am really saturated in finding the best possible solution as a workaround. Any luck with your IT team?

DylanDowrick
8 - Asteroid

@psubramanian There was a weird fix that our IT team had to implement on the gallery computer:

DylanDowrick_0-1667313443419.png

 

DylanDowrick
8 - Asteroid

.

guth05
6 - Meteoroid

We were able to get this to work on the server most of the time - once we loaded microsoft excel to the server and the empty folders mentioned above.  Every so often, it leaves excel open. The excel macro pulls data from a text file and then create various excel files, saves and closes each one after it creates them.  VBS script also saves and closes excel at the end.  Any idea why it would keep excel open on the server node sometimes but not other times?

DataGuy77
7 - Meteor

@guth05 one way to manage it would me to kill the Excel Process on the server at the beginning or end of the flow through the cmd tool.

GauravG1797
5 - Atom

Hello this really looks helpful but I get the following error while running the flow:-

 

"Open Method of workbook class has failed"  which is at line 23 of the VBA 

 

23#         ExcelProgram.Application.WorkBooks.Open full_workbook_path
               ' Open your workbook

 

Can you please help !! 

 

Run Alteryx Run: Using the Run Command to Automate Everything 

JoeL
Alteryx Alumni (Retired)

@GauravG1797  The line that error occurs is the first time excel is attempted to open in the script.

I would check to see if you have
1) excel is installed and licensed
2) the workflow is being run from yours, or anyone's, Windows login that is licensed to use microsoft office.

 

The workbook is overly simple, along with the macro, in the template I provided. it should not fail due to a variation in excel config.

HugoT
6 - Meteoroid

Hi @JoeL 

The workflow runs well in desktop app but it cannot execute .vbs file in server, is there any solution for server running case, thanks !

rtchegui
5 - Atom

Hi 

 

Whenever i attempt to run the abovementioned macro on Alteryx it uninstalls Alteryx in my machine, and i have reinstall everything from scratch. Has anyone face this problem. Any insight will be greatly appreciated.