Hi,
I have an Access program that basically creates Excel-files based on a query on an Access table.
The program uses loops and does the following:
- it queries each loop another year
- it transfers each loop the results of that query in a temporary excelfile X (using DoCmd.TransferSpreadsheet acExport)
- it makes a copy of some columns from the source temporary excelfile X to another (definitive) excelfile Y.
- in Excelfile Y, it pastes the columns each loop after the columns of the previous year. So the excelfile Y gets bigger each loop.
What I notice is that each loop takes more and more time to complete.
At the first loop, it takes 28 seconds to complete, which is relatively fast.
At loop 10, it takes 49 seconds.
Loop 20, it needs 72 seconds.
At loop 30, we are talking about 100 seconds.
As you can see, the growth in duration is linear, certainly not exponential.
After putting some timers in my code, I found out which part of the code is causing this increasing processing time: it is the actual copying:
I’m not a VBA expert, so this is why I’m wondering if the method I used is the most efficient one.
Is it normal that in such situations - the excelfile gets bigger each loop - I have to accept this increasing processing time?
Or are there more effcient ways to do the copy between the 2 Excelfiles, reducing the processing time?
(ideally, each loop would take the same amount of processing time)
Full code in attachment (did some modifications for reasons of identification)
I have an Access program that basically creates Excel-files based on a query on an Access table.
The program uses loops and does the following:
- it queries each loop another year
- it transfers each loop the results of that query in a temporary excelfile X (using DoCmd.TransferSpreadsheet acExport)
- it makes a copy of some columns from the source temporary excelfile X to another (definitive) excelfile Y.
- in Excelfile Y, it pastes the columns each loop after the columns of the previous year. So the excelfile Y gets bigger each loop.
What I notice is that each loop takes more and more time to complete.
At the first loop, it takes 28 seconds to complete, which is relatively fast.
At loop 10, it takes 49 seconds.
Loop 20, it needs 72 seconds.
At loop 30, we are talking about 100 seconds.
As you can see, the growth in duration is linear, certainly not exponential.
After putting some timers in my code, I found out which part of the code is causing this increasing processing time: it is the actual copying:
Code:
For year = a To b
'preprocessing code
'copying:
x.Worksheets(1).Columns(9).Copy Destination:=y.Worksheets(sheetnaam).Columns(start_kolom + 0)
x.Worksheets(1).Columns(10).Copy Destination:=y.Worksheets(sheetnaam).Columns(start_kolom + 1)
x.Worksheets(1).Columns(11).Copy Destination:=y.Worksheets(sheetnaam).Columns(start_kolom + 2)
x.Worksheets(1).Columns(12).Copy Destination:=y.Worksheets(sheetnaam).Columns(start_kolom + 3)
x.Worksheets(1).Columns(16).Copy Destination:=y.Worksheets(sheetnaam).Columns(start_kolom + 4)
'postprocessing code
next
I’m not a VBA expert, so this is why I’m wondering if the method I used is the most efficient one.
Is it normal that in such situations - the excelfile gets bigger each loop - I have to accept this increasing processing time?
Or are there more effcient ways to do the copy between the 2 Excelfiles, reducing the processing time?
(ideally, each loop would take the same amount of processing time)
Full code in attachment (did some modifications for reasons of identification)