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