when running vba script, getting error that macro is not available or all macros may be disabled-CodePudding

I have several scripts that were working fine until recently. They were running through a task scheduler and when the task scheduler opens the script it gets the following:

line 20 is this:

'Execute Macro Code
  ExcelApp.Run MacroPath

here is the full script:

'Input Excel File's Full Path
  ExcelFilePath = "C:\vba-files\task_sched\task_modules.xlsm"

'Input Module/Macro name within the Excel File
  MacroPath = "Module1.get_data"

'Create an instance of Excel
  Set ExcelApp = CreateObject("Excel.Application")

'Do you want this Excel instance to be visible?
  ExcelApp.Visible = 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

macro settings are enabled as seen here:

when the workbook is opened and the macro is ran manually it works fine. it is just erroring out when ran through the script

CodePudding user response：

I cannot understand how could it work fine before... Basically, your VBScript code should fully qualify the procedure to be called. I mean, it also needs the workbook name (or full name) where the macro to be called exists. Please, try:

 ExcelApp.run "'" & ExcelFilePath & "'" & "!" & MacroPath

"'" characters make the code running well even if the file path contains spaces...

In theory, using the workbook full name will automatically open the workbook keeping the macro, if not open... I did not test it on automation from VBScript. It works in this way when make the call from Excel (tested)