Get worksheet names
 

Hi,

I use the OleDb objects to connect to an excel file and retrieve data from a
specified worsheet.

What SELECT statement should I use (if there is an existing one) to retrieve
the names of the worksheets in the file?

Thanks,
Mircea

Get worksheet names
 

Mircea,


JetSQL doesnt allow to read the table names,
you can use ADOX or ADODB.OpenSchema(adSchemaTables)
BUT you'll get a mix of named ranges and
no way to get the sheets by their index...

So:
I've written following function a while back..

topic: Reading Sheet Names/Index from closed file using Biff8.
date : Nov 4,2004

hth :)


Function ReadSheets(sFileName As String, _
Optional bWorksheetsOnly As Boolean = True) As Collection
'Returns a collection of the sheets in a workbook
..
'Note: DialogSheets are returned as Worksheets!

'keepITcool 4 Nov 2004, excel.programming


Dim lHnd&, lLen&, lPos&, iPos&, iErr%, bTyp As Byte, sTxt$, iIdx&
Dim aByt() As Byte, cRes As Collection


Const IDbof = &H809
Const IDbiff8 = &H10
Const IDboundsheet = &H85
Const BuffSize = &H400


If Dir(sFileName) = vbNullString Then iErr = 1: GoTo theExit


ReDim aByt(0 To BuffSize)
lLen = FileLen(sFileName)
lHnd = FreeFile


Open sFileName For Binary Access Read As #lHnd Len = BuffSize


'Is it OLE structured?
If Input(6, #lHnd) < "ÐÏࡱ" Then iErr = 2: GoTo theExit


'Is it Biff8?
Do
lPos = lPos + BuffSize - 1
Get #lHnd, lPos, aByt
iPos = InStrB(aByt, ChrW$(IDbof) & ChrW$(IDbiff8))
Loop While iPos = 0 And lPos < lLen
If iPos = 0 Then iErr = 3: GoTo theExit


'Find the BoundSheets...
lPos = 0: iPos = 0
Do
lPos = lPos + BuffSize - 1
Get #lHnd, lPos, aByt
iPos = InStrB(aByt, ChrW$(IDboundsheet))
Loop While iPos = 0 And lPos < lLen
If iPos = 0 Then iErr = 4: GoTo theExit


Set cRes = New Collection
Do
lPos = lPos + iPos - 1
Get #lHnd, lPos, aByt
bTyp = aByt(9) 'types: 0WKS/1MACRO/2CHART
Debug.Assert aByt(11) = 0
sTxt = Mid(StrConv(aByt, vbUnicode), 12 + 1, aByt(10))
If bWorksheetsOnly Imp bTyp = 0 Then
iIdx = iIdx + 1
'Add an array index/name/type
cRes.Add Array(iIdx, sTxt, bTyp), sTxt
End If



If aByt(aByt(2) + 4) < IDboundsheet Then
iPos = 0
Else
iPos = InStrB(4, aByt


, ChrW$(IDboundsheet))
End If
Loop While iPos 0

theExit:
If lHnd Then Close #lHnd
Set ReadSheets = cRes
If iErr Then
Select Case iErr
Case 1: sTxt = "File not found."
Case 2: sTxt = "Unrecognised file format."
Case 3: sTxt = "Nof a Biff8 file format. Older Excel version?"
Case Else: sTxt = "Error analysing file."
End Select
On Error Resume Next
'let Err indicate what went wrong
Err.Raise vbObjectError + iErr, , sTxt
End If


End Function






--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Mircea Pleteriu wrote :

Hi,

I use the OleDb objects to connect to an excel file and retrieve data
from a specified worsheet.

What SELECT statement should I use (if there is an existing one) to
retrieve the names of the worksheets in the file?

Thanks,
Mircea

Get worksheet names
 

I got it by means of OleDbConnectionGetOleDbSchemaTable method.

"Mircea Pleteriu" wrote in message
...
Hi,

I use the OleDb objects to connect to an excel file and retrieve data from
a
specified worsheet.

What SELECT statement should I use (if there is an existing one) to
retrieve
the names of the worksheets in the file?

Thanks,
Mircea

Get worksheet names
 

Something along the lines of this article perhaps?

http://support.microsoft.com/default...b;en-us;318373
HOW TO: Retrieve Metadata from Excel by Using the GetOleDbSchemaTable Method
in Visual Basic .NET

--
Regards,
Tom Ogilvy


"Mircea Pleteriu" wrote in message
...
I got it by means of OleDbConnectionGetOleDbSchemaTable method.

"Mircea Pleteriu" wrote in message
...
Hi,

I use the OleDb objects to connect to an excel file and retrieve data
from
a
specified worsheet.

What SELECT statement should I use (if there is an existing one) to
retrieve
the names of the worksheets in the file?

Thanks,
Mircea

Get worksheet names
 

AA2e72E wrote:
Try this solution to enumerate Sheet names or defined names or ALL.

Case "SHEETS"
If 0 < InStr(Table.Name, "$") Then NamesinXLS =
NamesinXLS
& "," & Table.Name
Case "NAMES"
If 0 = InStr(Table.Name, "$") Then NamesinXLS =
NamesinXLS &
"," & Table.Name

This code list worksheet-level defined Names as SHEETS :(

Jamie.

--