Avatar of dearnemo
dearnemo
 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

Microsoft ExcelVB ScriptMicrosoft Development

Avatar of undefined
Last Comment
Martin Liss

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Martin Liss

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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.