Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieving the sheet names of another workbook
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieving the sheet names of another workbook
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Retrieving stored names of drop down lists | New Users to Excel | |||
View All Sheet Names in Workbook at Once | Excel Discussion (Misc queries) | |||
Insert Incremental sheet names in a workbook | Excel Worksheet Functions | |||
How do I display list of tab names used in a workbook on a sheet | Excel Worksheet Functions | |||
retrieving table names from Excel and Access sources using ADO | Excel Programming |