Detect Broken Hyperlinks when Hyperlink formula is used

I have column of hyperlinks that point to files on my network, the hyperlinks are made with the hyperlink formula instead of the usual way of inserting hyperlinks, the reason for this is I have concatenated parts from the next two columns to make the file path, i.e. the folder and file name.

I have made thousands of them, but some of the files are missing from the folders, is there a way to automatically test the hyperlinks to see if they link anywhere?

I know this topic has been covered many times but as far as I can tell they are for hyperlinks which are made by inserting them and not the hyperlink formula.

Any help would be greatly appreciated

I'm using excel 2013 btw

Hi rossboland1,

Are you using the 'Friendly Name' option in the Hyperlink() function? If not, and assuming you are explicitly showing the whole filepath, then the following sub should work (assuming hyperlinks in range A1:A10 and column D is empty and can receive the test results)

Please Login or Register  to view this content.

If you are using the 'Friendly Name' (which will mask the hyperlink string from the cell's value), as your formula seems to concatenate the next two columns, you could recreate this concatenation when setting strTest instead, eg.

Please Login or Register  to view this content.

If you need more help, please do post an example, as this suggestion is based on a few assumptions about how you have set up your data - although I do hope the method makes sense.

Hope this helps.

Hi underscoreBadger,
Thanks for your reply, yes I am using a friendly name.
I was able to get your example to work when I removed the friendly name but I was unclear about what to do for using a friendly name, where do I put that line in code?

Here is an example of what I'm trying to do
https://drive.google.com/file/d/0B_v...ew?usp=sharing

Sorry I just realized that was a bad example
This one is better

https://drive.google.com/file/d/0B_v...ew?usp=sharing

Hi rossboland1,

Thank you for the link. In this case, I would suggest two solutions: one would be to add a column to your spreadsheet that simply lists the unmasked hyperlink, and then test that using the above routine. This extra column could then be hidden if you don't want it displayed.

Alternatively, you could rewrite the line

Please Login or Register  to view this content.

as

Please Login or Register  to view this content.

This in effect re-creates your existing logic behind building the link, this time within the VBA.

There are probably pros and cons to both (and someone may suggest a different solution). Please do let me know if you get this working okay for your data.

Yes that worked perfectly
Thank you, you have saved me so much time