Looping very slow using Excel object

[LEXCERM]

Hi all,

Here is a snippet of a loop code I'm currently using in an Access module:-


For Each myCell In xlApp.Range("A2:A" & xlApp.Cells(xlApp.Rows.Count, "C").End(xlUp).Row)

For ChkDate = 5 To xlApp.Cells(myCell.Row, xlApp.Columns.Count).End(xlToLeft).Offset(0, -2).Column
AA = myCell.Value
BB = myCell.Offset(0, 1).Value
CC = myCell.Offset(0, 2).Value
DD = myCell.Offset(0, 3).Value

xlApp.Sheets("Result").Cells(xlApp.Rows.Count, "A").End(xlUp).Offset(1, 0) = AA
xlApp.Sheets("Result").Cells(xlApp.Rows.Count, "A").End(xlUp).Offset(0, 1) = BB
xlApp.Sheets("Result").Cells(xlApp.Rows.Count, "A").End(xlUp).Offset(0, 2) = CC
xlApp.Sheets("Result").Cells(xlApp.Rows.Count, "A").End(xlUp).Offset(0, 3) = DD

Next ChkDate
Next myCell


If I run this code in Excel, it takes approx 10 seconds to complete 20,000 records. However, running this in Access seems to take forever.

Are there any tips I could use? Am I over stating the xlApp references?

Kind regards.

 

[Joe4]

Why are you trying to run VBA code on Excel files from Access?
I prefer to avoid this whenever possible.
Since, as you pointed out, it runs much faster in Excel, why not just have an Excel macro, and call/run the Excel macro from Access (instead of trying to put the VBA code in Access)?

 

[LEXCERM]

Thanks for the reply Joe4. I did exactly that in the end. Kept the code in the Excel file and fired it through Access, then imported the data into Access. Cheers.