Writing to a excel spreadsheet is slow.

[Pat Lenahan]

I've created a VB6 program to read files and post them to an excel
spreadsheet. But, when it posts to the spreadsheet its slow. About 1/2 a
second per line. Is this unusual or is there a better way or is this normal?

Pat

 

[Pat Lenahan]

Is there a way to turn security and or validation off. Excel cant read it
from a file because I do some complex code to get the information I need
accessing a clarion database.

Pat

 

[Michael D. Ober]

If the files are in a form that Excel can import directly, look at the Excel
Open* functions and have Excel read the lines directly. Excel is an "out of
process" automation object, which requires that every call to it go though a
whole slew of security and process boundary change validation and support
code in Windows, which is why it's so slow.

Mike.

 

[Michael D. Ober]

Unfortunately not. The security and cross process interfaces are built into
windows itself.

Mike.

 

[Gerrit]

Have you disabled recalculation en refresh screen:

BEFORE
Dim lCalc As Integer
lCalc = Application.Calculation
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
................
.............
................
..................

aFTER
Application.Calculation = lCalc
Application.ScreenUpdating = True

 

[Jake Marx]

Hi Pat,

How are you getting the data? If you are using some type of data access
method (ADO for example), you may be able to dump the resulting recordset
into Excel in one command, which would be *much* faster. Look at the
CopyFromRecordset method of the Range object.

Alternatively, if your data is stored in an array, you can dump the values
from that array into an Excel range in one step as well.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]