Open workbook Count

[Hervinder]

I am trying to write a simple code within an existing macro that tells me
how many workbooks are open at any one time.

I can do counting sheets which would be

shts = activeworkbook.sheets.count

i cant seem to apply the same principle with open workbooks.

thank you
hervinder

[Norman Jones]

Hi Hervinder,

Try:

MsgBox Application.Workbooks.Count


---
Regards,
Norman



"Hervinder" <[email protected]> wrote in message
news:[email protected]...
>
> I am trying to write a simple code within an existing macro that tells me
> how many workbooks are open at any one time.
>
> I can do counting sheets which would be
>
> shts = activeworkbook.sheets.count
>
> i cant seem to apply the same principle with open workbooks.
>
> thank you
> hervinder

[Ardus Petus]

nWB = Workbooks.Count

HTH
--
AP

"Hervinder" <[email protected]> a écrit dans le message de
news: [email protected]...
>
> I am trying to write a simple code within an existing macro that tells me
> how many workbooks are open at any one time.
>
> I can do counting sheets which would be
>
> shts = activeworkbook.sheets.count
>
> i cant seem to apply the same principle with open workbooks.
>
> thank you
> hervinder

[Andrew Taylor]

Use Workbooks.Count


Hervinder wrote:
> I am trying to write a simple code within an existing macro that tells me
> how many workbooks are open at any one time.
>
> I can do counting sheets which would be
>
> shts = activeworkbook.sheets.count
>
> i cant seem to apply the same principle with open workbooks.
>
> thank you
> hervinder

[Bob Phillips]

Use Workbooks.Count, but be aware it will count Personal.xls if you have
one, and any other files in XLStart that get opened automatically.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Hervinder" <[email protected]> wrote in message
news:[email protected]...
>
> I am trying to write a simple code within an existing macro that tells me
> how many workbooks are open at any one time.
>
> I can do counting sheets which would be
>
> shts = activeworkbook.sheets.count
>
> i cant seem to apply the same principle with open workbooks.
>
> thank you
> hervinder

[Bob Phillips]

Use Workbooks.Count, but be aware it will count Personal.xls if you have
one, and any other files in XLStart that get opened automatically.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Hervinder" <[email protected]> wrote in message
news:[email protected]...
>
> I am trying to write a simple code within an existing macro that tells me
> how many workbooks are open at any one time.
>
> I can do counting sheets which would be
>
> shts = activeworkbook.sheets.count
>
> i cant seem to apply the same principle with open workbooks.
>
> thank you
> hervinder

[Kaak]

Dim wkbBook as Workbook
Dim lCount as long
For each wkbBook in Application.Workbooks
if wkbBook.Windows(1).visible Then lCount = lCount + 1
next wkbBook

[Chip Pearson]

Try the following function:


Function WorkbookCount(Optional VisibleOnly As Boolean = False)
Dim WB As Workbook
If VisibleOnly = True Then
For Each WB In Workbooks
If WB.Windows(1).Visible = True Then
WorkbookCount = WorkbookCount + 1
End If
Next WB
Else
WorkbookCount = Workbooks.Count
End If
End Function


You can then use it in code like

If WorkbookCount(VisibleOnly:=False) > 1 Then


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com






"Hervinder" <[email protected]> wrote in
message
news:[email protected]...
>
> I am trying to write a simple code within an existing macro
> that tells me
> how many workbooks are open at any one time.
>
> I can do counting sheets which would be
>
> shts = activeworkbook.sheets.count
>
> i cant seem to apply the same principle with open workbooks.
>
> thank you
> hervinder