Calling an Excel Macro in Alteryx without converting to VBS format
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I want to be able to load a large number of files into an Alteryx workflow. However, they are currently in 1904 date format and I get an error when I try to input them. To get around this, I've created a macro in VBA to turn off 1904 dates for all files in a given folder. I'd like to be able to use this macro as an event at the beginning of my workflow run so that the files will load without an error. However, Alteryx events seems to only want to run Excel macros as .vbs file types, and VBA macros save as .bas types. Can I run a standard VBA macro via Alteryx events or do I need to convert to a VBScript?
In case it helps, my code is as below:
Sub OpenFiles()
Dim MyFolder As String
Dim MyFile As String
MyFolder = "C:\Users\username\Documents\1904 Date Macro"
MyFile = Dir(MyFolder & "\*.xlsx")
Do While MyFile <> ""
Workbooks.Open fileName:=MyFolder & "\" & MyFile
ActiveWorkbook.Date1904 = False
ActiveWorkbook.Close SaveChanges:=True
MyFile = Dir
Loop
End Sub
Solved! Go to Solution.
- Labels:
- Events
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @mstockton
The requirement for a vbs script is not imposed by Alteryx. VBS is the only way to automate an excel file without manually opening the file. That being said, you don't need to convert the existing macro to vbs, You just need a vbs shell to open the file, trigger the macro and the save the file.
'Input Excel File's Full Path
ExcelFilePath = "C:\Users\chris\Documents\My File.xlsm"
'Input Module/Macro name within the Excel File
MacroPath = "Module1.MyMacroName"
'Create an instance of Excel
Set ExcelApp = CreateObject("Excel.Application")
'Do you want this Excel instance to be visible?
ExcelApp.Visible = True 'or "False"
'Prevent any App Launch Alerts (ie Update External Links)
ExcelApp.DisplayAlerts = False
'Open Excel File
Set wb = ExcelApp.Workbooks.Open(ExcelFilePath)
'Execute Macro Code
ExcelApp.Run MacroPath
'Save Excel File (if applicable)
wb.Save
'Reset Display Alerts Before Closing
ExcelApp.DisplayAlerts = True
'Close Excel File
wb.Close
'End instance of Excel
ExcelApp.Quit
'Leaves an onscreen message!
MsgBox "Your Automated Task successfully ran at " & TimeValue(Now), vbInformation
*Sample from SpreadsheetGuru
Dan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Better to set excel.app visble as false to save some time
