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


---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Aidy" wrote in message om...
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