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 Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Running VBA Script in workflow

rishabh_1995
8 - Asteroid

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.

11 REPLIES 11
AbhilashR
15 - Aurora
15 - Aurora

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
ACE Emeritus
ACE Emeritus

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

Thanks @BenMoss 

 

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

 

rishabh_1995_1-1589142591385.png

 

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

 

rishabh_1995_0-1589142190428.png

 

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
ACE Emeritus
ACE Emeritus

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

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
ACE Emeritus
ACE Emeritus

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

rishabh_1995
8 - Asteroid

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

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
5 - Atom

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!

 

alteryx.JPG

Labels