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