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 |
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 |
All times are GMT +1. The time now is 11:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com