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