vbScript opens up excel but doesn't load macro/modules?
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.
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
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.