Retrieving the sheet names of another workbook

  • Thread starter Thread starter Aidy
  • Start date Start date
A

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
 
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
 
Back
Top