list the sheets in a workbook

[Guest]

I need to be able to list the names of the sheets contained in a workbook.

 

[Guest]

here is a vb.net example that should work for ya.

Dim sheet As Excel.Worksheet
Dim ODOC As Excel.Workbook

................

Dim worksheetname As String
For Each sheet In ODOC.Worksheets
Console.WriteLine(sheet.Name)
Next

 

[Guest]

Function ListSheets() As String
For Each sht In ActiveWorkbook.Sheets
ListSheets = ListSheets & "," & sht.Name
Next
End Function

Sub aa()
MsgBox Mid(ListSheets, 2)
End Sub

Run the sub aa to get a comma delimited string containing the names of the
sheets. If you want to include worksheets only, replace ActiveWorkbook.Sheets
by ActiveWorkbook.Worksheets.

 

[Guest]

Dim ODOC As Excel.Workbook
Dim sheet As Excel.Worksheet

.......
Dim worksheetname As String
For Each sheet In ODOC.Worksheets
Console.WriteLine(sheet.Name)
Next

 

[Tom Ogilvy]

sStr = ""
for each sh in Activeworkbook.sheets
sStr = sStr & sh.Name & vbNewLine
Next
msgBox sStr

--
Regards,
Tom Ogilvy

"Listing of Excel shhets in a workbook" <Listing of Excel shhets in a
(e-mail address removed)> wrote in message
news:[email protected]...

 

[Alan Beban]

I need to be able to list the names of the sheets contained in a workbook.

Here are a couple of ways:

Sub qwerty4()
'Workbook must have reference to Microsoft Scripting Runtime
Dim dic As Dictionary, rng As Range
Set dic = New Dictionary
Set rng = Range("A1:A" & ActiveWorkbook.Sheets.Count)
For Each sh In ActiveWorkbook.Sheets
dic.Add CStr(sh.Name), sh.Name
Next
rng.Value = Application.Transpose(dic.Items)
End Sub

Sub qwerty5()
Dim arr, rng As Range
Dim i As Integer, n As Integer, m As Object
Set m = ActiveWorkbook.Sheets
n = m.Count
Set rng = Range("A1:A" & n)
ReDim arr(1 To n, 1 To 1)
i = 1
For Each sh In m
arr(i, 1) = sh.Name
i = i + 1
Next
rng.Value = arr
End Sub

Alan Beban