Populate Excel spreadsheet

[Anesthaesia]

Hi All,

I need to populate an Excel spreadsheet with a large amount of data. Everything is working fine, but it takes ages to write the entries.

As the data needs to be a in a specific order its going into an array and then to Excel using "oSheet.Cells(nRow, nCol).Value = strData"

Is there any way to speed this up? It takes about 2sec to write one row (32 columns) and there will be about 800 rows, which means over 20 minutes to do a single sheet.

Any suggestions on how to deal with this would be great (VB6 & Excel 2003)

 

[ousoonerjoe]

Pass it the recordset and have it load from there automatically.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[SkipVought]

Check out the CopyFromRecordset method.

"As the data needs to be a in a specific order its going into "

You realize that you can sort in Excel if required.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Anesthaesia]

After doing a bit of searching it seems this is known issue and the best way to do it is dropping an array straight into a worksheet.

My VBA knowledge is limited so if anyone could give me an example of doing this I am sure it is what I am after.

Thanks to those that replied, but neither of these are options.

 

[SkipVought]

How are you loading the array?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

I mean, from what source?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Swi]

As Skip says, what source?

Take a look at this link if you are loading from Access or another database as it uses ADO to write the records to Excel. You will find it quick and you would not have to mess with Excel Automation and you could sort the records in your SQL statement on retrieval.

thread222-1452603

Swi