Triggering Excel Macros from the comfort of your Alteryx Workflow

I mean, without any context this may sound like a slightly strange thing to do, so lets start with a usecase, and it’s one I have stumbled across a few times now.

I want to create a beautiful looking output in Excel. I want my columns and rows to have plenty of padding to improve readability. I want to have the data in a table so that the user can filter the view. I want to make use of the conditional formatting options to make those negative trends more visible to my audience.

At present, it is very difficult (I’d even go as far as saying impossible), to generate a Excel output in this way using Alteryx and the reporting toolkit that is supposed to make this possibe.

In this blog I’m going to outline the approach I now use to get me that amazingly beautiful excel report that the execs at my company will love.

Let’s start by building the data

In this example I have built a workflow that is designed to allow our sales reps to understand what regions, under their ownership, are either over performing or under performing, compared to our global business and the geographic region to which the state sits within.

The workflow creates a bland excel output.

Creating your Excel Macro

Now that I have my output excel file generated, what I will do is record a macro, which performs the formatting of this output as I desire; if you are new to the subject of Excel macro’s, then you can read this post from Microsoft.

This video below shows the process of me formatting the document in Excel whilst the steps are being recorded to create a macro.

Once you have created your macro, either by this recording method, or via custom generation of the macro script, we should then save this file as an ‘Excel Macro-Enabled Workbook’ or ‘.xlsm’, I will also delete the original data sheet from the file.

The macro is now stored within our empty workbook.

Amending your Output

Now that you have your ‘.xlsm’ we must edit our output data tool to make use of this ‘template’ file as shown in the example below.

Something that is really important is that we do not use the ‘Overwrite File’ option, as this will delete our file, and the macro that exists within it, before then creating a brand new file just with our data table.

Once you have made this change, you can then run your workflow and generate your output. If you open the output, you can then navigate into your macros and run the macro you created to format the document, and voila, a nicely formatted output.

Automating macro execution using Alteryx

In the video above I show how it’s possible to execute the macro once you have opened your Excel file, but what happens if you want to do this automatically with Alteryx, so that when you open it after the workflow has completed, the file is already formatted as you desire?

Well we can do this through the use of the ‘Run Command’ tool in Alteryx, and a small script.

Let’s start with the script generation, we can take the script below and adjust it to suit our needs, but essentially this will open a file, run a macro (or in fact, any number of macros you may have), before saving and closing the file.

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = FALSE 
objExcel.DisplayAlerts = FALSE
Set objWorkbook = objExcel.Workbooks.Open("FULLFILEPATH") 
objExcel.Run "MACRONAME"
objWorkbook.Save
objExcel.Quit

In my case, the script looks something like this…

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = FALSE 
objExcel.DisplayAlerts = FALSE
Set objWorkbook = objExcel.Workbooks.Open("C:\Users\BenMoss\Desktop\Help\State Sales.xlsm") 
objExcel.Run "Format"
objWorkbook.Save
objExcel.Quit

What I want to do is generate and execute this script after our data has been written to our .xlsm file (it can’t happen before, obviously). To control this order of execution what I will first do is bring in a ‘Block Until Done’ tool prior to our output data tool.

I then take a single record from this stream (otherwise we’d end up with a script duplicated for the number of rows that exist in our data), before using a formula tool to build our script (this also allows us to make our script dynamic).

It’s then a case of writing and executing the script, which as I mention above, can be done with the ‘Run Command’ tool.

This image shows how we can use the .csv file format with a .vbs extension, this process is set as the ‘write source’, i.e. first write my data stream (in this case our script) to a file (in this case a .vbs file).

We can then execute this script using the ‘wscript’ executable, setting the path to our script as the command argument.

The ‘Run Minimized’ and ‘Run Silent’ options means that neither the wscript nor excel interface will open during this process.

And there you have it, an Alteryx workflow which triggers an Excel Macro, in this case in order to format our output into a jazzy looking report.

Goodbye

Thanks for reading this blog, if you fancy running this workflow and seeing how it works, you can download it here.

For it to work seamlessly you should first enable macro’s to run by default in Excel, this can be done by adjusting the macro settings as shown in this blog.