asked on

vbScript to add modules to excel file

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. On debugging, it fails at oExcel.Run "RefreshDataFromIQY" saying either macros are not available or disabled. Hence it 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 have to be executed in sequence and some of my macros are recorded macros. Any help is much appreciated. Thanks.

filePath = "E:\data_extracts\TechOpsProjectTracker.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

Open in new window

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dlmille

MartinLiss's suggestion should work. However, I'd like you to return to your original comment:

>> 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.

You are correct. Opening an Excel Workbook SHOULD trigger the Workbook_Open() event.

Try creating a simple example - put this in your ThisWorkbook codepage of the file:

E:\data_extracts\TechOpsProjectTracker.xlsm

Private Sub Workbook_Open()
    MsgBox "I'm Open!!!"
End Sub

Open in new window

I just created a file testOpen.xls that just had the above macro in its ThisWorkbook code page and tested running from the desktop a vbscript that looked like this:

Dim oExcelApp
Dim oExcelWkb

set oExcelApp = createobject("Excel.Application")
set oExcelWkb = oExcelApp.Workbooks.Open("C:\Users\myID\Desktop\testOpen.xls")
oExcelWkb.Close False
oExcelApp.Quit

Open in new window

And it opened the file (in the background) and I got the message box Pop Up "I'm Open" prompt.

See attached examples.

Dave
testOpen.xls
testOpen.vbs

dearnemo

ASKER

Thanks for your i/p dlmille. It worked. Thanks again for explaination and clarifying my confusion. I took a reference to your code.

Dim oExcelApp
Dim oExcelWkb

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

Open in new window

However, while running it from cmd, its complaining runtime error Object required: 'Close'
Any idea? Thanks again.

dlmille

If your macro that kicks off on open, also closes the file, then that would create an error on line 6.

Can you confirm that the automated macro does not save itself and close already?

The reason I'm asking is that oExcelWkb object is instantiated in line 5, so if its no longer there by line 6, I'm starting to wonder.

Dave

dearnemo

ASKER

Yes my automated macro saves and closes itself on workbook open event. Does that mean there's no need to put oExcelWkb.Close True
in my code? I will try to see what I get if I comment this line out. I will post it here then. Thanks again for your help.

SOLUTION

dlmille

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dearnemo

ASKER

Yessss!!!! Thanks a looooot. You are a rockstar! :) I removed oExcelWkb.Close True and it worked w/o any errors. Thanks again! God bless you!

dlmille

You're welcome. Please remember MartinLiss's suggest was valid as well.

Dave

dearnemo

ASKER

Hi Dave, how do I accept MartinLiss's suggestions as well?

I have trouble running my vbs file from SQL Job. May be I should post it in a new forum/thread. Thanks guys!

Martin Liss

You click the 'Accept multiple solutions' button which has a little green checkmark. I requested Attention from a moderator for you.