Toolbar visibility problem with Excel in WSH running vbscript

I am writing a vbscript running under WSH which starts Excel and
writes some stuff into some cells. This all works just fine. I am
using Excel as an output window for the script because it is easy to
do and its easy to format the data. However, there is no need to
display the toolbars, formula bar, and status bar in my Excel output
window. I am able to turn these off with vbscript code.

Here is my problem. When Excel terminates, by the user clicking the
red X to close the window, Excel remembers that the toolbars, formula
bar, and status bar were not visible. The next time the user launches
Excel to do his own thing, all he sees is the menu bar. He must
manually make the other bars visible again.

I have not been able to come up with a solution to this problem. If
vbscript could sit in a loop waiting for the user to close the Excel
window that the script created, I could then open another hidden Excel
window and reset the visibility of the hidden bars. However, I cannot
figure out how to detect when the Excel window is closed.

Creating a VBA Excel macro to reset the visibility of the bars is not
practical since my script will be distributed to a large number of
users and I don't want to mess with that sort of thing. The solution
must be contained within the vbscript program.

Any suggestions as to how I can restore the visibility of the hidden
bars would be appreciated.

By the way, is there a way to turn off the menu bar as well? I really
don't need that either.

mr
Wed Dec 01 16:42:48 CST 2004

hi Arnie,

My vba object viewer for xl (xl2k) shows a "WorkbookBeforeClose" event,
fired by the "Excel.Application" object, before the workbook closes(?). Or,
at least that's what it sounds like.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/office97/html/output/F1/D3/S5A83F.asp

Did you try that?

cheers, jw

"Arnie" <kzjkn3@hotmail.com> wrote in message
news:a3ed90cc.0412010959.473ac96@posting.google.com...
> I am writing a vbscript running under WSH which starts Excel and
> writes some stuff into some cells. This all works just fine. I am
> using Excel as an output window for the script because it is easy to
> do and its easy to format the data. However, there is no need to
> display the toolbars, formula bar, and status bar in my Excel output
> window. I am able to turn these off with vbscript code.
>
> Here is my problem. When Excel terminates, by the user clicking the
> red X to close the window, Excel remembers that the toolbars, formula
> bar, and status bar were not visible. The next time the user launches
> Excel to do his own thing, all he sees is the menu bar. He must
> manually make the other bars visible again.
>
> I have not been able to come up with a solution to this problem. If
> vbscript could sit in a loop waiting for the user to close the Excel
> window that the script created, I could then open another hidden Excel
> window and reset the visibility of the hidden bars. However, I cannot
> figure out how to detect when the Excel window is closed.
>
> Creating a VBA Excel macro to reset the visibility of the bars is not
> practical since my script will be distributed to a large number of
> users and I don't want to mess with that sort of thing. The solution
> must be contained within the vbscript program.
>
> Any suggestions as to how I can restore the visibility of the hidden
> bars would be appreciated.
>
> By the way, is there a way to turn off the menu bar as well? I really
> don't need that either.

Arnie
Thu Dec 02 09:03:50 CST 2004

Using WorkbookBeforeClose event, at least to my knowledge, involves
writing vba code in the xl workbook. This is not what I want to do.
The solution that I am looking for must be within the vbs code.

One thing that I have thought of is to scan through the collection of
toolbars and remember which ones are visible when I create the
workbook. Then, when the user closes Excel, my script could then
launch a non-visible Excel application and restore the toolbars that
were previously visible. The problem is that I don't know how to
detect, in my vbscript code, when the user closes the Excel application
I created for him. The other problem I have is that this seems like a
really dumb way to solve my problem - there must be a better way!

Michael
Thu Dec 02 12:05:51 CST 2004

Arnie wrote:
> Using WorkbookBeforeClose event, at least to my knowledge, involves
> writing vba code in the xl workbook. This is not what I want to do.
> The solution that I am looking for must be within the vbs code.
>

You can handle events of COM objects (including Office *.Application
objects) in WSH hosted script. See the documentation of the 2nd optional
argument of the WScript.CreateObject method.

--
Michael Harris
Microsoft MVP Scripting

mr
Thu Dec 02 12:22:53 CST 2004

Try this:

if (objXL.Visible = False) then MsgBox("uh-oh. oXL was closed by user")

cheers, jw

mr
Fri Dec 03 16:07:34 CST 2004

hi Arnie,

The assertion that a script can NOT make use of excel features that are only
available when using vba is incorrect.

A way to do this was first (afaik) described by Alex Angelopopoulos (MVP) in
this newsgroup -- circa April 2002 -- and is still valid after all those
years, gasp!. In summary, you insert vba code into excel, and then call it
using the "Run" method. As long as you close excel without saving anything,
then the code will not get stuck in the user's spreadsheet.

As far as connecting events, one can also make use of an "ole callback".
vbScript has an ole callback facility called "GetRef". As microsoft
describes it, "GetRef" can be used to connect html/dhtml events to your
script, _BUT_ "GetRef" has much wider capabilities. These broader "GetRef"
capabilities may be characterized as "undocumented-and-unsupported", but
they work. Microsoft has an nasty habit of removing "U-and-U" code without
announcing its intentions ahead of time, so be forewarned. However, in this
case, microsoft seems to have no further plans for any wsh/vbs
"enhancements", and one may reasonably assume that
what-you-see-is-what-you-get -- including those "U-and-U" features.

Here is what you do. If the given task is to detect workbook closures
(which allegedly can't be done from script) -- then the approach would be to
insert code into the workbook, which _CAN_ detect it's own closure. You
then provide an ole callback to an event handler in your script, to be used
by the workbook close event handler to notify your script code. When the
closure event is detected, the vba code calls your script's close event
handler, and you do what you want.

O.K., so that may sound a little obscure. Here is a "concrete" example,
i.e., some code:


--- <snip> ---
' detect xl close event, using vba code from script, jw 03Dec04
Dim oXL : Set oXL = CreateObject("Excel.Application")
Const vbext_ct_StdModule = 1 ' from xl typelib...
Dim sWorkbookCode ' As String
Dim vbaWorkbook ' As Workbook
Dim vbaStdModule ' as code module
Dim sSetCallback ' As String
Dim m_bXLClosed ' as boolean

sWorkbookCode = "Option Explicit" & vbCrLf _
& "Private Sub Workbook_BeforeClose(Cancel As Boolean)" & vbCrLf _
& "Dim oCallback As Object" & vbCrLf _
& " MsgBox "" ..detected wkbook BeforeClose event"", _ " & vbCrLf _
& " vbInformation, "" << vba Code (in ThisWorkbook Module) >> """ &
vbCrLf _
& " Set oCallback = Application.Run(""getCallback"")" & vbCrLf _
& " oCallback ' execute the ole callback (back to the script)" &
vbCrLf _
& " Set oCallback = Nothing ' to close out this reference" & vbCrLf _
& "End Sub"

sSetCallback = "Option Explicit" & vbCrLf _
& "Private oCallback As Object" & vbCrLf _
& "Public Sub setCallbackTo_myScript(ByRef oGetRef As Object)" & vbCrLf
_
& " Set oCallback = oGetRef" & vbCrLf _
& " ' MsgBox(""callback set"")" & vbCrLf _
& "End Sub" & vbCrLf _
& "Public Function getCallback() As Object" & vbCrLf _
& " Set getCallback = oCallback" & vbCrLf _
& " ' MsgBox(""get callback called"")" & vbCrLf _
& " Set oCallback = Nothing ' to close out this reference" & vbCrLf _
& "End Function"

' ================================================
' end of initializations, SCRIPT BEGINS HERE...
' ================================================

With oXL
.Width = 400 : .Height = 250 ' resize
.Workbooks.Add
.Visible = True
.Cells(1, 1).Value = "whs/vbs/vba demo script dialog... "
.Cells(2, 1).Value = " ..the objective here is to see whether"
.Cells(3, 1).Value = " it is possible to catch a close event"
.Cells(4, 1).Value = " from a whs/vbs script (try and see)... "
.Range("A1:D4").Select
.Selection.Font.Bold = True
.Selection.Interior.ColorIndex = 14 ' teal?
.Selection.Font.ColorIndex = 6 ' pale yellow
End With

' add code to the active workbook (to detect close events)...
Set vbaWorkbook = oXL.VBE.VBProjects.Item(1).VBComponents("ThisWorkbook")
vbaWorkbook.CodeModule.AddFromString sWorkbookCode

' add a standard code module, returns a module object (vbComponent)...
' (note: can't find a way to access macros stored in "ThisWorkbook"
module)...
Set vbaStdModule =
oXL.VBE.VBProjects(1).VBComponents.Add(vbext_ct_StdModule)
' name the standard code module, (so as to keep track of it, for later)...
vbaStdModule.Name = "myCodeModule"

' add callback code to std module...
oXL.VBE.VBProjects.Item(1).VBComponents("myCodeModule") _
.CodeModule.AddFromString(sSetCallback)

' pass ole callback reference to excel/vba module...
oXL.Run "setCallbackTo_myScript", GetRef("myScript_xlCloseEvent")

m_bXLClosed = False ' initialize

Do ' script waits here for xl to close
WScript.Sleep 200
Loop Until m_bXLClosed


MsgBox("Script Ending Now")

' close excel...
oXL.Quit
Set oXL = Nothing


Sub myScript_xlCloseEvent()
MsgBox "detected WorkbookBeforeClose event!!! ", vbExclamation, _
" << myScript VBS Code (myScriptCloseEvent Handler) >> "
m_bXLClosed = True

' save workbook (attempt to get rid of ugly xl crash)...
oXL.ActiveWorkbook.SaveAs "c:/windows/temp/book1.xls"
End Sub

--- </snip> ---

Achtung! Vorsicht! Only attempt to run this script on a (dedicated) test
system. While this concept is "fundamentally sound" and the code is a
masterpiece, it is too much for Microsoft Excel (at least with
xl2k/ofc2k/win98se). You will get a memory fault after excel is closed, and
you will find that excel is NOT really closed after you close it (look at
your task list). Further, if you run the script several times your system
will slow down, and eventually stop when you run out of "virtual memory". I
have tried to clear out any objects that I created, but that didn't seem to
help. Bonus Points will be awarded to anybody who can resolve this minor
problem
(he, he)...

cheers, jw