Fastest way of getting data into excel

[Guest]

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

 

[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

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?

 

[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

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?

 

[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.

 

[Guest]

Thanks everyone.....the array method seems perfect