Creating Excel sheets from Access

[MartinF]

Hi,

I have some code that builds up a formatted Excel spreadsheet entirely from data in Access tables.

Each cell is written and formatted as it goes, and whilst it works perfectly, it takes an absoulte age to process. There are approx 75 columns of data, and a sheet of 350ish rows takes well over half an hour to produce. Are there any ways or tricks that i can use to speed up the processing of Excel sheets up. I have checked through the Access code to make sure that it is efficient as possible, but its still painfully slow.

Any ideas?

 

[Craig0201]

Martin,

How about exporting it simply using TransferSpreadsheet and then creating an Excel macro to do the formatting for you?

Two stage process but almost certainly far quicker.

Craig

 

[Databaseguy]

Martin,

At the beginning of the sub-routine add:

objExcel.ScreenUpdating = False
objExcel.Application.Calculation = xlManual

And at the end

objExcel.ScreenUpdating = True
objExcel.Application.Calculation = xlAutomatic
objExcel.Calculate

I just trimmed a 6 minute routine into a minute with these changes...


Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy