Determining the number of open workbooks

[DKS]

Hi,

How can I programatically (via module) determine the number of open
workbooks? Ideally I would love to also determine the names (titles) of each
open workbook, but if that is too much then I can at least live with the
retrieval of info on the number of open workbooks.

Many thanks in anticipation.

 

[Nigel]

Number of workbooks is....

Workbooks.Count

Display each name in MsgBox.....

Dim wB As Workbook
For Each wB In Workbooks
MsgBox wB.Name
Next

 

[Ron de Bruin]

Hi DKS

You can count the workbooks in the loop

Dim wb As Workbook
For Each wb In Application.Workbooks
MsgBox wb.Name
Next wb

 

[Jim Thomlinson]

Here is some code to try...

Sub test()
Dim wbk As Workbook

MsgBox Workbooks.Count
For Each wbk In Workbooks
MsgBox wbk.Name
Next wbk
End Sub

Note that this code will be correct if there is only one open instance of
XL. If you have multiple open instances then it only works on the instance
that it is in. That should not be a problem but it is something to be aware
of...

 

[Ron de Bruin]

Forgot to add this

Test if the workbooks are visible in the loop if that is important.
For example if you want to be sure that there is a visible workbook open

Personal.xls will count as 1 for example

 

[DKS]

If we open several excel files, would it not be in the same instance of Excel
by default?

 

[Jim Thomlinson]

If you double click on an XL file then it opens in one instance of XL. If you
then choose file | Open or if you double click on another XL file it will be
opened in the same original instance. If you now click on Start | Programs
.... Excel then you get another instance of XL. Files opened in this instance
know nothing of the other instance and Vice Versa. If you look in Task
manager you will see 2 seperate XL processes running...