Opening xl in hidden mode via VBS script

tx12345 · Sep 21, 2006

hi

there is some VBS code that is very handy:

Code:

on error resume next
set app = createobject("excel.application")
set wb = app.workbooks.open (replace(wscript.scriptfullname,wscript.scriptname,"hello.xls"))
if wb is nothing then app.quit

when using a form concept, one can have excel completely hidden - even on start up - where only the form appears. makes it 'seem' like the prog is all yours and excel is nowhere to be found. so that is a great feature.

there is a glitch, though. if any other files are opened, once they run their routine (in my case database updates), the program quits. but if you never stray from the single file where the form is located, everything is fine.

i was wondering if there is a way to tweak the code to make sure the original file stays open, even though at times other files might open, which would include opening ie to go on the net and do stuff.

many thanks as always

tx

kxlvba · Sep 21, 2006

Same here I have a different issue. I'm using the following code to hide and show the excel file. For me I need to have the control to hide and show the excel file after doing something on a userform.
Using Application.Visible = True / False hides all workbooks. I just need to hide the workbook which has the form and keep rest of the open workbooks open. Any advice.

Legacy 98055 · Sep 21, 2006

You may be able to work with the following code I posted here. I have a hunch that you will need to avoid modal userforms. This code forces all other workbooks to be opened within a separate instance of Excel. Another way of stating this is that this code, pasted into ThisWorkbook class, will keep thisworkbook opened, alone, within it's own instance.

Tx. Provide some more details. When you mention other workbooks opening and interfering, are these workbooks opened via code from within the invisible instance or opened from some user from elsewhere? I cannot reproduce your problem. You will have to walk me through it.

<table width="100%" border="1" bgcolor="White" style="filter

rogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD> Option Explicit

 Private Declare Function SetForegroundWindow Lib "user32" _
 (ByVal hWnd As Long) As Long

 Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
 (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

 Private WithEvents pParentApp As Application
 Private pAuxiliaryInstance As Application
 Private pPersonalXls As String

 Private Sub Workbook_Open()
 If ThisWorkbook.ReadOnly Then ThisWorkbook.Close False
 SetUpOrpan
 End Sub

 Private Sub SetUpOrpan()
 Dim wb As Workbook

 'get an instance to "PERSONAL.XLS" if it is loaded
 On Error Resume Next
 Set wb = Workbooks("PERSONAL.XLS")
 On Error GoTo 0

 If Workbooks.Count > 1 And wb Is Nothing Then
 'PERSONAL.XLS not loaded
 'other workbooks are already open in this instance
 'open ThisWorkbook in a new instance of Excel.Application
 CloseMeAndStartMeInNewInstance
 ElseIf Workbooks.Count > 2 Then
 'PERSONAL.XLS may or may not be loaded. Does not matter at this point
 'other workbooks are already open in this instance
 'open ThisWorkbook in a new instance of Excel.Application
 CloseMeAndStartMeInNewInstance
 Else
 'this instance is ok to open ThisWorkbook
 'close PERSONAL.XLS in this instance
 If Not wb Is Nothing Then
 pPersonalXls = wb.FullName
 wb.Close
 End If
 'continue opening in this instance
 'start watching the application level events to
 'respond to any attempts to create a new workbook
 'or to open an existing workbook
 Set pParentApp = Application
 End If
 End Sub

 Private Sub SetAuxiliaryInstance(App As Excel.Application)
 If pAuxiliaryInstance Is Nothing Then
 Set pAuxiliaryInstance = App
 End If
 End Sub

 Private Sub Workbook_BeforeClose(Cancel As Boolean)
 If Not pAuxiliaryInstance Is Nothing Then
 'if the user closes the Auxiliary Instance but we still have a reference
 'to the application, we will have an orphaned proccess running invisibly
 'this will close it
 If Not pAuxiliaryInstance.Visible Then
 pAuxiliaryInstance.Quit
 Set pAuxiliaryInstance = Nothing
 End If
 End If
 End Sub

 Private Sub pParentApp_NewWorkbook(ByVal wb As Workbook)
 'close the newly created workbook and open it in the Auxiliary Instance
 wb.Close False
 CreateNewOrGetExistingInstance
 pAuxiliaryInstance.Workbooks.Add
 SetForegroundWindow FindWindow("XLMAIN", pAuxiliaryInstance.Caption)
 End Sub

 Private Sub pParentApp_WorkbookOpen(ByVal wb As Workbook)
 'close the newly opened workbook and re-open it in the Auxiliary Instance
 Dim WorkbookFullName As String
 If wb.FullName = ThisWorkbook.FullName Then Exit Sub
 WorkbookFullName = wb.FullName
 wb.Close False
 CreateNewOrGetExistingInstance
 pAuxiliaryInstance.Workbooks.Open WorkbookFullName
 SetForegroundWindow FindWindow("XLMAIN", pAuxiliaryInstance.Caption)
 End Sub

 Private Sub CreateNewOrGetExistingInstance()
 If pAuxiliaryInstance Is Nothing Then
 Set pAuxiliaryInstance = New Application
 If pAuxiliaryInstance.Workbooks.Count = 0 And pPersonalXls <> "" Then
 pAuxiliaryInstance.Workbooks.Open pPersonalXls
 End If
 End If
 'always set the visiblility to True because the user may have closed
 'the application but it is still running invisibly if we have a reference to it
 pAuxiliaryInstance.Visible = True
 End Sub

 Private Sub CloseMeAndStartMeInNewInstance()

 Dim xlApp As Excel.Application

 'Will "transfer" this workbook to another instance of Excel
 Application.DisplayAlerts = False
 ThisWorkbook.Saved = True
 If ThisWorkbook.ReadOnly = False Then
 ThisWorkbook.ChangeFileAccess xlReadOnly
 End If
 Application.DisplayAlerts = True

 Set xlApp = New Excel.Application
 xlApp.Workbooks.Open ThisWorkbook.FullName
 xlApp.UserControl = True
 xlApp.Visible = True

 xlApp.Run ThisWorkbook.Name & "!Thisworkbook.SetAuxiliaryInstance", Application

 ThisWorkbook.Close savechanges:=False

 End Sub
</td></tr></table><button onclick='document.all("92120063722562").value=document.all("92120063722562").value.replace(/ \s\s/g,"");document.all("92120063722562").value=document.all("92120063722562").value.replace(/ /g,"");window.clipboardData.setData("Text",document.all("92120063722562").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="92120063722562" wrap="virtual">
Option Explicit

Private Declare Function SetForegroundWindow Lib "user32" _
(ByVal hWnd As Long) As Long

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private WithEvents pParentApp As Application
Private pAuxiliaryInstance As Application
Private pPersonalXls As String

Private Sub Workbook_Open()
If ThisWorkbook.ReadOnly Then ThisWorkbook.Close False
SetUpOrpan
End Sub

Private Sub SetUpOrpan()
Dim wb As Workbook

'get an instance to "PERSONAL.XLS" if it is loaded
On Error Resume Next
Set wb = Workbooks("PERSONAL.XLS")
On Error GoTo 0

If Workbooks.Count > 1 And wb Is Nothing Then
'PERSONAL.XLS not loaded
'other workbooks are already open in this instance
'open ThisWorkbook in a new instance of Excel.Application
CloseMeAndStartMeInNewInstance
ElseIf Workbooks.Count > 2 Then
'PERSONAL.XLS may or may not be loaded. Does not matter at this point
'other workbooks are already open in this instance
'open ThisWorkbook in a new instance of Excel.Application
CloseMeAndStartMeInNewInstance
Else
'this instance is ok to open ThisWorkbook
'close PERSONAL.XLS in this instance
If Not wb Is Nothing Then
pPersonalXls = wb.FullName
wb.Close
End If
'continue opening in this instance
'start watching the application level events to
'respond to any attempts to create a new workbook
'or to open an existing workbook
Set pParentApp = Application
End If
End Sub

Private Sub SetAuxiliaryInstance(App As Excel.Application)
If pAuxiliaryInstance Is Nothing Then
Set pAuxiliaryInstance = App
End If
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not pAuxiliaryInstance Is Nothing Then
'if the user closes the Auxiliary Instance but we still have a reference
'to the application, we will have an orphaned proccess running invisibly
'this will close it
If Not pAuxiliaryInstance.Visible Then
pAuxiliaryInstance.Quit
Set pAuxiliaryInstance = Nothing
End If
End If
End Sub

Private Sub pParentApp_NewWorkbook(ByVal wb As Workbook)
'close the newly created workbook and open it in the Auxiliary Instance
wb.Close False
CreateNewOrGetExistingInstance
pAuxiliaryInstance.Workbooks.Add
SetForegroundWindow FindWindow("XLMAIN", pAuxiliaryInstance.Caption)
End Sub

Private Sub pParentApp_WorkbookOpen(ByVal wb As Workbook)
'close the newly opened workbook and re-open it in the Auxiliary Instance
Dim WorkbookFullName As String
If wb.FullName = ThisWorkbook.FullName Then Exit Sub
WorkbookFullName = wb.FullName
wb.Close False
CreateNewOrGetExistingInstance
pAuxiliaryInstance.Workbooks.Open WorkbookFullName
SetForegroundWindow FindWindow("XLMAIN", pAuxiliaryInstance.Caption)
End Sub

Private Sub CreateNewOrGetExistingInstance()
If pAuxiliaryInstance Is Nothing Then
Set pAuxiliaryInstance = New Application
If pAuxiliaryInstance.Workbooks.Count = 0 And pPersonalXls <> "" Then
pAuxiliaryInstance.Workbooks.Open pPersonalXls
End If
End If
'always set the visiblility to True because the user may have closed
'the application but it is still running invisibly if we have a reference to it
pAuxiliaryInstance.Visible = True
End Sub

Private Sub CloseMeAndStartMeInNewInstance()

Dim xlApp As Excel.Application

'Will "transfer" this workbook to another instance of Excel
Application.DisplayAlerts = False
ThisWorkbook.Saved = True
If ThisWorkbook.ReadOnly = False Then
ThisWorkbook.ChangeFileAccess xlReadOnly
End If
Application.DisplayAlerts = True

Set xlApp = New Excel.Application
xlApp.Workbooks.Open ThisWorkbook.FullName
xlApp.UserControl = True
xlApp.Visible = True

xlApp.Run ThisWorkbook.Name & "!Thisworkbook.SetAuxiliaryInstance", Application

ThisWorkbook.Close savechanges:=False

End Sub</textarea>

tx12345 · Sep 21, 2006

Hi Tom

thanks for the reply. i forgot to post the links to the example files

http://slmap.hostingweb.us//hello.xls
http://slmap.hostingweb.us//goodbye.xls
http://slmap.hostingweb.us//hello.vbs

the vbs file is the same code as above. all files need to be in the same folder. for this test i put them all in C:\.

i open C:\hello.xls by clicking on hello.vbs. hello.xls opens nicely, with excel completely hidden.

then i click on the 'Run' button on the form from hello.xls. all this does is has the goodbye.xls file open, copy and paste some txt, then close.

but once goodbye.xls closes (part of the routine) excel quits. if i do the workbook open routine to hide excel, there are no bugs, but then i get the big splash of exel at the open which is what i am trying to avoid.

thanks again

tx

tusharm · Sep 21, 2006

Create an add-in. See http://www.mrexcel.com/board2/viewtopic.php?p=148963#148963

Same here I have a different issue. I'm using the following code to hide and show the excel file. For me I need to have the control to hide and show the excel file after doing something on a userform.
Using Application.Visible = True / False hides all workbooks. I just need to hide the workbook which has the form and keep rest of the open workbooks open. Any advice.

tusharm · Sep 21, 2006

The only reason XL would quit when other files are opened / closed is that there is code somewhere (either in those files or some event procedure in some other workbook/add-in) telling XL to quit. If so, there's nothing you can do other than change those files (or get whoever is responsible for those files to change them).

You can make all your files cooperate -- something along the lines of how all my different add-ins manage the common TM menu they use (*). Each file should clean up its own environment (close data workbooks in your case; delete individual menu and sub-menu items in my case). Then, the add-in checks if it is the last user of the resource (in your case probably workbooks.count=1; in my case that there are no more items present in the TM commandbar). If so, they clean up the common resource. Otherwise, they leave the task to someone else.

(*) In my case, the concept extends recursively to the TM menu andn all subordinate sub-menus. Each add-in deletes a sub-menu if, when it is done, there are no more entries under that submenu.

{snip}

there is a glitch, though. if any other files are opened, once they run their routine (in my case database updates), the program quits. but if you never stray from the single file where the form is located, everything is fine.

i was wondering if there is a way to tweak the code to make sure the original file stays open, even though at times other files might open, which would include opening ie to go on the net and do stuff.

many thanks as always

tx

Legacy 98055 · Sep 21, 2006

Tusharm. You would have to take a look at his code. The app is indeed closing when opened via automation and not closing when opened by the user. Hello.xls is opened via automation, hello.xls opens goodbye.xls. When goodbye.xls is saved and then closed, the entire app shuts down. This does not happen when the exact same process is duplicated by the user opening Hello.xls and running the same code. My hunch is that the running object table contains no record of hello.xls. I can't think of anything else. Also, he is wanting to maintain a standalone look with only userforms being visible. How can that be done using an addin?

tusharm · Sep 21, 2006

Tom,

Dunno why that would happen.

Usually, I don't download files but since you had already looked at these, I did. Both XLS files have a 'quit' procedure with a 'application.quit' statement in them. If you haven't already done so, you may want to put a 'stop' before the .quit and see if either routine is called. At that point, check the Call Stack to see how it gets called.

I have also learnt to be very careful about playing games with the state of the workbook in BeforeClose and BeforeSave event procedures. In the goodbye.xls file, there is a beforeclose proc that sets the saved property to true. You might want to get rid off the event proc and in the hello.xls file replace the .save and the .close with .close savechanges:=true (please check syntax).

Of course, all of the above may be red herrings since it is possible that there's something wrong with XL/the OS.

Tusharm. You would have to take a look at his code. The app is indeed closing when opened via automation and not closing when opened by the user. Hello.xls is opened via automation, hello.xls opens goodbye.xls. When goodbye.xls is saved and then closed, the entire app shuts down. This does not happen when the exact same process is duplicated by the user opening Hello.xls and running the same code. My hunch is that the running object table contains no record of hello.xls. I can't think of anything else. Also, he is wanting to maintain a standalone look with only userforms being visible. How can that be done using an addin?

tx12345 · Sep 21, 2006

I can't think of anything else.

after tinkering with the code, i discovered that you can open as many files as you want, and do whatever you want with them, but if you go to *close* any of the newly opened files, then the whole thing up and quits on you, without regard to whether or not there is an 'application.quit' command somewhere in the code.

it seems at first glance the issue is since xl is hidden, when it receives a close command, it cannot make a distinction as to what it is it is supposed to close, and so it just quits the prog, or something like that

so it seems anywhere a command to close appers it is interpreted as a command to quit

the question is how to close a file and not have xl think it means quit the program.

tusharm · Sep 21, 2006

I cannot duplicate this problem w/XL2003 on WinXP Pro.

Here's what I did: Created a workbook, book1.xls. In the thisworkbook module, added the code

Code:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    MsgBox "In " & ThisWorkbook.Name & " BeforeClose"
    End Sub

Private Sub Workbook_Open()
    MsgBox "In " & ThisWorkbook.Name & " Open"
    If InStr(1, ThisWorkbook.Name, "book1", vbTextCompare) > 0 Then
        Workbooks.Open ThisWorkbook.Path & Application.PathSeparator & "book2.xls"
    Else
        ThisWorkbook.Close
        End If
    End Sub

Saved the file as book1.xls and book2.xls.

Modified the VBS script to

Code:

on error resume next
set app = createobject("excel.application")
set wb = app.workbooks.open (replace(wscript.scriptfullname,wscript.scriptname,"book1.xls"))

When I run the script, I get the book1 open, book2 open, and book2 close messages. Then, as expected XL hangs and I have to use the Windows Task Manager to kill it. The next time I run XL through the UI, 'book1.xls [original]' is in the 'document recovery' pane.

I can't think of anything else.

Click to expand...

after tinkering with the code, i discovered that you can open as many files as you want, and do whatever you want with them, but if you go to *close* any of the newly opened files, then the whole thing up and quits on you, without regard to whether or not there is an 'application.quit' command somewhere in the code.

it seems at first glance the issue is since xl is hidden, when it receives a close command, it cannot make a distinction as to what it is it is supposed to close, and so it just quits the prog, or something like that

so it seems anywhere a command to close appers it is interpreted as a command to quit

the question is how to close a file and not have xl think it means quit the program.

Opening xl in hidden mode via VBS script

tx12345

Board Regular

Excel Facts

kxlvba

New Member

Legacy 98055

Guest

tx12345

Board Regular

tusharm

MrExcel MVP

tusharm

MrExcel MVP

Legacy 98055

Guest

tusharm

MrExcel MVP

tx12345

Board Regular

tusharm

MrExcel MVP

Similar threads

Forum statistics

Share this page

Opening xl in hidden mode via VBS script

Board Regular

Excel Facts

New Member

Legacy 98055

Guest

Board Regular

MrExcel MVP

MrExcel MVP

Legacy 98055

Guest

MrExcel MVP

Board Regular

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock