Check existense of URL's

[peterkerk]

I have a document with ~70,000 URL's in column A.
I want to check for each cell if the URL actually works (so the website can be opened and does not return an error).
If it does not work, I want cell B in the same row to be filled with a "*".

Is this possible and if so could someone provide me with a code sample?

[fredlo2010]

How do you know that the page does not exists anymore? What do you see in the browser?

Can you provide and example?

Thanks

[peterkerk]

So that would be something to be checked by programatically opening that URL. Normally I develop in .NET and I can open a httprequest and check the response. So for each of these URL's I would like to check what the response is. But I need it to be done in Excel. Does that clarify what I need?

[fredlo2010]

Hi,

I was looking for what was being check in the response. Yeah that's the way I would go. Here is some code to help you.

So if the keyword is in the response of if there is any other error cause by a HTTPrequest failure then the function will return a bad url.

Please Login or Register  to view this content.

Hope this helps

[peterkerk]

Thanks, I'm pretty new to all this and using Excel 2013. Where would I add this code and how would I run it? I added a new module to my VBA project but then have no idea what's next...

[peterkerk]

@fredlo2010: any (further) help you can provide me here? Thanks ahead!

[fredlo2010]

Hey Peter;

Give this page a read and you will be all set.

https://www.ablebits.com/office-addi...a-macro-excel/

Thanks

[peterkerk]

Hi Fred. Thanks!
I followed the steps on the link you provided.

I then ran the Marco 'MainRunner' on column L (not sure what macros "TurnExtrasOn" and "TurnExtrasOff" do)
I know that some links that are in column L definitely exist and some definitely don't.

What happens is that after completion of the macro a "*" is added in ALL rows in column B.

Also, I'm not sure if your macro requires the links to be checked to be true Excel hyperlinks (so blue underlined)

Am I doing something wrong?

[fredlo2010]

I am not sure,

Can you post a sample of your workbook ?

The links have to be just links like the address you would type in the browser.

Thanks

[peterkerk]

Sure, thanks!
www.lalisto.nl/files/sample.xlsm

[fredlo2010]

Hi,

The code just ran fine for me.


just change this section because your links are in column "K"

Please Login or Register  to view this content.

and the only invalid website was

"http://www.denisanddennis.com"


Thanks

[peterkerk]

Ah, it works indeed! However, my original files has way more links and performance becomes an issue I now notice. Is there a way to run the macro only on cells I manually select?

[fredlo2010]

Try this

Please Login or Register  to view this content.

Thanks

[peterkerk]

Thanks! I updated the sheet on www.lalisto.nl/files/sample.xlsm with your code. I get the dialog asking me if I selected individual ranges.
However, it again adds a * for all sites again in column L.
What am I doing wrong this time?

[fredlo2010]

You need to modify this line

Please Login or Register  to view this content.

thanks

[peterkerk]

This looks as if i need to know what value will be returned by the request...I was more hoping the code would pick up on the httpresponse code, e.g. only 200 is considered to be a correct url.
I'm not sure what this constant value is exactly doing?

[Kyle123]

So why don't you just check the response code?

[peterkerk]

Yeah, I tried that by doing this:

Please Login or Register  to view this content.

But that also just generates a "*" in each row....even though I know some urls exist.

[Kyle123]

Have you got an example of one which you think is valid, but doesn't return a 200?

[fredlo2010]

Peter please use code tags in your post. Thanks

And yeah we will need and example.

[peterkerk]

@Fred: Thanks, I updated that post.
Also updated the sample.xlsm:
All sites should return a 200 except www.denisanddennis.com
Also see in the sample that for all URL's the column next to it is marked with a "*"

[fredlo2010]

Hello,

It was my bad. I had a bad code in the IsGoodURL function.

The line bellow is invalid

Please Login or Register  to view this content.

This is the new fully tested function. I have also changed the code to check status.

Please Login or Register  to view this content.

This is a sample of the working file
sample.xlsm

Thanks

[fredlo2010]

Please look at my previous post

[peterkerk]

That works! Thanks!
One final question: I'm selecting and checking around 500 URL's per batch, but then I get "Excel is not responding"...Now I also already got this when checking just 5 URLs, but apparently it still is working correctly. Is there some way to show if this macro is really doing something (maybe a progress bar or something) or that Excel is actually stuck and not doing anything?

[fredlo2010]

You can do that with for example the code bellow will write to the Statusbar the word "Number"

Please Login or Register  to view this content.

As per excel been unresponsive that's how it is maybe if you use oHTTP.send asynchronously? then you will have to check if it finished or something with a loop.

If this is a one time operation why don't you just start it and go to lunch and when you come back the code should have finished No batch the whole thing.

Thanks

[peterkerk]

Ok, thanks for all this!

[fredlo2010]

I am glad I was able to help.

[peterkerk]

@Fred: Sorry, I'm running into one more thing!

I found that this URL
http://gatheringsde.com
redirects to
http://gatheringsdelaware.com/

Both the check for responsecode 200:

Please Login or Register  to view this content.

as well as this check:

Please Login or Register  to view this content.

Fail to recognize that link http://gatheringsde.com actually points to a correct URL.

I also setup a test with an actual 301 redirect www.trouwlocaties.com/test.php, but my code fails to recognize that.

How can I check for 301/302 responses?