Started by David Maruca, November 17, 2010, 03:10:55 AM
0 Members and 1 Guest are viewing this topic.
Quote from: José Roca on November 17, 2010, 08:52:32 PMProbably 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.
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
grfMode=STGM_SIMPLE | STGM_CREATE | STGM_READWRITE | STGM_SHARE_EXCLUSIVE;hr=StgCreateDocfile(szFile,grfMode,0,&pStorage);
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.
Quote from: Frederick J. Harris on November 17, 2010, 10:03:31 PMDid you know you can use ODBC to read *.xls data David (doesn't even require Excel to be installed)?
QuoteAre you familiar with the Range.Value2 property? It's the fastest way to extract from and put into Excel files using automation.
Quote from: Frederick J. Harris on November 17, 2010, 10:40:26 PMNo, I'll have to check it out.
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 = ArrayValsEnd Sub
Quote from: Frederick J. Harris on November 17, 2010, 10:52:48 PMSay 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!
Page created in 0.130 seconds with 22 queries.