Fastest way of getting data into excel

J

jnc

Hi Everyone

I am writing an app at the moment which has to import a large amount of data (1m + cells) into excel and am currently using the excel object model to do this. I am using the cells value property to do this i.e 'objsheet.Cells(1,1).value = x'

I have noticed that usings the cell object model seem to be very slow is there a faster way of getting the data into excel

Cheers.....Ji
 
F

Frank Kabel

Hi
one way:
disable screenupdating and automatic calculation at the
beginning of your macro and enable it again after
inserting your values
-----Original Message-----
Hi Everyone,

I am writing an app at the moment which has to import a
Click to expand...
large amount of data (1m + cells) into excel and am
currently using the excel object model to do this. I am
using the cells value property to do this
i.e 'objsheet.Cells(1,1).value = x'.
I have noticed that usings the cell object model seem to
Click to expand...
be very slow is there a faster way of getting the data
into excel?
 
C

Charles Williams

put your data into an array and transfer the array to a range

dim varr(65536,256) as variant
'
' fill array with data
'
objsheet.range("a1:IV65536")=varr

Usually it pays to transfer the information in as large a block as possible


Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

jnc said:
Hi Everyone,

I am writing an app at the moment which has to import a large amount of
Click to expand...
data (1m + cells) into excel and am currently using the excel object model
to do this. I am using the cells value property to do this i.e
'objsheet.Cells(1,1).value = x'.
I have noticed that usings the cell object model seem to be very slow is
Click to expand...
there a faster way of getting the data into excel?
 
J

JWolf

You could create a temporary array in VBA and fill it. Then transfer
the array to a range in one statement:
DataRange.Value=TempArray
This is much faster than inserting into individual cells. Also see
Frank K's tips.
 
Top