Reading Sheet Names/Index from closed file using Biff8.

K

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
 
I

Ivan F Moala

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>
 
K

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 >
 
N

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
 
K

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 >
 
N

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 here:

http://www.freevbcode.com/Source/006/vb Excel.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
 
K

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 >
 
I

Ivan F Moala

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

???
 
R

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
 
R

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
 
K

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 >
 
K

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 >
 
R

RB Smissaert

KeepITCool,
i note you've replaced chrW for chrB in your search for the recordID..
Click to expand...

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
 
K

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 >
 
I

Ivan F Moala

Hi keepITcool

Thanks, I'll keep that function handy.
Might look into a BIFF reader <g>
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 >
Click to expand...
 
J

Jamie Collins

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

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.

--
 
Top