Not able to execute macro from .vbs file

Question

I created a "Test" macro in Sheet1 of an excel sheet "Data" and trying to call it from vbs file

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\xxx\xxxx\xxxx\VB\Data.xlsm")

objExcel.Application.Run "C:\xxx\xxxx\xxxx\VB\Data.xlsm!Sheet1.Test" 
objExcel.ActiveWorkbook.Close

objExcel.Application.Quit

WScript.Quit

I am getting following error: The macro may not be available in this workbook or all macros may be disabled. Code: 800A03EC

I changed macro security to "Enable all macros" and also checked "Trust access to the VBA Project model" under Developer Macro settings. But still no luck

Answer 1

You can try it without Run, you can call your functions explicitly from workbook and sheets objects!

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\xxx\xxxx\xxxx\VB\Data.xlsm")
Set Sheet1 = objWorkbook.Sheets(1)
Sheet1.Test 
objExcel.ActiveWorkbook.Close

objExcel.Application.Quit

WScript.Quit

Answer 2

Thanks Guys, I found solution for the above problem. May be problem was, I am trying to run a macro without opening excel workbook, not sure though. Anyhow this is the working code. I am able to run macros from vbs now

Option Explicit
Dim xlApp, xlBook

Set xlApp = CreateObject("Excel.Application")
set XlBook = xlApp.Workbooks.Open("C:\xxx\xxx\xxx\xxx\Data.xlsm")
xlApp.Run "Sheet1.Test"
xlBook.Close
xlApp.Quit
Set xlBook = Nothing
set xlApp = Nothing
WScript.Echo "Finished"
WScript.Quit