Memory problems with VBA

[JonasKl]

Hey everyone,

I have a macro that accesses a URL in column 1. Then it scans the resulting web page for other URLs and filters it. It then lists the remaining URLs in column B of my excel. Moreover, it accesses each of these links again and checks if the link is working. This is then documented in column C.

Now, I have a problem that I tried to solve by myself but I did not get there. After running the macro for like 500 links, it gives me an error that there is not enough memory. The computer on which it is running has sufficient RAM and I tried to delete the cache after every iteration with my code (Application.CutCopyMode = False). I also tried to save it after every iteration which makes the entire process slower but apparently does the trick of reducing memory storage sometimes. All these options did not working and I still cannot run the program for more than 400 or 500 links. I need to do it with 45000 though, so doing it all at once would be awesome.

Can someone identify the problem and maybe fix it? I am really stuck here!

I attached the current version of the excel with 500 links in it.

Thank you so much in advance!

Cheers,
Jonas

[Marc L]

Hi !

Create objects once before the loop instead of create same objects within loop !

[JonasKl]

How would it look in the code?

The thing is that the code always accesses a different link from a loop to the next one. Can you still only create one object at the beginning?

[Marc L]

For example, loop must be inside the With CreateObject block, the opposite of your actual code !

As you can see within so many samples within this forum like this thread …

[JonasKl]

Thanks, I'm really a beginner with all this VBA stuff. The code has been written by someone else from this forum. Would you be able to change the code in the excel file I uploaded in the way necessary to make it work?

That would be awesome!

[6StringJazzer]

Closed at request of JonasKl so it can be posted in Commercial Services. You may review the question and "lock" it there if you have Guru status or above.