How many Instances of Excel are open

[julhs]

In Workbook1 in its “ThisWorkbook” module I have a “Workbook_Open” event that automatically opens Workbook2 (reference file) when Workbook1 is opened.

Question; are Wb1 and Wb2 open in the same instance of Excel or in 2 separate instances

 

[julhs]

I was hoping you were not going to come back with that statement!
Think I’m just going to have to except attaching a Save/Close sub to a command button and be done with it.

Really appreciate your help
Julhs

 

[Alex Blakenburg]

Sorry I didn't have a better answer for you. Maybe it's time to consider an upgrade. Windows 7 and Excel 2010 are quite dated now.

 

[julhs]

Funnily you should say that, had only been looking into that on Saturday.

 

[Alex Blakenburg]

It must be a sign.

 

[GTO]

Hi Julhs,

If you haven't dashed the PC against the wall and/or given up outright, maybe try the code at #11 modified/simplified a bit. In a temp folder, Book1.xlsm and Book2.xlsm. In the ThisWorkbook Module of Book1.xlsm:

Option Explicit
  
Sub Workbook_Open()
  
  Application.ScreenUpdating = False
  
  Application.EnableEvents = False
  Workbooks.Open (ThisWorkbook.Path & "\Book2.xlsm")
  Application.EnableEvents = True
  
  ThisWorkbook.Windows(1).Activate
  
  Application.ScreenUpdating = True
  
End Sub
  
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim WB As Workbook
  
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  
  'Loop through each workbook
  For Each WB In Application.Workbooks
    'Close the workbooks and save changes.
    If WB.Name <> ThisWorkbook.Name Then
      WB.Saved = True
      WB.Close SaveChanges:=False
      DoEvents
    End If
  Next WB
  
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  
  ThisWorkbook.Saved = True
  Application.Quit
  
End Sub

I am certainly baffled as to why your code is not working, and just thought a last ditch effort to see if an event in another workboook could be the cause. Please note that rather than forcing a Save, I chose to simply mark the workbooks as saved.

Mark

 

[julhs]

Mark
Not sure what the significance might be, and provided I did exactly what you intended.
Having placed Book1 & 2 in a Temp Folder and then adjust both Open and Close routines in ThisWorkbook module as per your code.
I’m afraid to say it doesn’t seem to be SAVING either Workbook and still getting a “Blank” instance of Excel remaining open when the “Close” sub has run.
If this is baffling 2 MVP’s then its little wonder I couldn’t sort this myself!!!!
My final thought is that something somewhere in my settings is causing the problem or my whole Excel app is corrupted?

 

[julhs]

HANG ON A SECOND, when i closed the Temp Folder versions and went and opened Book1 in the normal fasion I
got the message that it was already open.

 

[julhs]

Mark
That was a red herring!!
Be that as it may, for my own sanity I'm going have to put this to bed and forget about it.
Hopefully when I do a COMPLETE upgrade of hardware and programs that some of my system problems go away

 

[GTO]

Julhs,

If you are having issues with your OS and/or Office apps, then I imagine an upgrade will solve issues. I certainly would be frustrated and by all means, hang onto you sanity and sorry we were unable to solve it.

Reference ...I’m afraid to say it doesn’t seem to be SAVING either Workbook and... I did mention that I chose not to save the test workbooks.

In case you later find yourself wanting to give it another go, I would start with the simplest test I can think of:

Brand new workbook.
Add a new Standard Module; insert:

Public Sub QuitXL()
  ThisWorkbook.Saved = True
  Application.Quit
End Sub

Save and close the WB.
Quit Excel.
Restart Windows.
Open the WB and ALT+F8 to run QuitXL.

If Application.Quit doesn't execute, I am at a total loss.

Best of luck,

Mark

 

[Dan_W]

Jaafar Tribak has code that will do this - I haven't checked if this is the code i was thinking of because am about to get on plane, but I'm pretty sure that this (with some adjustments) will count the number of instances of Excel currently running: Post in thread 'How to target instances of Excel' How to target instances of Excel