Possible Istorage.Read Error

[David Maruca]

Probably it is because you both are using the deprecated StgOpenStorage function instead of StgOpenStorageEx. If I try to use StgOpenStorage in my computer, it returns a %STG_E_OLDFORMAT error.

Thanks for looking, Jose. I updated to StgOpenStorageEx but I still get the same behavior. Here is the updated section of code that uses StgOpenStorageEx.

Code: [Select]

    wszName = UCODE$("Test.xls" & $NUL)
    hr = StgOpenStorageEx(STRPTR(wszName), %STGM_SHARE_EXCLUSIVE OR %STGM_READ, _
        %STGFMT_STORAGE, 0, BYVAL %NULL, %NULL, $IID_IStorage, pStorage)
    IF FAILED(hr) THEN
      MSGBOX "StgOpenStorage failure: " & HEX$(hr)
      EXIT FUNCTION
    END IF
    wszName = UCODE$("Workbook" & $NUL)
    hr = pStorage.OpenStream(STRPTR(wszName), %NULL, %STGM_SIMPLE OR %STGM_READ OR %STGM_SHARE_EXCLUSIVE, %NULL, pStream)
    IF FAILED(hr) THEN
        wszName = UCODE$("Book" & $NUL)
        hr = pStorage.OpenStream(STRPTR(wszName), %NULL, %STGM_SIMPLE OR %STGM_READ OR %STGM_SHARE_EXCLUSIVE, %NULL, pStream)
        IF FAILED(hr) THEN
            MSGBOX "IStorage.OpenStream failure: " & HEX$(hr)
            EXIT FUNCTION
        END IF
    END IF

[Frederick J. Harris]

I haven't tested it yet but I'm just about sure I've found the source of the problem.  First let me state I tried your code and it works Jose.  However, only on your file created with your code.  If I use your code to read my file, it still doesn't work.  That of course suggested to me that the effect I'm seeing had something to do with the way I created the IStream.  Here are the flags I used...

Code: [Select]

grfMode=STGM_SIMPLE | STGM_CREATE | STGM_READWRITE | STGM_SHARE_EXCLUSIVE;
hr=StgCreateDocfile(szFile,grfMode,0,&pStorage);

I believe the STGM_SIMPLE flag is the culpret.

Code: [Select]

STGM_SIMPLE STGM_SIMPLE is a mode that provides a much faster implementation of a compound file in a limited, but frequently used case. It is described in detail in the following Remarks section.

This mode is useful for applications that perform complete save operations. It has the following constraints:

There is no support for substorages.  Access to streams follows a linear pattern. Once a stream is released, that stream cannot be opened for read/write
operations again. The IStorage::OpenStream method is not supported in this implementation.  The storage and stream objects cannot be marshaled.
Each stream is at least 4096 bytes in length. If fewer than 4096 bytes are written into a stream by the time the stream is released, the stream will be extended to contain 4096 bytes.   In this compound file implementation, only a subset of the methods of IStorage and IStream are available.
Specifically, in simple mode, supported IStorage methods are QueryInterface, AddRef, Release, CreateStream, Commit, and SetClass. In addition, SetElementTimes is supported with a NULL name, allowing applications to set times on a root storage in simple mode.

Supported IStream methods are QueryInterface, AddRef, Release, Seek, and SetSize. Also, Read and Write methods on ISequentialStream are supported.

I don't think the StgCreateDocfile() verses StgCreateDocfileEx() is the problem.  I'll test it now but I'm about sure that's it.

[Frederick J. Harris]

Did you know you can use ODBC to read *.xls data David (doesn't even require Excel to be installed)?

[David Maruca]

Did you know you can use ODBC to read *.xls data David (doesn't even require Excel to be installed)?

Yea, but it is inefficient for my needs. I run table detection, data validation, and some cleaning routines on the data I import. Importing ODBC into my structs and then back into another DB just is not any faster IMO. The problem becomes even worse when I have to work with large files. The memory has to be loaded from disk or network and then moved before I can even work with it, and I might not even need all of it. You just can't beat being able to read the files directly and BIFF is far superior to xlsx because you can grab one cell value out of a 1GB file with barely any parsing.

[Frederick J. Harris]

Are you familiar with the Range.Value2 property? It's the fastest way to extract from and put into Excel files using automation.

No, I'll have to check it out.

[Frederick J. Harris]

Say David, How hard is it using your BIFF file access techniques is it to create a blank *.xls file, the way it would be if you just opened Excel and got a blank Book1.xls and saved it?

I fooled around with this quite a bit using ODBC but wasn't successful.  You can create a blank Microsoft Access database with ODBC (SQLConfigDataSource() I think), but all I could get out of ODBC in that regard (with Excel) was the creation of a blank directory of all things!

[David Maruca]

No, I'll have to check it out.

Here is a VBA example. Keep in mind that the array will always have 2 dimensions and that it will always be "1 to ..." Keep this in mind because variable(x, y) does not always correspond to row, col. Try this on a very large range and you will be impressed.

Code: [Select]

Sub ArrayExample()
    Dim ArrayVals() As Variant
    Dim sht As Worksheet
    Dim row As Long
    Dim col As Long
    
    Set sht = ActiveSheet
    
    ''Populating example
    'Create an array, fill it, and put it in excel
    'The column must be declared even if there is only one. It would be ArrayVals(1 to MAXROWS, 1 to MAXCOLS) in all cases.
    ReDim ArrayVals(1 To 65536, 1 To 32) As Variant
    For row = 1 To 65536
        For col = 1 To 32
            ArrayVals(row, col) = row * col
        Next
    Next
    sht.Range("A1:AF65536").Value2 = ArrayVals
    
    ''Reading in example
    'This reads in the used range into a 2 dimension array
    ArrayVals = sht.UsedRange.Value2
    For row = 1 To UBound(ArrayVals, 1)
        For col = 1 To UBound(ArrayVals, 2)
            If IsNumeric(ArrayVals(row, col)) Then
                ArrayVals(row, col) = ArrayVals(row, col) * 2
            End If
        Next
    Next
    sht.UsedRange.Value2 = ArrayVals
End Sub

[David Maruca]

Say David, How hard is it using your BIFF file access techniques is it to create a blank *.xls file, the way it would be if you just opened Excel and got a blank Book1.xls and saved it?

I fooled around with this quite a bit using ODBC but wasn't successful.  You can create a blank Microsoft Access database with ODBC (SQLConfigDataSource() I think), but all I could get out of ODBC in that regard (with Excel) was the creation of a blank directory of all things!

I haven't got to that yet. So far I'm just reading files. I haven't seen a hello world example.

[Frederick J. Harris]

Thanks David.  I'll check that out.

I'm like a bull in a china shop.  I came into this thread with a half baked idea of what might be wrong with David's code, and as far as I know I'm about the only one who's benifited from all this.  David's still got his original problem and Jose mostly fixed mine.