Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 23rd 04, 05:33 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2004
Posts: 1
Default 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   Report Post  
Old June 25th 04, 07:29 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2003
Posts: 691
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Retrieving stored names of drop down lists MZ New Users to Excel 1 December 13th 09 02:41 PM
View All Sheet Names in Workbook at Once Lincoln Excel Discussion (Misc queries) 2 May 7th 07 11:17 PM
Insert Incremental sheet names in a workbook Sinner Excel Worksheet Functions 2 March 30th 07 02:37 AM
How do I display list of tab names used in a workbook on a sheet tmottur Excel Worksheet Functions 2 December 1st 05 09:42 PM
retrieving table names from Excel and Access sources using ADO masayoshi hayashi Excel Programming 3 December 10th 03 09:35 AM


All times are GMT +1. The time now is 07:52 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017