vbScript opens up excel but doesn't load macro/modules?

Question

I m in a very weird situation. I created a vbs script that would open my excel file. I had defined vba code in WorkBook_open method. I thought creating a vbs script to open up my excel would invoke my workBook_open method and execute the vba code inside it. But I was wrong. Below is my vbs code.

filePath = "E:\data_extracts\mydata.xlsm"
Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Open(filepath)
oExcel.Visible = True
oExcel.Run "RefreshDataFromIQY"
oExcel.ActiveWorkbook.Save
oExcel.ActiveWorkbook.Close
oExcel.Quit
Set oExcel = Nothing

On debugging, it fails at oExcel.Run "RefreshDataFromIQY" saying either macros are not available or disabled. Hence it is the code just opnes up excel application successfully and that's all it does. I have macro codes in module1, module2. How/where do I write to execute my macros in vbs script below. My macros/modules have to be executed in sequence and some of my macros are recorded macros. Any help is much appreciated. Thanks.

Thanks for your input Scott. Here's what I made changes to my code

Dim oExcelApp
Dim oExcelWkb

set oExcelApp = createobject("Excel.Application")
set oExcelWkb = oExcelApp.Workbooks.Open("\\myserver\data_extracts\TestTOPTMay307.xlsm")
oExcelWkb.Close True
oExcelApp.Quit

However on running it from command line, its giving me runtime error Object required: 'Close'. Any idea why? Why is it failing to Close? What am i doing wrong? Thanks.

just a crossing all the t's thought -> do you have your Excel app set to trust VBA? — Scott Holtzman
2 more thoughts -. 1) you could try "E:\data_extracts\mydata.xlsm!RefreshDataFromIQY" or "E:\data_extracts\mydata.xlsm!Module1.RefreshDataFromIQY" 2)If "RefreshDataFromIQY" is a private sub / function, it may not work in this (probably doesn't apply to you, but just thought I would add it in — Scott Holtzman

Scott Holtzman Scott Holtzman · Accepted Answer · 2012-05-22T18:46:35

I just tested your code against a dummy file I made. It worked when I placed the code inside a module and left it as public. However, when I put into a private module -> like worksheet level module, I got the error you got.

However, when I referenced the private object, the code ran through. So my answer to you is to replace

oExcel.Run "RefreshDataFromIQY"

With

oExcel.Run "[yourClassName].RefreshDataFromIQY"

Also, I placed a workbook_event in my file as well. The event triggered successfully on open, so if there is trouble with yours, it's most likely in the code inside the event.

vbScript opens up excel but doesn't load macro/modules?

1 Answers