Tab Name Question

[Guest]

Is there a way in which to determine the name of the tab(s) (through code) in a particular spreadsheet with only knowing the spreadsheet name and location

Any help would be appeciated. Thanks

Kirk

 

[kkknie]

If you mean a different one than your currrent one, this will work:


Code
-------------------
Sub GetTabs()

Dim s
Dim i As Long
Dim sName As String

sName = ActiveWorkbook.Name

Application.ScreenUpdating = False
Workbooks.Open Filename:="C:\Subfolder\Filename.xls"

i = 1
For Each s In Worksheets
Workbooks(sName).Sheets(1).Cells(i, 1).Value = s.Name
i = i + 1
Next
ActiveWindow.Close

End Su

 

[Roger Whitehead]

I think you'd need to open the file first.

for each sh in workbook("YourWorkbook.xls").sheets
debug.print sh.name
next


--
HTH
Roger
Shaftesbury (UK)

Is there a way in which to determine the name of the tab(s) (through code)

in a particular spreadsheet with only knowing the spreadsheet name and
location?

 

[Tod]

To get the names of the worksheets in a workbook (assuming
your running this code from another workbook or from a
workbook in the xlstart folder:

Dim i As Integer
Workbooks.Open "C:\MyPath\MyBook.xls"
For i = 1 To ActiveWorkbook.Worksheets.Count
Debug.Print ActiveWorkbook.Worksheets(i).Name
Next i
ActiveWorkbook.Close SaveChanges:=False
End Sub

Or from Script:

Dim i
dim xl
dim SheetNames

Set xl = CreateObject("Excel.Application")
xl.workbooks.open "C:\My Path\MyBook.xls"
For i = 1 To xl.ActiveWorkbook.Worksheets.Count
SheetNames = SheetNames & vbcrlf &
xl.ActiveWorkbook.worksheets(i).name
Next
xl.ActiveWorkbook.Close

msgbox "The workbook contains these sheets." & vbcrlf &
Sheetnames

xl.quit

tod

-----Original Message-----
Is there a way in which to determine the name of the tab

(s) (through code) in a particular spreadsheet with only
knowing the spreadsheet name and location?

 

[Bob Phillips]

Kirk,

Here is another approach that works on closed workbooks. This function
returns an array of sheet names.

Use like

aSheets = SheetsADOX("C:\Mytest\Book1.xls")

Function SheetsADOX(FileName As String) As Variant
Dim oConn As Object
Dim oCat As Object
Dim oTable As Object
Dim sConnString As String
Dim sFileName As String
Dim sTableName As String
Dim aSheets As Variant
Dim cLength As Integer
Dim iTestPos As Integer
Dim iStartpos As Integer

Dim i As Long

sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & FileName & ";" & _
"Extended Properties=Excel 8.0;"

Set oConn = CreateObject("ADODB.Connection")
On Error Resume Next
oConn.Open sConnString
If Err.Number <> 0 Then
SheetsADOX = ""
Exit Function
Else
On Error GoTo 0
Set oCat = CreateObject("ADOX.Catalog")
Set oCat.ActiveConnection = oConn

ReDim aSheets(oCat.Tables.Count - 1)
For Each oTable In oCat.Tables
sTableName = oTable.Name
cLength = Len(sTableName)
iTestPos = 0
iStartpos = 1
'Worksheet name with embedded spaces are enclosed by single
quotes
If Left(sTableName, 1) = "'" And Right(sTableName, 1) = "'" Then
iTestPos = 1
iStartpos = 2
End If
'Worksheet names always end in the "$" character
If Mid$(sTableName, cLength - iTestPos, 1) = "$" Then
aSheets(i) = Mid$(sTableName, iStartpos, cLength -
(iStartpos + iTestPos))
i = i + 1
End If
Next oTable

End If

oConn.Close
Set oCat = Nothing
Set oConn = Nothing

SheetsADOX = aSheets

End Function

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Is there a way in which to determine the name of the tab(s) (through code)

in a particular spreadsheet with only knowing the spreadsheet name and
location?