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.

Number of workbooks is....

Workbooks.Count

Display each name in MsgBox.....

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

--

Regards,
Nigel
(E-Mail Removed)



"DKS" <(E-Mail Removed)> wrote in message
news:E3AD4CF9-6EDF-4F91-981F-(E-Mail Removed)...
> 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.

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"DKS" <(E-Mail Removed)> wrote in message news:E3AD4CF9-6EDF-4F91-981F-(E-Mail Removed)...
> 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.

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...
--
HTH...

Jim Thomlinson


"DKS" wrote:

> 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.

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ron de Bruin" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> 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
>
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "DKS" <(E-Mail Removed)> wrote in message news:E3AD4CF9-6EDF-4F91-981F-(E-Mail Removed)...
>> 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.

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



"Jim Thomlinson" wrote:

> 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...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "DKS" wrote:
>
> > 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.

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...
--
HTH...

Jim Thomlinson


"DKS" wrote:

> If we open several excel files, would it not be in the same instance of Excel
> by default?
>
>
>
> "Jim Thomlinson" wrote:
>
> > 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...
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "DKS" wrote:
> >
> > > 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.