Call VBA macro from Alteryx

[sscy]

Hi all,

I have a problem, which I hope you guys can help me :)

We use a VBA macro to update a number of SAP BO queries and saves them as text files (for an SQL DB). Alteryx does not seem to have access rights to update a SAP BO query directly, but we can update the queries using excel VBA which uses our windows credentials.

My questions is: is it possible to call an VBA macro through Alteryx ?

We have an Alteryx server, so we could fully-automate our data load, currently someone has to press a button every day and is not able to use Excel when the files are updating.   

I look forward to your answer.

Br,

Simon

[data_rachel]

@e0r00ouwhat sorts of problems are you having?

I "borrowed" and adapted the .vbs from a colleague. I named the code opeandrunmacro3.vbs. Here it is below along with the run command tool config. The .csv files are just dummy files for the sake of having something in the input and output fields. Macro3 is saved in the file named in Path1. An earlier part of the workflow outputs an Excel file that I want to run macro3 on. The vbs opens Excel, opens Path1, opens the last modified file in my specified folder, runs Macro3 on it, then quits Excel.

dim objExcel
set objExcel = CreateObject("Excel.Application")
objExcel.Visible = TRUE
objExcel.DisplayAlerts = FALSE

Path1="C:\Users\Documents\formattemplate.xlsm"

Set objWorkbook = objExcel.Workbooks.Open(Path1, 0, True)

Dim fso, path, file, recentDate, recentFile
Set fso = CreateObject("Scripting.FileSystemObject")
Set recentFile = Nothing
For Each file in fso.GetFolder("S:\reports\").Files
If (recentFile is Nothing) Then
Set recentFile = file
ElseIf (file.DateLastModified > recentFile.DateLastModified) Then
Set recentFile = file
End If
Next

If recentFile is Nothing Then
WScript.Echo "no recent files"
Else
objExcel.Workbooks.Open recentFile
End If

objExcel.Run("formattemplate.xlsm!Macro3")

objWorkbook.Saved=False

objExcel.quit

[alourie14]

Thank you everyone who contributed to this thread! Lots of good info.

I need to run VBA macros in the beginning and at the end of my alteryx process. I've adapted and simplified @data_rachel's process to fit my needs.

The .vbs file just runs the specific macro on the specific excel worksheet (which is the alteryx output). It also saves the excel spreadsheet.

Here it is, in case someone needs it:

Spoiler

dim objExcel
set objExcel = CreateObject("Excel.Application")
objExcel.Visible = TRUE
objExcel.DisplayAlerts = FALSE

Path1="M:\SCP Team\*\MACROS\Alexei_Alteryx\MacroWorkbook.xlsm"

Set objWorkbook = objExcel.Workbooks.Open(Path1, 0, True)

objExcel.Workbooks.Open "M:\SCP Team\*\Report\SSN Report.xlsx"

objExcel.Run("MacroWorkbook.xlsm!SSN_Pivot")

objExcel.ActiveWorkbook.Save
objWorkbook.Close
objExcel.Quit

Set objExcel = Nothing
Set objWorkBook = Nothing

Where "Path1="M:\SCP Team\*\MACROS\Alexei_Alteryx\MacroWorkbook.xlsm"" is the location of the workbook with the VBA macro and "objExcel.Workbooks.Open "M:\SCP Team\*\Report\SSN Report.xlsx"" is the Alteryx output file where macro needs to be run.
Instead of doing "Run Command" as a workflow tool, I've used it as an event in the events tab to run at the end of my workflow.

[RJS]

Hello Garrett

I got this command script to work after my workflow ran without errors to open my .xlsm files and convert the active sheets to a .pdf

Can you suggest a way to then have the workflow email the pdf in the same flow?

Seems the event window is more a terminating point

I think the run command tool may help but I'm not to sure how that would look in the workflow.

Thank you.

[Garrett]

Few potential designs come to mind:

1. Modify your VBA so it sends the email. plenty of online write-ups on this.
2. Add an additional "Send Email" Event. Might be hard to control order of operations.
3. Use Run Command tool instead of an Event. Then use Email tool or Event to send email. I find Run Command to be a tricky tool to configure, but allows you unlimited extensiblity. 
4. Run a second workflow/app that emails the results. Could probably accomplish this with an Event, a Runner/Conditional Runner macro, or maybe a chained analytic app.

[Tahir89]

Hi Garret,

In your Event you entered the "Command Arguments[Optional]:" as

"C:\Work\ExecuteExcelMacroFromAlteryx\ScriptFrom\Alteryx.vbs"

What does this line state?

Kind Regards,

Tahir

[alourie14]

Hi Tahir,

That line basically points to where the vbs file is stored (the file is used to run the VBA).
That line should be input here(using the screenshot posted above in data_rachel's post):

See Command Argument(Optional) there which is a full address of the vbs file used?

[RJS]

Id like to offer the VBS code I managed that will open my excel file which will "on open" .run the VBA in it and save the graphical tab as a PDF.  The VBS code then picks up the fresh PDF and emails it. All one workflow

LaunchMacro
Sub LaunchMacro()
Dim xl
Dim xlBook
Dim sCurPath
sCurPath = CreateObject("Scripting.FileSystemObject").GetAbsolutePathName(".")
Set xl = CreateObject("Excel.application")
Set xlBook = xl.Workbooks.Open("C:\Component Mtc\Report.xlsm",0)
xl.Application.Visible = True
xl.Application.run "SaveAsPDF"
xl.DisplayAlerts = False
xlBook.saved = True
xlBook.saveas("C:\Component Mtc\Report.xlsm")
xl.activewindow.close
xl.Quit
Set xlBook = Nothing
Set xl = Nothing

 
Set objEmail          = CreateObject("CDO.Message")
 objEmail.From       = "< your email >"
 objEmail.To           = "< your email >"
 objEmail.Subject    = "TEST Email" & vbTab & Now()
 objEmail.Textbody = "TEST Email" & vbTab & Now()
 objEmail.AddAttachment "C:\Component Mtc\Report.pdf"
 objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
 objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.delta.com"
 objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
 objEmail.Configuration.Fields.Update
 objEmail.Send

 End Sub

[tylancaster]

Really appreciate this information.  How/where is the VBS Script entered into Alteryx?

[CokeLuke]

I usually put it in Run Command tool or in Events. Examples above are using Run Command. Here is an Events example below: 

[mst3k]

i can write VB macros in excel, but i'm not familiar with the VBS...

it looks like we are using alteryx to run a VB script, that opens an excel, which has a VB macro in it.

but the excel macro is itself VB, right? can we skip launching an excel file and having to store the macro in it, and just put VB code straight into this .VBS file?

UPDATE: 

with some trial and error, i was able to at least get this setup working, where the VBScript launches a macro built into an excel file.

I was also able to pass some values through my workstream, so the code is flexible and reusable from within Alteryx. my use case was we have Alteryx output files daily that I want to make read-only. I was able to set up an alteryx workflow that would pass the target workbook name to my macro, so it's reusable. Also the alteryx code is flexible to have the macro filebook and macro name be enterable fields as well, so the whole set up is reusable for any other macros I may create.