Running VBA Script in workflow

[rishabh_1995]

Hi Alteryx Community,

I'm trying to run a very simple VBA script in Alteryx to do some very basic formatting on the output Excel file I generate (which is simply to fix certain column widths & row heights). I'm rendering the output Excel file using the Reporting tools. Just wanted to understand how I can configure my workflow to run the VBA Script inside Alteryx itself rather than having to run it after opening the Excel file (I've saved the script in an xlam file).

I do know that probably the run command tool would do the trick, but I'm really not sure how to get that configured properly. Would be keen to know if someone could help me with that.

[AbhilashR]

Hi @rishabh_1995, the following Community post might be a good starting point for what you are looking to achieve: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Alteryx-Run-Command-Executing-Excel...

[BenMoss · Accepted solution]

I didn’t know there was a community article for this, as posted above, but I blogged this very usecase myself just a couple of weeks ago (formatting).

https://www.theinformationlab.co.uk/2020/04/14/triggering-excel-macros-from-the-comfort-of-your-alte...

Ben

[rishabh_1995]

Thanks @BenMoss 

I've configured the steps based on what's mentioned in the article (see picture below).

But I keep getting the following error message and I'm not sure what to do with it.

I've put the Macro in my personal macro workbook, and have doubled check to confirm that my macros are enabled. Would you happen to know why this error comes.

[BenMoss]

I’d advise opening the script that is written in the run command tool in notepad and trying to debug, the error you are facing there is not related to Alteryx, it’s related to the script you have built to execute, so it’s really hard for me to say.

In the example I share the macro is built into the same workbook as the file I am writing the data too, the way you worded your question suggests you have gone a different route and are storing the macro in a different workbook, you’ll need to adjust your script to account for this, no idea how.

With a client I am working with we have the macro stored in an empty file, the first step of the workflow is to execute a script which takes a copy of this file, with the macro built into it, then I write data to that file before triggering the macro in that workbook, might be an approach to consider.

Ben

[rishabh_1995]

Hi @BenMoss , I'm guessing that I would have to keep the Macro in a separate workbook. Reason for that being that I'm getting the output via the render tool and from what I've seen, the render tool doesn't support a .xlsm file format. Do you reckon the logic of creating the copy of the file with the Macro embedded would work? If so, would you mind sharing the script you've built for creating the copy of the file with the Macro in it? I would love to give that a shot from my end.

Worst comes to worst, I've also saved the Macro in a xlam file and can run the Macro with a click of a button on Excel. But would definitely love to explore whether I can get this to work inside Alteryx, as it's the first time I'm trying something out like this.

[BenMoss]

May I ask why you are using the render tool at all if you are using a script to apply formatting afterwards?

[rishabh_1995]

So I'm generating an Excel report which does a series of checks on a dataset. The final report comprises of a summary page, which gives an overview of the status of each test ("Passed", "Failed", "N/A"), and will also include several additional sheets for failed tests to provide details of the individual line items where the test has failed. I've done pretty much all the formatting for the Excel report within Alteryx itself, and everything looks like how I want it to.

The only challenge I have is that for the summary page, I'm not able to get the column width & row height sorted out in Alteryx (and I've looked quite a bit into this / checked with others as well, and there's no way to do it within Alteryx without potentially messing other elements up), and that is the only thing for which I want an extra layer of formatting using a VBA script.

Hope this clarifies my concern a bit more

[rishabh_1995]

Hi @BenMoss 

I had just leveraged the solution you had posted in your article for a different use case, where the entire formatting was done using VBA script. I know it's been a while since I wrote back on the post, but just thought of mentioning this as the solution you gave really helped out with the new situation I had.

Many thanks again !

[gggifaaa]

Hi Ben,

I also met a problem (shown in pic below) by trying to use the method you provide in the link:

https://www.theinformationlab.co.uk/2020/04/14/triggering-excel-macros-from-the-comfort-of-your-alte...

I wonder where goes wrong? Why can't alteryx read the Run Macro? (i didn't encrypted or set any read only mode)

thank you!