Show worksheet name

  • Thread starter Thread starter JohnB
  • Start date Start date
J

JohnB

Could you please help.


Is it possible to show the workbook name and all worksheet
names of a closed xla and have them listed in a
spreadsheet.

I have looked at Mr Pearsons website but cannot find
anything regarding this matter.


Thank you for your help.
JB
 
John,

Here's some VBA that will read the sheet names from a workbook.

Dim objConn As ADODB.Connection
Dim objCat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim iRow As Long
Dim sWorkbook As String
Dim sConnString As String
Dim sTableName As String
Dim cLength As Integer
Dim iTestPos As Integer
Dim iStartpos As Integer

sWorkbook = "c:\myTest\bob.xls"
sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sWorkbook & ";" & _
"Extended Properties=Excel 8.0;"

Set objConn = New ADODB.Connection
objConn.Open sConnString
Set objCat = New ADOX.Catalog
Set objCat.ActiveConnection = objConn

iRow = 1
For Each tbl In objCat.Tables
sTableName = tbl.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
Cells(iRow, 1) = Mid$(sTableName, iStartpos, cLength -
(iStartpos + iTestPos))
iRow = iRow + 1
End If
Next tbl
objConn.Close
Set objCat = Nothing
Set objConn = Nothing

You will also need to setup a reference to the Microsoft ADO Data Objects
vn.n library, and the Microsoft ADO Ext [version] For DDL & Security
library, the file is msadox.dll.
 
Thank you Bob

I'll start work on that straight away.
Sorry for not replying straight away. I could not see your
response until this morning.

JB
-----Original Message-----
John,

Here's some VBA that will read the sheet names from a workbook.

Dim objConn As ADODB.Connection
Dim objCat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim iRow As Long
Dim sWorkbook As String
Dim sConnString As String
Dim sTableName As String
Dim cLength As Integer
Dim iTestPos As Integer
Dim iStartpos As Integer

sWorkbook = "c:\myTest\bob.xls"
sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sWorkbook & ";" & _
"Extended Properties=Excel 8.0;"

Set objConn = New ADODB.Connection
objConn.Open sConnString
Set objCat = New ADOX.Catalog
Set objCat.ActiveConnection = objConn

iRow = 1
For Each tbl In objCat.Tables
sTableName = tbl.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
Cells(iRow, 1) = Mid$(sTableName, iStartpos, cLength -
(iStartpos + iTestPos))
iRow = iRow + 1
End If
Next tbl
objConn.Close
Set objCat = Nothing
Set objConn = Nothing

You will also need to setup a reference to the Microsoft ADO Data Objects
vn.n library, and the Microsoft ADO Ext [version] For DDL & Security
library, the file is msadox.dll.


--

HTH

Bob Phillips

JohnB said:
Could you please help.


Is it possible to show the workbook name and all worksheet
names of a closed xla and have them listed in a
spreadsheet.

I have looked at Mr Pearsons website but cannot find
anything regarding this matter.


Thank you for your help.
JB
Click to expand...


.
Click to expand...
 
onedaywhen said:
Bob,
I noticed your code is commented "Worksheet names always end in the
'$' character". I thought this too, but I have a workbook where one of
the sheets comes up as 'SheetName$_'. Do you have any idea why this
might be? If you do, please post back.
Click to expand...

I have never come across this, what is the worksheet called, anything
special? I have tried numerous things, add a space to the end, and a $, but
they all still come back ending with $.

BTW your approach only requires ADOX. The ADODB.Connection object is
unnecessary because the ADOX.Catalog object's ActiveConnection
property accepts a connection string. Alternatively you could use just
ADODB by using the OpenSchema method.
Click to expand...

Good point. It makes the code shorter, more immediate, less intensive and 1
less reference. Nice one!

Bob
 
I have two sheets, one called dDetails and another called Details
(CodeName and Name properties are the same for both sheets). The
Details sheet is returned as a table called
Details$_. There are various sheet level defined names e.g.
Details!Surname and I'm thinking this is what is causing the anomalies
(perhaps it's picking up one of Excel's defined names such as
Print_Area?) Another sheet was just now showing an anomaly: the table
name was coming up as
Sheet2$Address, and there's a defined name on that sheet called
Sheet2!Address. I don't think it's a problem, just a curiosity, but
makes it worth using the InStr function rather than testing from the
right.
 
Back
Top