Discover names of open Workbooks/Worksheets

[Chris Watts]

I am trying to discover the names of the Workbooks and Worksheets that are
open (not active) in Excel, using vba.

When I try:

Sub DiscoverOpenSheets()
Dim wbBook As Workbook
Dim wsSheet As Worksheet

For Each wbBook In Application
MsgBox wbBook.Name
Next

For Each wsSheet In ActiveWorkbook
MsgBox wsSheet.Name
Next

End Sub

Each of the "For Each .. In .." statements causes an error - "Object does
not support this property or method".
What am I doing wron?

TIA
Chris

 

[Dave Peterson]

Sub DiscoverOpenSheets()
Dim wbBook As Workbook
Dim wsSheet As Worksheet

For Each wbBook In Application.workbooks
MsgBox wbBook.Name
Next wbBook

For Each wsSheet In ActiveWorkbook.worksheets
MsgBox wsSheet.Name
Next wsSheet

End Sub

 

[Dave Peterson]

And you may want:

Sub DiscoverOpenSheets()
Dim wbBook As Workbook
Dim wsSheet As Worksheet

For Each wbBook In Application.workbooks
For Each wsSheet In wbbook.worksheets
MsgBox wsSheet.Name
Next wsSheet
next wbbook

End Sub

 

[Don Guillett]

Nice. To make a list try this small mod

Sub DiscoverOpenSheets()
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim r As Long
r = 1
For Each wbBook In Application.Workbooks
'MsgBox wbBook.Name
For Each wsSheet In wbBook.Worksheets
Cells(r, 1) = wbBook.Name
Cells(r, 2) = wsSheet.Name
'MsgBox wsSheet.Name
r = r + 1
Next wsSheet
Next wbBook
columns("a:b").autofit
End Sub

 

[Dave Peterson]

And just in case any of those names look like numbers (or dates)...

Cells(r, 1) = "'" & wbBook.Name
Cells(r, 2) = "'" & wsSheet.Name

Nice. To make a list try this small mod

Sub DiscoverOpenSheets()
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim r As Long
r = 1
For Each wbBook In Application.Workbooks
'MsgBox wbBook.Name
For Each wsSheet In wbBook.Worksheets
Cells(r, 1) = wbBook.Name
Cells(r, 2) = wsSheet.Name
'MsgBox wsSheet.Name
r = r + 1
Next wsSheet
Next wbBook
columns("a:b").autofit
End Sub

 

[Chris Watts]

Many thanks Dave and Don. That should do it for me.
cheers
Chris