On Open using Public Var from VBS?

[RayportingMonkey]

I create multiple reports through out the day and the source for some of
these reports comes to me through SharePoint. To that end, I have a VBS
script I call from Outlook when an email notification comes in telling me
that my source is available. This script streams the source from the
SharePoint URL to a predetermined location.

What I am theorizing about is a means of setting up a "Public Variable" in
the VBS Script that could be passed to the On Open event in a given Excel
Workbook which in turn, would run a specific macro to import the newly
acquired data.

I assume I would have to put a logic statement in the On Open event that
would test to see if this public variable has been set. If not, it would
simply end. But if the public variable is set, it would run the applicable
macro.

I am always trying to get to 100% automation where I can - is this even
possible?

I thank all the brilliant and capable people out there in advance!

Regards,
Ray

 

[RayportingMonkey]

Why does it seem that I can't ever find an answer until I post here?!

I solved this with the following VBScript:


Dim xlApp
Dim xlWkb
Const ForceExit = True

Dim WBPath
WBPath = "\\WorkbookLocation\Here\"
Dim WBName
WBName = "WB_Name.xls"

Set xlApp = CreateObject("excel.application")
Set xlWkb = xlApp.Workbooks.Open(WBPath & WBName)

'Run the On Open macro (xlautoopen = 1)
xlWkb.RunAutoMacros 1

xlApp.Run WBName & "!Macro2"

'Choose to dispay Excel or Close Excel
'based on ForceExit variable
If ForceExit Then
xlWkb.Close 0
xlApp.Quit
Set xlWkb = Nothing
Set xlApp = Nothing
Else
xlApp.Visible = True
End If