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

CommonSense · Accepted Answer · 2016-12-28 13:08:52Z

3

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

edited Dec 28, 2016 at 13:08

answered Dec 28, 2016 at 11:07

CommonSense

4,5422 gold badges18 silver badges44 bronze badges

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

2 Comments

Ravi Adabala Over a year ago

Excellent!! Its working now. Thank you. Also I found other way by using run. I posted that code also and accepting this as answer

CommonSense Over a year ago

@Ravi Adabala, Hey, cheers! I knew about your other way, but I just getting nervous when there's a work with multi-app projects thru Run.

user692942 · Accepted Answer · 2016-12-28 11:49:42Z

1

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

edited Dec 28, 2016 at 11:49

user692942

16.4k8 gold badges85 silver badges196 bronze badges

answered Dec 28, 2016 at 11:35

Ravi Adabala

837 bronze badges

1 Comment

Shai Rado Over a year ago

you know you can run the MACRO without actually opening the Workbook, right? Which makes it faster

Collectives™ on Stack Overflow

Not able to execute macro from .vbs file

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related