MrExcel Publishing
Your One Stop for Excel Tips & Solutions

FAST import

Posted by Luc Rogge on November 03, 2000 1:06 PM

I made an ActiveX DLL in VB to feed data into an Excel Worksheet from a proprietary file.
If there is about a thousand rows, it takes 10 minutes on a 700Mhz machine!!!
How can I optimize it?
What is the fastest way to feed data into a sheet?
Should I convert my file into text delimited format and rely on native imports?

Posted by Ivan Moala on November 03, 2000 8:37 PM

One way to speed things up is by using Data arrays eg:
1) Mysheet is set to your sheet
2) DataArray has already been loaded into a
Variant Array eg.
Reads cells into variant array
DataArray = Range("MyRange").Resize(1000,1)

So to read it back into the sheet use;
MySheet.Range("A2").Resize(1000, 1).Value = DataArray

Reads in 1000 data val into a range @ A2 1000 rows
by 1 columns.

The diff in doing it this way as opposed to a For
Each Next loop can be dramatic depending on the
data and amount. This is due to the calls when using
a For Each Next loop to a range object. In this case it will do this call 1000 times as opposed to
just ONE call.

Just as a note, from Mshelp >If the function that you are calling from the DLL is performing operations on a Range, then a built-in worksheet function will probably perform faster than the external function. The reason for this is the overhead that is involved in transferring the contents of the range out of Microsoft Excel and into the external DLL. The built-in functions in Microsoft Excel can access the cell table much more efficiently than a DLL.


Posted by Luc Rogge on November 05, 2000 12:16 PM

Thanks Ivan,

It now takes 3 s. instead of 10 minutes!!!