Opening xl in hidden mode via VBS script

tx12345

Board Regular
Joined
Aug 28, 2006
Messages
165
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

kxlvba

New Member
Joined
Jun 11, 2006
Messages
43
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.
 
L

Legacy 98055

Guest
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:progid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>  <font color="#0000A0">Option</font> <font color="#0000A0">Explicit</font>

  <font color="#0000A0">Private</font> <font color="#0000A0">Declare</font> <font color="#0000A0">Function</font> SetForegroundWindow <font color="#0000A0">Lib</font> "user32" _
      (ByVal hWnd <font color="#0000A0">As</font> Long) <font color="#0000A0">As</font> <font color="#0000A0">Long</font>

  <font color="#0000A0">Private</font> <font color="#0000A0">Declare</font> <font color="#0000A0">Function</font> FindWindow <font color="#0000A0">Lib</font> "user32" <font color="#0000A0">Alias</font> "FindWindowA" _
      (ByVal lpClassName <font color="#0000A0">As</font> String, <font color="#0000A0">ByVal</font> lpWindowName <font color="#0000A0">As</font> String) <font color="#0000A0">As</font> <font color="#0000A0">Long</font>

  <font color="#0000A0">Private</font> <font color="#0000A0">WithEvents</font> pParentApp <font color="#0000A0">As</font> Application
  <font color="#0000A0">Private</font> pAuxiliaryInstance <font color="#0000A0">As</font> Application
  <font color="#0000A0">Private</font> pPersonalXls <font color="#0000A0">As</font> <font color="#0000A0">String</font>

  <font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> Workbook_Open()
      <font color="#0000A0">If</font> ThisWorkbook.ReadOnly <font color="#0000A0">Then</font> ThisWorkbook.Close <font color="#0000A0">False</font>
      SetUpOrpan
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

  <font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> SetUpOrpan()
      <font color="#0000A0">Dim</font> wb <font color="#0000A0">As</font> Workbook

     <font color="#008000"> 'get an instance to "PERSONAL.XLS" if it is loaded</font>
      <font color="#0000A0">On</font> <font color="#0000A0">Error</font> <font color="#0000A0">Resume</font> <font color="#0000A0">Next</font>
      <font color="#0000A0">Set</font> wb = Workbooks("PERSONAL.XLS")
      <font color="#0000A0">On</font> <font color="#0000A0">Error</font> <font color="#0000A0">GoTo</font> 0

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

  <font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> SetAuxiliaryInstance(App <font color="#0000A0">As</font> Excel.Application)
      <font color="#0000A0">If</font> pAuxiliaryInstance <font color="#0000A0">Is</font> <font color="#0000A0">Nothing</font> <font color="#0000A0">Then</font>
          <font color="#0000A0">Set</font> pAuxiliaryInstance = App
      <font color="#0000A0">End</font> <font color="#0000A0">If</font>
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

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

  <font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> pParentApp_NewWorkbook(ByVal wb <font color="#0000A0">As</font> Workbook)
     <font color="#008000"> 'close the newly created workbook and open it in the Auxiliary Instance</font>
      wb.Close <font color="#0000A0">False</font>
      CreateNewOrGetExistingInstance
      pAuxiliaryInstance.Workbooks.Add
      SetForegroundWindow FindWindow("XLMAIN", pAuxiliaryInstance.Caption)
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

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

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

  <font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> CloseMeAndStartMeInNewInstance()

      <font color="#0000A0">Dim</font> xlApp <font color="#0000A0">As</font> Excel.Application

     <font color="#008000"> 'Will "transfer" this workbook to another instance of Excel</font>
      Application.DisplayAlerts = <font color="#0000A0">False</font>
      ThisWorkbook.Saved = <font color="#0000A0">True</font>
      <font color="#0000A0">If</font> ThisWorkbook.ReadOnly = <font color="#0000A0">False</font> <font color="#0000A0">Then</font>
          ThisWorkbook.ChangeFileAccess xlReadOnly
      <font color="#0000A0">End</font> <font color="#0000A0">If</font>
      Application.DisplayAlerts = <font color="#0000A0">True</font>

      <font color="#0000A0">Set</font> xlApp = <font color="#0000A0">New</font> Excel.Application
      xlApp.Workbooks.Open ThisWorkbook.FullName
      xlApp.UserControl = <font color="#0000A0">True</font>
      xlApp.Visible = <font color="#0000A0">True</font>

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

      ThisWorkbook.Close savechanges:=False

  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table><button onclick='document.all("92120063722562").value=document.all("92120063722562").value.replace(/<br \/>\s\s/g,"");document.all("92120063722562").value=document.all("92120063722562").value.replace(/<br \/>/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

Board Regular
Joined
Aug 28, 2006
Messages
165
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,029

ADVERTISEMENT

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

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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
 
L

Legacy 98055

Guest

ADVERTISEMENT

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

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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

Board Regular
Joined
Aug 28, 2006
Messages
165
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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.

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.
 

Forum statistics

Threads
1,136,268
Messages
5,674,734
Members
419,523
Latest member
Urnovio

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top