VBA Check if hyperlink is valid, if not replace cell with warning

[BlakeSkate]

Here is an example of what i need:

a working hyperlink within range BC:BF
https://s3.amazonaws.com/images.free...1083-BGU-4.jpg


a problematic hyperlink within range BC:BF
https://s3.amazonaws.com/images.free...hoes/UPC-4.jpg

if the problematic link is invalid or pulls up the "This XML file does not appear to have any style information associated with it. The document tree is shown below." page i would like to replace it with blank cells. is this doable?
Thanks for any and all help!

[LordLoki]

do you want that to happen when someone clicks the link or do you want a routine that checks all Links?

Greets
Loki

[BlakeSkate]

do you want that to happen when someone clicks the link or do you want a routine that checks all Links?
i

LordLoki (awesome name btw)
i want a macro that checks range BC:BF whenever the macro is run

[LordLoki]

Hi Blake,

Thanks, i am a big Fan of Nordic Mythology and my favorite is Loki so thats where the name comes from :D

Now lets look into your Problem.
I wrote a code that should bring you to the goal that you want as long as we are talking about web links only.
Tested it with your test links and it worked. you can go more into detail than i did (search the whole body of the website for specific text") but i just focused on the Title cause its nearly midnight over here :D

I put some comments in and hope you get along if you need someting more just let me know. Will look into it tomorrow then.

EDIT: BTW you need to set the Reference for "Microsoft Internet Controls"

Greets
Loki

Please Login or Register  to view this content.

[BlakeSkate]

LordLoki,
I'm 100% certain i have no idea how to set the reference for the application for IE?
i think thats the only reason it's not working on my end as i've changed all "Sheet 1"'s to my sheet name which is "Master Supra"

other than that i'm very excited for this code and am grateful that you're writing it.

[xladept]

VBE - Tools - References - Click on "Microsoft Internet Controls"

[BlakeSkate]

xladept,
i clicked the box, am i supposed to specify the destination folder for CreateObject?

[LordLoki]

No should just run without any promt. Maybe attach your file then we can see whats wrong.

EDIT: I attached a working file where i changed the name of the Sheet to "MasterSupra" try it and see if you get the Same Error

[BlakeSkate]

LordLoki,

My workbook is too large to upload on this website as its 3MB without the VBA code that i have on it already :X
here is a screenshot though hahaha
"Master Supra" is the 8th worksheet (not sure if thats relevant?)

w8XyTdY.jpg
UeDAFgU.jpg
jnhtN8g.jpg
kGrWWN9.jpg

[BlakeSkate]

Also, your file works perfectly, but when i copy/paste the VBA into my workbook it doesn't do anything it just displays the message box that the action was completed

[LordLoki]

The Hyperlinks in your workbook are not actual Hyperlinks they are just text as i see.

If that is correct the code needs to be changed a bit cause now he is just looping through all Hyperlinks on a sheet. I am in the train to work at the moment will give you an update later

[LordLoki]

Hi Buddy,

Modified the Code so that it works with Text Only
just change columnletter = "A" to the Column where your Hyperlinks are stored
and to answer your question the Position of your Sheet is not relevant cause we use the Sheet Name to adress it.
The Position is only relevant when you adress it like that ws.sheets(1)

Please Login or Register  to view this content.

Greets
Loki

[BlakeSkate]

LordLoki,


EDIT: Can you have other VBA in the same module? i added a sub below your yours in the workbook you sent and it gave me the follow errors:
2ND EDIT: upon further inspection it looks like this may not be able to handle a certain amount of hyperlinks? removing duplicates (reducing 2000+ to 180) made the process go by smoothly, but i can't reduce the number of hyperlinks it checks. When there are too many hyper links it spits out "Excel is waiting for another application to complete an OLE action." every 5 seconds and does not allow me to exit the macro without force shutting down excel.




Sorry for being an absolute pain
Could we use the original and just use

Please Login or Register  to view this content.

to make the text hyper links, because thats what i've been doing and it has a partial success rate.
Here is the workbook reduced so its not too big for the siteForLoki.xlsm

[BlakeSkate]

Essentially what i'm getting at is i have 8000+ hyperlinks between BC:BF that are stored as text.
The above VBA to convert the text into hyperlinks combined with the workbook supplied works ONLY if i do a very small amount at a time.
Perhaps you can have it go through like 500 at a time so it doesn't end up hurting itself?

the goal is to have everything automated so i don't have to move the data either so i supplied you with my workbook above.
I noticed it likes to bug out when you run it more than once, and when you have other vba in the same module.
Any thoughts on this?

Thanks again for helping me out thus far i am super grateful, and again: sorry for being a pain.

[LordLoki]

Sorry just checked my code and found my mistake.

Needs to be ie.NAVIGATE WS.Cells(i, Columnnumber).text not only ie.NAVIGATE WS.Cells(i, Columnnumber)
and the "Broken Link" setup was set to Worksheets(1) instead of "Master Supra"

Fixed it and tested with your worksheet.
to check all the links takes quiete some time so i added a little progress bar so that you know he is moving

Check the Attached Workbook to see if its working for you now.
Or post the code below in your workbook. (code below is without progress bar)
To make the Porgress bar in your original file work just export the userform and import in your file

Greets
Loki

Please Login or Register  to view this content.

[BlakeSkate]

Hey LordLoki,
I'm still getting the "OLE" error?
i'm running the code straight from your attached workbook. It ends up stopping the process (the progress bar won't go any further), and i can't exit the script using ESC.

also is there a way to specify the range as BC:BF instead of just one column?
Thanks!

[BlakeSkate]

Hey LordLoki,
I'm still getting the "OLE" error?

I actually found the issue i just needed to move "Microsoft Internet Control" above OLE in the priority list under "References" in the VBA sheet.
The progress bar ends up going all the way to the end but i think its stuck

EDIT: it worked! now is there a way to have it do a range instead of just one column?
2nd EDIT: i've been running it doing various things and there seems to be an inconsistency as sometimes i get the OLE error and sometimes i don't

[LordLoki]

Hi blake,

I can not reproduce the error with Ole. I changed the code to check a range and its running now. Checked for the ole error maybe it is caused by tge status bar. Will upload a new version later without status bar and multiple columns also activating ie so that it pops up. So that you have a visual indicator that tells you it is running.

[BlakeSkate]

Hi blake,

I can not reproduce the error with Ole. I changed the code to check a range and its running now. Checked for the ole error maybe it is caused by tge status bar. Will upload a new version later without status bar and multiple columns also activating ie so that it pops up. So that you have a visual indicator that tells you it is running.

Awesome thank you!
yeah i'm not entirely sure why its giving me that error, but it doesn't happen the first time its run, so it does the job i need it to.
but its definitely not the status bar (i would like to keep that) because i got it on the first couple of codes too.
can you send me a screenshot of all the references you have ticked?

[LordLoki]

Hi Blake,

I was playing around with the Sheet for a while and i also got the OLE Error on my other Computer.
Can not figure out why it does not happen on my Laptop but i know now that it is a problem with the Internet Explorer.
Seems like he freezes every now and then and that gets the VBA Code cause it waits for IE to get readystate.

I tried to work around that problem by putting the creeation of the IE in the loop and kill it after checking the Link.
So basicly it creates a new IE instance for every Link.
That works for the OLE problem as it seems but now he Throws an Automation Error.
Tried to ignore it through on error resume next but that did not work either.

will see if i can figure sth out that works without crashing
Come back to you after the Weekend

[BlakeSkate]

Come back to you after the Weekend

awesome thank you for working so hard on this. i got a temporary fix in the mean time so no rush. I just used your first code with the hyperlink adder + a couple other copy + Paste VBA codes, but i can't wait to get the one'n'done version.

[BlakeSkate]

Come back to you after the Weekend

are we still working on this Loki?

[LordLoki]

Yes its still on my todo just busy with a big work project

[BlakeSkate]

Yes its still on my todo just busy with a big work project

Awesome thank you for the update