This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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
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
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.
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