1

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.

Nemo
  • 1,111
  • 6
  • 28
  • 45
  • 1
    just a crossing all the t's thought -> do you have your Excel app set to trust VBA? – Scott Holtzman May 22 '12 at 18:15
  • 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 May 22 '12 at 18:27

1 Answers1

1

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.

Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72
  • Scott I made edits to my post. Please see above. Thanks for your guidance. :0 – Nemo May 22 '12 at 19:27
  • code works fine for me. check and make sure your path name is correct. Also, if you have a macro running on the workbook_open event, you may want to be a timer statement into the vbs code to wait for X seconds, so the macro in the workbook can process. Also, for future reference, your 2nd question seems like a different problem from your first. It's best practice to accept an answer to your question, than post a new question if it's another separate issue. – Scott Holtzman May 22 '12 at 19:39
  • Thanks Scott. The problem was I was forcing 'Close' in my vbscipt whereas the macro that gets executed under Workbook_Open automatically saves and closes. So I just had to omit oExcelWkb.Close True and it worked. Thanks. In reference to this thread, I wil be posting a new thread(new problem I ran into). Thanks again. – Nemo May 22 '12 at 20:19
  • + 1 For seeing this through :) @Nemo: `gets executed under Workbook_Open automatically saves and closes.` If you wouldn't have ignored my comment then we would have solved this earlier :) I ended up finally deleting my comment :) – Siddharth Rout May 22 '12 at 20:56