Macro, Efficiency and Memory

[Monsignor]

I'm experiencing this for the first time:
I'm using Excel to import about 20,000 web pages. I've noticed that Excel started off flying: 1 page every 3 seconds. Around 1000 it was noticeably slower. Around 2000 it was pulling 1 page every 30 seconds.

One thing I've been doing is stop the macro, cut the pages that are imported, paste them into another workbook, and then restart the macro.

Also, it seems to help a little to completely shut Excel down before re-starting the macro. And when I start, it's the same thing. Starts off great and then slows to a crawl.

What's happening inside Excel? Inside the computer? Am I really doing any good to shut Excel down occasionally? Is there a cache somewhere that needs to be emptied?

 

[Leith Ross]

Hello Monsignor,

It is difficult to provide a specific answer when the macro code is absent. Regardless of the method you are using to import the data, that is a lot of web sites. Not every web site is going to respond in the same amount time and most importing is done synchronously. The macro will wait for a response or until the site times out before moving to the next site.

 

[JLGWhiz]

It probably would not hurt to have a workbook.Save built into an IF,,,ElseIf...Then, or a Select Case or some other statemet after every two or three hundred pages are imported. That would free up some memory and might prevent the big slow down.

 

[wigi]

Hello

Have a look here:

http://www.excelhero.com/blog/2010/05/multi-threaded-vba.html

A great method to speed up web scraping.