When does a Workbook become a member of the WorkBooks Collection?

[Mister_joe]

Hi Everyone,
I read a statement that says "the Workbooks collection consists of all open Workbook objects". I need a little bit more clarification on the statement. Here's why:

(a) I opened several Excel workbooks without saving any of them. I inserted a macro in one of the workbooks and issued the "Debug.Print Application.Workbooks.Count" command. The command returned 1.

(b) I opened one Excel workbook without saving it, I inserted a macro in it and issued the "Debug.Print Application.Workbooks.Count" command. The command returned 1.

(c) I opened one Excel workbook without saving it, I inserted a macro in it. In addtion, I opened a previously saved Excel file and issued the "Debug.Print Application.Workbooks.Count" command. The command returned 2.

The questions that I need answers to are:
1. When is a workbooks regarded as being open?
2. Does a workbook exist and yet it is not a workbook object?
3. When does a workbook become a workbook object?

Thanks in advance, for your response.

[Aflatoon]

I think you have your original workbooks opening in separate instances of Excel. The Workbooks collection includes all open workbooks in that same instance of Excel.

[Mister_joe]

Thanks. How does Excel open in separate instances under the same Office in the same OS? Bear with me, I am not an expert in this area at all.

[Aflatoon]

If you open Excel by double-clicking a file in Windows Explorer for example, the way it opens is determined by the file type associations set in the registry. Normally, these would open the file in any existing instance of Excel but they can also be set to start a new copy of the application. This appears to be what yours are doing. If you open a workbook using the File-Open command within Excel, they will always open within that same instance - your example (c) above.
If you repair your Excel installation through Control Panel, it should reset the file associations to the defaults.

[Mister_joe]

Thanks. I see what you mean. In my case (a) above, I opened a blank file, then hold down the shift key and clicked on the Excel icon on the task bar to open a new workbook. So, that means I basically started separate copies of Excel. Are there underlying benefits in having separate copies of the application? Why did Microsoft include this option?

[westconn1]

Are there underlying benefits in having separate copies of the application?sometimes it can be very useful, especially for automation

[Mister_joe]

I appreciate your responses. Thank you!

[GTO]

FWIW, while I am not disagreeing per se with westconn, I would suspect that for most users, multiple instances could/would more likely be a PITA and confusing. Just an opinion of course, but imagine the users around you going to View and finding no other workbooks under Switch Windows. Not to say that I've never wanted a "princess" workbook ("it's all about me! No one else allowed!"), for code that was included, but my comments are more at the general use of the app.

[Mister_joe]

Nice if you could break it down for a newbie; like what's the meaning of FWIW, PITA?

[GTO]

Oops, sorry about that. I am afraid I wasn't using any coding related acronyms (like OOP - Object Oriented Programming).


While I am certainly old enough not be a huge fan of texting and the plethora of acronyms with it, I am a terrible typist and sometimes take the lazy way.
FWIW - For what it's worth; PITA - Pain in the arse.

I run into plenty of these that I don't understand; and use http://acronyms.thefreedictionary.com/ in such cases.

Hope that helps,

Mark

[westconn1]

it is sometimes hard to know which workbooks will open in a particular instance of excel, especially if multiple instances are already open
often workbooks opened from url will open in a separate instance, or from within a zip file, but these are just general observations, not any sort of rule i know of

automating excel using VBA from within excel or other applications, the programmer can control if a new instance is used or an existing instance (if one already exists), but difficult to control which instance if several instances are already running

some reasons for using multiple instances, when coding, include
some automation can tke place in a workbook that is not visible to the user
continuous code running in a workbook will not freeze workbooks in some other instance, so you can have code running waiting for an event, that can occur in a different instance of excel, there was an example in this forum recently where that would have worked
alternate instances can be automated from the other, if required


as mentioned above, most users will not feel any need for these advantages

[snb]

You can check the amount of Excel instances using:


Sub geladen_applicaties()
With GetObject(, "Word.Application").Application
For Each ts In .Tasks
If InStr(ts.Name, " Excel") Then c01 = c01 & vbLf & ts.Name
Next
MsgBox c01
end with
End Sub