Taking lot of time in excel report generation using VB

Question

Hi,

I am generating report in excel sheet(which is coming from Microsoft Access).
For 100 records, it can generate the excel sheet in 2 to 3 seconds. But, it takes 10 minutes for more than 2000 records.

Is there a solution or is there any other way to generate report in excel sheet?

Answer 1

Use Excel's CopyFromRecordset. Much faster than cell by cell. Modules: Transferring Records to Excel with Automation

Also see Excel automation formatting examples for some additional tips in working with Excel.

Answer 2

If Tony's answer won't work for you ... you might have to "profile" your application. I do not know of any tools that will do it for you (such as you have in .net) but you can always sprinkle your output code with "debug.print now() & " to see where your bottleneck is.

Seth

Answer 3

SpreadsheetGear for .NET is much faster than using Excel from VB.NET as demonstrated by customer comments on this page.

You can see live samples here and download a free trial here.

Disclaimer: I own SpreadsheetGear LLC

Answer 4

If I'm understanding your problem, this is a very easy fix that doesn't require coding.

1. Open up Excel
2. Go to Data->"Import External Data"->"Import Data"
3. Choose your Access file and table/query of choice.
4. Whenever you want to refresh the data, just right click on the import area and click refresh.

Later, you can code up some VBA to auto-refresh and save the XL for you.