community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Call VBA macro from Alteryx

Alteryx Certified Partner

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

 

rumcmd.PNG

 

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

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.

Meteor

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.

Alteryx Certified Partner

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

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

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

Capture.PNG

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

Meteor

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

 

Labels