KeepITCool,

It is a nice little function as it is and the need to distinguish between a
worksheet and a dialog sheet won't be needed often.
Would it always work fine as it is now to get the sheetnames and the sheet
types; that is worksheet or chartsheet?

RBS


"keepITcool" wrote in message
...

Hi guys

ADO (Jet) needs Sheet Names and cannot use indexes. (Since tables in
databases would have no 'ordinal' position)

To give myself a bit (or byte) of flexibility I'm trying to come up with a
little structure reader for closed files. I've got a nice simple
routine... Problem is it will recognize a DialogSheet as a Worksheet

To be precise it returns a 0 (worksheet) in byte 9 of the BoundsSheet
record...

So I assume I've got to jump to the stream itself.. but I haven;t figured
out how (yet) I presume I must use the Long at offset 4 in the record as
my target address?

Anyone any ideas? (or working code?)
My code below.. Now a bad start as is :)


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Function ReadSheets(sFullName As String, _
Optional bWorksheetsOnly As Boolean = True) As Collection
'Returns a collection of the sheets in a workbook.

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

Const IDboundsheet = &H85
Const BuffSize = &H400

If Dir(sFullName) = vbNullString Then Exit Function

Set cRes = New Collection
ReDim aByt(0 To BuffSize)
lLen = FileLen(sFullName)
lHnd = FreeFile

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

Do
lPos = lPos + BuffSize - 1
Get #lHnd, lPos, aByt
iPos = InStrB(aByt, ChrB(IDboundsheet))
Loop While iPos = 0 And lPos < lLen

Do While iPos 0
lPos = lPos + iPos - 1
Get #lHnd, lPos, aByt
sTxt = Mid(StrConv(aByt, vbUnicode), 13, aByt(10))
iTyp = aByt(9)

cRes.Add Array(sTxt, iTyp), sTxt
If aByt(aByt(2) + 4) < IDboundsheet Then
iPos = 0
Else
iPos = InStrB(4, aByt, ChrB(&H85))
End If
Loop

Close #lHnd
Set ReadSheets = cRes

End Function