Hi there
I am using Excel 2000.
I created a workbook with several macros which do the following:
From sheet 1, using a web query it extracts a table of data from an external website and puts it in a range of cells on sheet 1.
It then copies one by one 9 different cells from that range and pastes it into a different order on sheet 3.
Then using a list of keywords stored on sheet 2, it repeats the procedure but going to a different page on the website.
There are alltogether 26 cut or copy or paste actions involved in one run, which apparently slows everything down.
When I first started to run that thing, it extracted about 40 sets of data a minute. Then, after about 1000 runs, it slowed down to 1 set in 3 minutes!
I changed the code by adding a macro to cut the accumulated data on sheet 3 and pasted it into a second workbook. It helped a little, now I extract about 3 sets per minute. The speed is now constant. But not good enough. I have to extract 130000 sets of data, that would take me about a month of permanent running to extract them all!!!
Any idea how to speed that thing up?
If needed I would send you the complete code.
Hans
I am using Excel 2000.
I created a workbook with several macros which do the following:
From sheet 1, using a web query it extracts a table of data from an external website and puts it in a range of cells on sheet 1.
It then copies one by one 9 different cells from that range and pastes it into a different order on sheet 3.
Then using a list of keywords stored on sheet 2, it repeats the procedure but going to a different page on the website.
There are alltogether 26 cut or copy or paste actions involved in one run, which apparently slows everything down.
When I first started to run that thing, it extracted about 40 sets of data a minute. Then, after about 1000 runs, it slowed down to 1 set in 3 minutes!
I changed the code by adding a macro to cut the accumulated data on sheet 3 and pasted it into a second workbook. It helped a little, now I extract about 3 sets per minute. The speed is now constant. But not good enough. I have to extract 130000 sets of data, that would take me about a month of permanent running to extract them all!!!
Any idea how to speed that thing up?
If needed I would send you the complete code.
Hans