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