Calling an Excel Macro in Alteryx without converting to VBS format

[mstockton]

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

[danilang · Accepted solution]

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

[urbot]

Better to set excel.app visble as false to save some time