Retrieving the sheet names of another workbook

[Aidy]

I am trying to read the name of the sheets of WorkBook B, from
WorkBook's A VBE. This is the code

<snip>
Dim oExcel As New Excel.Application
Dim oWorkBook As Workbook
Dim oWorkSheet As Worksheet

Sub OpenWorkBook(sFileName As String)

Set oWorkBook = oExcel.Workbooks.Open(Filename:=sFileName)

End Sub

Sub RetrieveWorkSheets()

Dim sName As String

For Each oWorkSheet In oWorkBook.Worksheets
sName = oWorkSheet.Name
Next oWorkSheet

End Sub
<snip>

However the name of the sheets in the WorkSheets collection is of the
Active Workbook and not the WorkBook I have opened in the OpenWorkBook
procedure.

Is there anyway to retrieve the sheet names from another workbook?

Cheers

Aidy

 

[David McRitchie]

Hi Andy,
If the other workbook is open try this.

Sub GetSheets_for_Andy()
Dim wkSheet As Worksheet, i As Long
Dim OtherWB As String
OtherWB = "BigWorkbook.xls"
'--Create a New Sheet
Sheets.Add After:=Sheets(Sheets.Count) '-- place at end
'--Rename current Sheet (the new sheet)
ActiveSheet.Name = OtherWB & Format(Now, "-yyyymmddhhmm")
Cells(1, 1) = "List of Sheets in " & OtherWB
For Each wkSheet In Workbooks("xenu.xls").Worksheets
i = i + 1
Cells(1 + i, 1).Value = "'" & wkSheet.Name
Next wkSheet
Columns("A:A").Select
'--Sort the list of worksheet names
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End Sub