Memory problem/slow macro

[Cicada]

Hello all. The following code performs the desired task though not until completion. There are roughly 200 hyperlinks with 6 elements taken from each link. Is there any way that I can speed this up/free up memory? It is incredibly slow and then blocks at 200 odd elements. Any help greatly appreciated.

Cheers
Dan

Please Login or Register  to view this content.

[Keebellah]

Hi, my first question.
Does it really stop or does it just say Excel is not responding (in French if your interface is French)
You must know, and I fell for it too, that Excel keeps working in the background. This started with Excel 2007 because of the way it is written.
The entire concept of how it works has changed.
The UI (User Interface) stops working but in the background the code is still busy even though you cannot press Ctrl + Break or interrupt.
Patience is the only option.
You can add a status bar update to see but this also stops.

[Keebellah]

I include this code before and after the main macros

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[AB33]

Dan,
You are using not the most efficient scrapper. I do not know if you know HTTP object. It is unobtrusive and much robust than IE.

I can't say more unitl I see and test the URLs.

[Cicada]

Thank you for the replies.

Keebelah they do say patience is a virtue though I do not have it in droves. I will have a look at what you suggest and see if it improves things. Though it does seem incredibly slow for what really is a simple scrape. Hello again AB here is an example link

I am scraping the names and populating sheet 2. No I am not familiar with http object. Will check it out. Thanks again to both of you.

Cheers
Dan

[AB33]

Here we go!

I have tested it using the URL's you just posted. You need to make some changes for the code to work on your real data:

searchUrl should be replaced by cel– the values in each row in sheet1
You also need to tick HTML librarry in Tools- References- MSHTML OBJECT LIBRARY

Please Login or Register  to view this content.

[WideBoyDixon]

Hi. You *can* avoid using IE but you may need to use early-binding. In VBA, go to Tools/References and check the boxes next to "Microsoft HTML Object Library" and "Microsoft XML, v6.0". Then you could try the following:

Please Login or Register  to view this content.

The DoEvents line hopefully keeps Excel from going to "Not Responding" and would allow you to break into your code.

WBD

[Cicada]

Hello, Both codes worked very well. Need to learn about this. So fast. Thanks a heap AB and Wideboy. Much appreciated.

Cheers
Dan

[Cicada]

Hello would like to pick you brains again. The names come out in a continuos list which is ok but not ideal. I would like to keep each link grouped ie keep each links elements separated by an empty row. How would one go about doing this?

Cheers
Dan

[AB33]

Do you mean you want to insert an empty row for each URL?

Please Login or Register  to view this content.

[Cicada]

Hello AB,

Cheers mate. Exactly what I was after. I had entered the wrong variables and was getting an error. Stupid. Thanks for all your help.

Dan

[WideBoyDixon]

Hello would like to pick you brains again. The names come out in a continuos list which is ok but not ideal. I would like to keep each link grouped ie keep each links elements separated by an empty row. How would one go about doing this?

Cheers
Dan

You would add an extra line here:

Please Login or Register  to view this content.

WBD