Fast way to insert data from database
 

I wrote a VB program that retrive data from SQL server to make a excel
report. The report has some static text, such as report header, report
footer, etc. Besides I need to set the font size of certains columns,
therefore, I cannot directly use the ADO recordset directly.

To make the excel report, I need to add the data cell by cell using
WorkSheet.Cells() method, it works but its performance is slow.

Is there any way for me to inserting the data from database with good
performace?

Fast way to insert data from database
 

gh

How many records/columns? Try turning off screen updating and setting
calculation to manual.

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'your code

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Also, it may be faster to transfer the whole recordset to the worksheet,
then apply the formatting afterwards.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"gh" wrote in message
...
I wrote a VB program that retrive data from SQL server to make a excel
report. The report has some static text, such as report header, report
footer, etc. Besides I need to set the font size of certains columns,
therefore, I cannot directly use the ADO recordset directly.

To make the excel report, I need to add the data cell by cell using
WorkSheet.Cells() method, it works but its performance is slow.

Is there any way for me to inserting the data from database with good
performace?

Fast way to insert data from database
 

Just my $0.02...

I have recently begun copying the entire recordset to a blank workshee
and then stripping off everything I don't need. Using:

Range("A1").CopyFromRecordset objRS

You can delete the headers and other stuff and do formatting on a muc
larger scale (i.e. by column rather than by individual cell). Yo
could even set up a sheet with all of your formatting and then past
the recordset into it.



--
Message posted from http://www.ExcelForum.com