vbScript to add modules to excel file

dearnemo
dearnemo used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Does this work?

oExcel.Application.Run "RefreshDataFromIQY"
Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Author

Commented:
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.
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Author

Commented:
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.
Most Valuable Expert 2012
Top Expert 2012
Commented:
That is correct.  No need (and you can't) close a workbook if its already closed.

Dave

Author

Commented:
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!
Most Valuable Expert 2012
Top Expert 2012

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

Dave

Author

Commented:
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 LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial