Problem running Macros without opening the file [duplicate]

Question

I wanted to run macros using the command prompt, the macros are placed under a sheet in excel. I created a VBScript and let it automatically run in command prompts, but it shows "The macro may not be available in this workbook or all macros may be disabled."

This is my VBA code

enter image description here

This is my VBS

'Create Excel App Instance & Open Xlsm File
Set objExcelApp = CreateObject("Excel.Application")
objExcelApp.Visible = True
objExcelApp.DisplayAlerts = False

'Define Macro File & Path
sFilePathXlsm = "C:\Users\roeyj\OneDrive\Desktop\Intern\Monthly PM\Service Contracts as of 2022 (macro).xlsm"
Set iWb = objExcelApp.Workbooks.Open(sFilePathXlsm)

'1. Run 1st Macro in another Excel
sMacroToRun = "'" & sFilePathXlsm & "'!Sheet3.contract_vba"
objExcelApp.Run sMacroToRun


'Save & Close file
iWb.Save
iWb.Close
objExcelApp.DisplayAlerts = True
objExcelApp.Quit

This is what the command prompt shows

enter image description here

The excel file is opened but the macros don't run, I need help with this.

@braX, i have enabled all the macros settings in excel. Shouldn't be the setting problems, i think there is something wrong in my VBScript — LO ROEY JIA
– LO ROEY JIA, Commented Jan 18, 2023 at 5:31
Please do not post your code as images. No one can copy/paste it then. — braX
– braX, Commented Jan 18, 2023 at 5:34
If you want a macro to be available to any workbook, then put it in the personal workbook. — Solar Mike
– Solar Mike, Commented Jan 18, 2023 at 6:20
Try objExcelApp.Run "'" & iWb.Name & "'!Sheet3.contract_vba" — Tim Williams
– Tim Williams, Commented Jan 18, 2023 at 7:02

Tim Williams · Accepted Answer · 2023-01-18 07:08:23Z

1

Run doesn't want the full path, so try:

 objExcelApp.Run "'" & iWb.Name & "'!Sheet3.contract_vba"

answered Jan 18, 2023 at 7:08

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Problem running Macros without opening the file [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related