[keepITcool]

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

[Ivan F Moala[_38_]]

Hi KeepItCool

Not sure if this helps you.....
The BOUNDSHEET record is formated as

85 0 0E 0 63 1B 0 0 0 0 6 0 Sheet name follows

where you have correctly ID the BOUNDSHEET ID = 85 hex (Constant ID)

and

0E = (variable) the offset to the next BOUNDSHEET data (hex 85)
6 = (variable) the Length of the sheet name.

So I gues you could search for this pattern

85 0 <byte 0 <byte <byte 0 0 0 0 <byte

--
Ivan F Moal

-----------------------------------------------------------------------
Ivan F Moala's Profile: http://www.excelforum.com/member.php...nfo&userid=195
View this thread: http://www.excelforum.com/showthread.php?threadid=27473

[keepITcool]

Ivan thnx.

I had already figured a needed to append a nullchar in my search.
so search an 85 00. And I realize I should dig a little deeper before
exiting the First Do/Loop

It's the BYTE 9 in the series that indicates the sheet type.
problem remains that for Dialog Sheets it will indicate a 0,
denoting a Worksheet.

How do i decide what's truely a Worksheet.



keepITcool

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


Ivan F Moala wrote :


Hi KeepItCool

Not sure if this helps you.....
The BOUNDSHEET record is formated as

85 0 0E 0 63 1B 0 0 0 0 6 0 Sheet name follows

where you have correctly ID the BOUNDSHEET ID = 85 hex (Constant ID)

and

0E = (variable) the offset to the next BOUNDSHEET data (hex 85)
6 = (variable) the Length of the sheet name.

So I gues you could search for this pattern

85 0 <byte 0 <byte <byte 0 0 0 0 <byte 0

[No_Good_At_All]

I'm no good at vba but tried to take a closer look.......

Looking for the wrong value?

I changed the code a bit to write the values to the sheet and i also added a
dialog sheet, a xl4 macro sheet etc.
When i look at the written values i see that aByt(6) is a zero for every
sheet, except for the worksheets.
If it's a worksheet, the value is greater than zero.

Hope this helps




"keepITcool" schreef in bericht
...

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

[keepITcool]

Thanks for the suggestion, but alas....

As far as i can figure..Offset 4 thru 8 are a long representing the
position of the worksheet stream in the file. The fact that offset 6
is 0 means the stream is further down the file..


keepITcool

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


"No_Good_At_All" wrote :

I'm no good at vba but tried to take a closer look.......

Looking for the wrong value?

I changed the code a bit to write the values to the sheet and i also
added a dialog sheet, a xl4 macro sheet etc.
When i look at the written values i see that aByt(6) is a zero for
every sheet, except for the worksheets.
If it's a worksheet, the value is greater than zero.

Hope this helps




"keepITcool" schreef in bericht
...

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

[No_Good_At_All]

Search on the internet came up with a VB 6.0 programm to write a workbook
without Excel.
This code came from the source project witch you will find he

http://www.freevbcode.com/Source/006/vb%20Excel.zip

This link comes from:
http://www.freevbcode.com/ShowCode.Asp?ID=6856

Here's a bit of code from this source...........

' Procedure : Function Serialize
'---------------------------------------------------------------------------
------------
Public Function Serialize() As Byte()
Dim b() As Byte
Dim bname() As Byte

recLen = (Len(field_4_sheet_name)) + 12
ReDim b(recLen - 1)

CopyMemory b(0), ByVal VarPtr(ID), 2 ''' record
ID
b(2) = UBound(b) - 3 ''' Record
Length

CopyMemory b(4), ByVal VarPtr(field_1_stream_Position), 4 ''' Stream
Position
CopyMemory b(8), ByVal VarPtr(field_2_visibility), 1 '''
Visibility

b(9) = field_3_sheet_type ''' sheet
type
b(10) = Len(field_4_sheet_name) ''' Name
length

b(11) = 0 ''' Unicode
flag (compressed String)
bname = StrConv(field_4_sheet_name, vbFromUnicode)
CopyMemory b(12), bname(0), UBound(bname) + 1 ''' sheet
name

Serialize = b
End Function

--
Maybe this will help....................

Greetz,

No_Good_At_All





"keepITcool" schreef in bericht
...
Thanks for the suggestion, but alas....

As far as i can figure..Offset 4 thru 8 are a long representing the
position of the worksheet stream in the file. The fact that offset 6
is 0 means the stream is further down the file..


keepITcool

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


"No_Good_At_All" wrote :

I'm no good at vba but tried to take a closer look.......

Looking for the wrong value?

I changed the code a bit to write the values to the sheet and i also
added a dialog sheet, a xl4 macro sheet etc.
When i look at the written values i see that aByt(6) is a zero for
every sheet, except for the worksheets.
If it's a worksheet, the value is greater than zero.

Hope this helps




"keepITcool" schreef in bericht
...

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

[keepITcool]

Yes,

I have google too:) and have that code.
I also have ExcelFileFormat.pdf from OpenOffice.org

I know how to parse the BoundSheet Record.
I tried to explain in my original post, the problem is NOT how to parse
or read a BoundSheet record.. It is HOW to Distinguish a Worksheet from
a DialogSheet, as that information is NOT in that record.

I'll have to look into Reading the BOF header of the Worksheet stream.
I'll figure it out..

Thx anyway...

keepITcool

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


"No_Good_At_All" wrote :

Search on the internet came up with a VB 6.0 programm to write a
workbook without Excel.
This code came from the source project witch you will find he

http://www.freevbcode.com/Source/006/vb%20Excel.zip

This link comes from:
http://www.freevbcode.com/ShowCode.Asp?ID=6856

Here's a bit of code from this source...........

[Ivan F Moala[_39_]]

Hi keepitcool

sorry misread the Q.

Not sure that there is a diff in the BOUMDSHEET, but when I look @ the
diff between a Dialog Sheet and a Worksheets streams
they show

INDEX INDEX
CALCMODE CALCMODE
CALCCOUNT CALCCOUNT
REFMODE REFMODE
ITERATION ITERATION
DELTA DELTA
SAFERECALC SAFERECALC
PRINTHEADERS PRINTHEADERS
PRINTGRIDLINES PRINTGRIDLINES
GRIDSET GRIDSET
GUTS GUTS
DEFAULTROWHEIGHT DEFAULTROWHEIGHT
WSBOOL WSBOOL
HEADER HEADER
FOOTER FOOTER
HCENTER HCENTER
VCENTER VCENTER
SETUP SETUP
PROTECT DEFCOLWIDTH
DEFCOLWIDTH DIMENSIONS
DIMENSIONS ROW
MSODRAWING ROW
OBJ RK
FORMULA

So perhaps looking for differences, in this case
ROW and FORMULA

I beleive the ID is

ROW = hex 208
FORMULA = hex 006

???


keepITcool Wrote:
Ivan thnx.

I had already figured a needed to append a nullchar in my search.
so search an 85 00. And I realize I should dig a little deeper before
exiting the First Do/Loop

It's the BYTE 9 in the series that indicates the sheet type.
problem remains that for Dialog Sheets it will indicate a 0,
denoting a Worksheet.

How do i decide what's truely a Worksheet.



keepITcool

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


Ivan F Moala wrote :


Hi KeepItCool

Not sure if this helps you.....
The BOUNDSHEET record is formated as

85 0 0E 0 63 1B 0 0 0 0 6 0 Sheet name follows

where you have correctly ID the BOUNDSHEET ID = 85 hex (Constant ID)

and

0E = (variable) the offset to the next BOUNDSHEET data (hex 85)
6 = (variable) the Length of the sheet name.

So I gues you could search for this pattern

85 0 <byte 0 <byte <byte 0 0 0 0 <byte 0



--
Ivan F Moal

-----------------------------------------------------------------------
Ivan F Moala's Profile: http://www.excelforum.com/member.php...nfo&userid=195
View this thread: http://www.excelforum.com/showthread.php?threadid=27473

[RB Smissaert]

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

[RB Smissaert]

KeepITCool,

I have worked your function a bit further out and as other people may find
this useful I post it here.
I am sure you won't mind.

Function ReadSheets(sFullName As String, _
Optional bWorksheetsOnly As Boolean = False, _
Optional bSheetTypeAsString As Boolean = True) As
Collection

'from a newsgroup posting by KeepITCool
'--------------------------------------------------------------------
'Returns a collection of 0-based one-dimensional arrays
'showing the sheet names in element 0 of the array
'and the sheet type in element 1 of the array
'0 = WorkSheet (dialog sheet will be 0 as well)
'2 = ChartSheet
'if bWorksheetsOnly = True it will only look at worksheets
'if bSheetTypeAsString = True it will show the sheet type as a string
'--------------------------------------------------------------------

Dim aByt() As Byte
Dim iTyp As Integer
Dim lHnd As Long
Dim lLen As Long
Dim lPos1 As Long
Dim lPos2 As Long
Dim sTxt As String
Dim sTyp As String
Dim cRes As Collection

Const IDboundsheet = &H85 '133
Const BuffSize = &H400 '1024

If Dir(sFullName) = vbNullString Then
Exit Function
End If

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
lPos1 = lPos1 + BuffSize - 1
Get lHnd, lPos1, aByt
lPos2 = InStrB(aByt, ChrB(IDboundsheet))
Loop While lPos2 = 0 And lPos1 < lLen

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

If bSheetTypeAsString = True Then
If iTyp = 0 Then
sTyp = "WorkSheet"
Else
sTyp = "ChartSheet"
End If
If bWorksheetsOnly = True Then
If iTyp = 0 Then
cRes.Add Array(sTxt, sTyp), sTxt
End If
Else
cRes.Add Array(sTxt, sTyp), sTxt
End If
Else
If bWorksheetsOnly = True Then
If iTyp = 0 Then
cRes.Add Array(sTxt, iTyp), sTxt
End If
Else
cRes.Add Array(sTxt, iTyp), sTxt
End If
End If

If aByt(aByt(2) + 4) < IDboundsheet Then
lPos2 = 0
Else
lPos2 = InStrB(4, aByt, ChrB(&H85))
End If
Loop

Close lHnd
Set ReadSheets = cRes

End Function


Sub TestReadSheets()

Dim arr
Dim c As Byte
Dim i As Long
Dim col As Collection

Set col = ReadSheets("C:\ExcelFiles\Test.xls")

For i = 1 To col.Count
arr = col.Item(i)
For c = 0 To 1
Cells(i, c + 1) = arr(c)
Next
Next

End Sub


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

[keepITcool]

Ivan, Thanks...

Maybe I'll give it another go later... but I dont think so.

Analysing the entire stream is going to complicate things beyond the
intended scope of the function. I have no intention of creating a
fullfledged BiffReader <g

For the purpose of reading datafiles to translate sheetindex to sheetname
I'll have to assume there are NO dialogsheets in the books.

Following is the function as is.


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

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


Ivan F Moala wrote :


Hi keepitcool

sorry misread the Q.

Not sure that there is a diff in the BOUMDSHEET, but when I look @ the
diff between a Dialog Sheet and a Worksheets streams
they show

INDEX INDEX
CALCMODE CALCMODE
CALCCOUNT CALCCOUNT
REFMODE REFMODE
ITERATION ITERATION
DELTA DELTA
SAFERECALC SAFERECALC
PRINTHEADERS PRINTHEADERS
PRINTGRIDLINES PRINTGRIDLINES
GRIDSET GRIDSET
GUTS GUTS
DEFAULTROWHEIGHT DEFAULTROWHEIGHT
WSBOOL WSBOOL
HEADER HEADER
FOOTER FOOTER
HCENTER HCENTER
VCENTER VCENTER
SETUP SETUP
PROTECT DEFCOLWIDTH
DEFCOLWIDTH DIMENSIONS
DIMENSIONS ROW
MSODRAWING ROW
OBJ RK
FORMULA

So perhaps looking for differences, in this case
ROW and FORMULA

I beleive the ID is

ROW = hex 208
FORMULA = hex 006

???


keepITcool Wrote:
Ivan thnx.

I had already figured a needed to append a nullchar in my search.
so search an 85 00. And I realize I should dig a little deeper before
exiting the First Do/Loop

It's the BYTE 9 in the series that indicates the sheet type.
problem remains that for Dialog Sheets it will indicate a 0, denoting
a Worksheet.

How do i decide what's truely a Worksheet.



keepITcool

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


Ivan F Moala wrote :


Hi KeepItCool

Not sure if this helps you.....
The BOUNDSHEET record is formated as

85 0 0E 0 63 1B 0 0 0 0 6 0 Sheet name follows

where you have correctly ID the BOUNDSHEET ID = 85 hex (Constant ID)

and

0E = (variable) the offset to the next BOUNDSHEET data (hex 85)
6 = (variable) the Length of the sheet name.

So I gues you could search for this pattern

85 0 <byte 0 <byte <byte 0 0 0 0 <byte 0

[keepITcool]

Bart..

see my revised code in the first branch of this thread
it does a few checks to decide if the file has the correct structure.

i note you've replaced chrW for chrB in your search for the recordID..
as you MUST ensure the 85H is followed by 00H, this is not a good idea.

since you 'decode' the types you should have included type 1 (Macro sheets)

and dont be too sure I dont mind.. you're playing around with MY original!
:lol

keepITcool

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


"RB Smissaert" wrote :

KeepITCool,

I have worked your function a bit further out and as other people may
find this useful I post it here.
I am sure you won't mind.

[RB Smissaert]

KeepITCool,

i note you've replaced chrW for chrB in your search for the recordID..

Can't remember doing that (wouldn't dare!), but I will take your new
function and alter that one.
I just prefer the standard VBA notation of variables and If .... Then
constructions,
plus I like the option to get the sheet type as a string.

RBS



"keepITcool" wrote in message
...
Bart..

see my revised code in the first branch of this thread
it does a few checks to decide if the file has the correct structure.

i note you've replaced chrW for chrB in your search for the recordID..
as you MUST ensure the 85H is followed by 00H, this is not a good idea.

since you 'decode' the types you should have included type 1 (Macro
sheets)

and dont be too sure I dont mind.. you're playing around with MY original!
:lol

keepITcool

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


"RB Smissaert" wrote :

KeepITCool,

I have worked your function a bit further out and as other people may
find this useful I post it here.
I am sure you won't mind.

[keepITcool]

yep.. screwed up my naming conventions :(
and.. chrB was in my first attempt.

Over the weekend I just may try to read a few more
items from the file.(Document Properties etc)

however this Biff8 has me stumped...
i find it awfully complex.



keepITcool

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


"RB Smissaert" wrote :

KeepITCool,

i note you've replaced chrW for chrB in your search for the recordID..

Can't remember doing that (wouldn't dare!), but I will take your new
function and alter that one.
I just prefer the standard VBA notation of variables and If .... Then
constructions,
plus I like the option to get the sheet type as a string.

RBS

[Ivan F Moala[_44_]]

Hi keepITcool

Thanks, I'll keep that function handy.
Might look into a BIFF reader <g

keepITcool Wrote:
Ivan, Thanks...

Maybe I'll give it another go later... but I dont think so.

Analysing the entire stream is going to complicate things beyond the
intended scope of the function. I have no intention of creating a
fullfledged BiffReader <g

For the purpose of reading datafiles to translate sheetindex t
sheetname
I'll have to assume there are NO dialogsheets in the books.

Following is the function as is.


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

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


Ivan F Moala wrote :


Hi keepitcool

sorry misread the Q.

Not sure that there is a diff in the BOUMDSHEET, but when I look
the
diff between a Dialog Sheet and a Worksheets streams
they show

INDEX INDEX
CALCMODE CALCMODE
CALCCOUNT CALCCOUNT
REFMODE REFMODE
ITERATION ITERATION
DELTA DELTA
SAFERECALC SAFERECALC
PRINTHEADERS PRINTHEADERS
PRINTGRIDLINES PRINTGRIDLINES
GRIDSET GRIDSET
GUTS GUTS
DEFAULTROWHEIGHT DEFAULTROWHEIGHT
WSBOOL WSBOOL
HEADER HEADER
FOOTER FOOTER
HCENTER HCENTER
VCENTER VCENTER
SETUP SETUP
PROTECT DEFCOLWIDTH
DEFCOLWIDTH DIMENSIONS
DIMENSIONS ROW
MSODRAWING ROW
OBJ RK
FORMULA

So perhaps looking for differences, in this case
ROW and FORMULA

I beleive the ID is

ROW = hex 208
FORMULA = hex 006

???


keepITcool Wrote:
Ivan thnx.

I had already figured a needed to append a nullchar in my search.
so search an 85 00. And I realize I should dig a little deeper
before
exiting the First Do/Loop

It's the BYTE 9 in the series that indicates the sheet type.
problem remains that for Dialog Sheets it will indicate a 0,
denoting
a Worksheet.

How do i decide what's truely a Worksheet.



keepITcool

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


Ivan F Moala wrote :


Hi KeepItCool

Not sure if this helps you.....
The BOUNDSHEET record is formated as

85 0 0E 0 63 1B 0 0 0 0 6 0 Sheet name follows

where you have correctly ID the BOUNDSHEET ID = 85 hex (Constant
ID)

and

0E = (variable) the offset to the next BOUNDSHEET data (hex 85)
6 = (variable) the Length of the sheet name.

So I gues you could search for this pattern

85 0 <byte 0 <byte <byte 0 0 0 0 <byte 0






--
Ivan F Moala


------------------------------------------------------------------------
Ivan F Moala's Profile: http://www.excelforum.com/member.php...fo&userid=1954
View this thread: http://www.excelforum.com/showthread...hreadid=274733

[Jamie Collins]

keepITcool wrote

Over the weekend I just may try to read a few more
items from the file.(Document Properties etc)

Doing a comparison, I noticed ADO does not expose the worksheet name
as a table if the worksheet is hidden. Defined Names, including
worksheet-level Names, are still exposed and the data can still be
queried as usual using the worksheet name.

Jamie.

--

[keepITcool]

i'll build in a visibility checker :)

keepITcool

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


(Jamie Collins) wrote :

keepITcool wrote

Over the weekend I just may try to read a few more items from the
file.(Document Properties etc)

Doing a comparison, I noticed ADO does not expose the worksheet name
as a table if the worksheet is hidden. Defined Names, including
worksheet-level Names, are still exposed and the data can still be
queried as usual using the worksheet name.

Jamie.

--