Open workbook Count

[Guest]

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

 

[Norman Jones]

Hi Hervinder,

Try:

MsgBox Application.Workbooks.Count

 

[Andrew Taylor]

Use Workbooks.Count

 

[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)

 

[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 wkbBoo

 

[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






message
news:[email protected]...