Share variable(object) between multiple VBscript

Question

I would like to share some Excel macro with some collegues who dont know how to use it well.

I've never done anything in VBS but I've find the following .vbs to launch the macro :

Option Explicit

Dim xlApp, xlBook, oFSO, oShell, oExcel, oFile, oSheet
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False

Set xlBook = xlApp.Workbooks.Open("C:\Users\Desktop\API.xlsm")
xlApp.Run "getIEX"
xlBook.Close
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
WScript.Echo "End"

WScript.Quit

At some point of my Excel's macro I have a Userform on which I have a screen of a part of my Excel Table's sheet. On this userform I have 2 button : Auto-Save or Save Manualy.

if I press auto-save all is fine cause my Excel's macro keep going, save the file and then the vbs script close the unvisible excel workbook.

But, if they press save manualy (which end the Excel's macro), same thing happen... So I tried to call 2 others script from Excel VBA with this code :

    If ResponseX = vbYes Then
        Call savefile
        Shell "wscript.exe ""C:\Users\Desktop\UserFormYes.vbs"""          
    Else
        Shell "wscript.exe ""C:\Users\Desktop\UserFormNo.vbs"""
        Exit Sub

One if they press Auto-Save (to end the script) and another one for the manualy save which will make xlApp visible before quit the WScript.

The problem is : My object (xlApp) is not share between .vbs scripts.

The first .vbs script create the object xlApp and launch the VBA macro. Then the macro launch another .vbs script I would like to do 2 .vbs scripts : .First one if they press Yes :

xlBook.Close
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
WScript.Echo "End"

WScript.Quit

.Second one if they press no :

Set xlApp.Visible = True


WScript.Quit

How can I do this ? Is it possible ? Because currently, I have the error : Object Require : xlApp

Thanks :)

What does "save manually" do besides ending the macro? Could "save manually" mean to prompt the user for a path/filename/format and run `SaveAs` before returning? — Mathieu Guindon, Apr 01 '21 at 15:54
I would like the "save manually" buton will end both VBA macro and VBS script and make the Excel workbook visible. Then users could change what they want a save the excel files like they used to. — , Apr 01 '21 at 16:51
Why not just give them a workbook with a button in it, which they can press to run your code? vbscript launch and trying to pass control back and forth seems unnecessarily complicated. — Tim Williams, Apr 01 '21 at 17:20

Mathieu Guindon · Accepted Answer · 2021-04-01T17:10:33.017

2

It's not shared and it cannot be shared, the script and Excel are running in two different processes. The VBA code already has access to its host EXCEL.EXE instance ...that was spawned from the script.

So the VBA code accessing the global Application object is already using the very same xlApp object as in the script.

The problem isn't that xlApp isn't shared between the scripts; the problem is that the VBA code is returning when you don't want it to.

The script invokes a getIEX macro; when that macro returns, the script resumes.

I would question the need to involve VBScript in the first place, and make the macro-enabled Excel document expose an easy way for users to invoke that macro (e.g. attach the macro to some shape or button).

EDIT -

Now that we understand that there are two VBScript scripts involved that need to talk to each other (revision 1 seemed to be about sharing the xlApp variable with the invoked VBA macro), the problem is clearer, and the solution is for the second script to define its own xlApp variable and use GetObject to get that object reference from a currently-running process.

Dim xlApp
Set xlApp = GetObject("Excel.Application") 'watch out if you have multiple running instances!
Set xlApp.Visible = True
WScript.Quit

edited Apr 01 '21 at 17:10

answered Apr 01 '21 at 16:11

Mathieu Guindon

69,817
8
107
235

I dont need to share my Excel VBA variable with my script. I need to share xlApp ( object in my first .vbs script) with another .vbs script – Apr 01 '21 at 16:53
As explained, you can't do that, it's cross-process. Needs a different approach altogether. `GetObject("Excel.Application")` could work, but it's flakey... and quite clunky/frail tbh. – Mathieu Guindon Apr 01 '21 at 17:05
Oh ok.. The problem is "GetObject" will find the first "Excel.Application" already open... If there is no way to find an "Excel.Application.UnVisible" I will check if I could find something else. – Apr 01 '21 at 17:10
Yes. But the whole approach smells TBH, I wish I could see the bigger picture and assess what *actually* needs to happen, because this all seems like an X-Y problem to me (have problem X, but solve for Y). – Mathieu Guindon Apr 01 '21 at 17:12
@sartou see https://stackoverflow.com/q/30363748/1188513 – Mathieu Guindon Apr 01 '21 at 17:19
Ok thanks for all. Maybe I can just use the .vbs script to launch the VBA macro ! And then, from the macro close the UnVisible.Workbook or make it visible depending where the user clicked ? – Apr 01 '21 at 17:57
The VBA macro can make its host app visible by just doing `Application.Visible = True` itself, yes. – Mathieu Guindon Apr 01 '21 at 18:00
Perfect ! Thank you ! and I use "ActiveWorkbook.Close SaveChanges:=False" if they saved. But Since Excel close before the .vbs script end, I have an error message (800A9C68). Can I disable this error ? Edit : I did with "on error resume next" – Apr 01 '21 at 18:21

Share variable(object) between multiple VBscript

1 Answers1