Excel from VB6. Fast Export Method.?

Question

Hi...All

I'm Creating an application that Exports data to Excel.

First it Selects data from Database and Performs some calculation & Changes on msFlexGrid and then writes to Excel file.

Except Writing to Excel Entire process is performed within 1-2 Seconds while datasize is still huge. but it takes 15-20 Seconds to Export the Data on Excel.

While same thing is done using msAccess it exports the data in fractions of Seconds.

What method does Access to write data on Excel i.e. so quick. and how can I use it in my Application.

Amit · Accepted Answer

Dear Ajax,

if data is directly exported to excel's each cell one by one through VB it takes too much time.
instead use a two dimentional array and write data over that Array.

then Write entire array on worksheet range

    MySheet.Range("$A$1:" & MySheet.Range("A1").Offset(ubound(MyAry, 1) + 1, ubound(MyAry, 2).Address).Value = MyAry

Info:
Formula:         ubound(MyAry, 1) 
and formula:    ubound(MyAry, 2) 

shall return the dimensions of your Array
and offset().address shall return the address the offset cell in respect to the current cell.

thisway you can select the required range and copy the value of your array on to it.

Hope this will help you.

? · Answer

You need to speak with a fertility specialist. Up to age 35, doctors will let you try for a year before they will suggest fertility treatment. After age 35, it is only 6 months. There are many treatments available, so you definately should talk to your doctor about it.

Excel from VB6. Fast Export Method.?

2 Answers