Transferring data from Access to Excel in VBA very slow

[Michel S.]

Hi !

I'm working with Office XP, SP3.

I have a simple Access VBA module which creates an Excel Workbook, and
fills the first Worksheet with data coming from a query.

In the main loop, I only perform the following :

objExcel.ScreenUpdating = False
With xlsSheet.Cells(intRow, strColumn)
.Value = (recordset coresponding field value)
.HorizontalAlignment = (preset alignment)
If strNumberFormat <> vbNullString Then
.NumberFormat = strNumberFormat
End If
.Interior.Color = (Color based on cell value)
.Font.Color = (Color based on cell value)
End With
objExcel.ScreenUpdating = True

The right side of the assignment values (recordset field name,
alignment, number format, etc.. ) are stored in an Array of User
Defined Type (one line per field) initialized once at the beginning of
the function.

There is a total of 29 fields only (columns A to AC), and for each
cell, only these 5 properties are affected.

Despite this, it takes a little more than 1 second to fill each row,
which appears very slow to me.

Since there are more than 1200 rows, it nearly takes 20 minutes to
perform the transfer.

I have cheked the functions returning the FG/BG colors based on the
field value and they take less than a second to execute in a 10000
iterations loop. I do not suspect they are related to the slow
performance.

What are other's experiences with the performance of this kind of data
transfer ?

Any suggestions to "optimize"/make it faster ?

BTW, because I have to format some columns and also colour many cells
based on their value (10 possible values), I'm afraid I can't use the
"global transfer" options availiable in Access.. Unless somebody has
an option unknown to me.


Thanks in advance !

FU2: microsoft.public.access

[mrice]

Have you got other cells which are being calculated from the ones that you are changing or a worksheet_change user defined function? These could slow the code down.

It would be worth running the code line by line (F8) to see which step is causing the problem.