+ Reply to Thread
Results 1 to 27 of 27

Checking for broken hyperlinks

  1. #1
    Forum Contributor
    Join Date
    04-27-2006
    Location
    Cayman Islands
    Posts
    375

    Checking for broken hyperlinks

    Good morning all,

    I am putting together a document where often the numbers have to be justified by some form of supporting document, often in .pdf format. To be as user friendly as possible I hyperlink those numbers to said document. Unfortunately that means there are a lot of hyperlinks and mistakes get made!

    So my question is, is there a way to automatically check the entire document to ensure none of the links are broken, and if there are broken links, which ones they are? Thanks in advance!

    Chris
    Last edited by ChrisMattock; 01-20-2011 at 08:17 AM.

  2. #2
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Checking for broken hyperlinks

    Assuming the hyperlinks refer to documents on your network...
    Try this

    • Select any sheet in the workbook you want to test
    • ALT+F11....to open the VBA Editor
    • Insert.Module
    • Copy the below code and paste it into that module
    Please Login or Register  to view this content.


    • Select a sheet that contains hyperlinks you want tested.
    • Select the range of cells that contains the hyperlinks....eg A1:Z100
    • ALT+F8...to open the list of availlable macros
    • Select: TestHLinkValidity
    • Click: Run

    Any hyperlinks with invalid targets will be colored yellow.

    Is that something you can work with?
    Last edited by Ron Coderre; 01-19-2011 at 04:53 PM.
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Contributor
    Join Date
    04-27-2006
    Location
    Cayman Islands
    Posts
    375

    Re: Checking for broken hyperlinks

    Thanks for the help Ron, that works brilliantly!

  4. #4
    Registered User
    Join Date
    09-09-2011
    Location
    Houston, TX
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Checking for broken hyperlinks

    This worked great for finding existing, unbroken hyperlinks. Now I want to find cells in the range that don't have hyperlinks and highlight those. Any suggestions?

  5. #5
    Registered User
    Join Date
    08-28-2012
    Location
    Pittsburgh
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Checking for broken hyperlinks

    Tried it. All my links were colored yellow. Half of the hyperlinks actually work and the other half don't but the macro says they all are invalid.

  6. #6
    Registered User
    Join Date
    10-30-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Checking for broken hyperlinks

    Having the same problem.

    Quote Originally Posted by bcolson View Post
    Tried it. All my links were colored yellow. Half of the hyperlinks actually work and the other half don't but the macro says they all are invalid.

  7. #7
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,417

    Re: Checking for broken hyperlinks

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

    Also, for confirmation, I believe Ron's code is for documents on a network. If you are at URLs then there is other code for this. It was not clear from your post which you were trying to check.

    PM me if you have any problems.

    abousetta
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

  8. #8
    Registered User
    Join Date
    09-11-2012
    Location
    Liverpool, England
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Checking for broken hyperlinks

    Thanks this macro worked great for me!

  9. #9
    Registered User
    Join Date
    01-24-2013
    Location
    St. Louis, Missouri
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Checking for broken hyperlinks

    Hi, Ron.

    This worked marvelously for me but I have one question.

    Is there a way to run this on hyperlinks that are attached to images instead of hyperlinks that are attached to cells?

    This MACRO is skipping the cells with said image hyperlinks (I have intentionally made some of the links incorrect to verify this).

    Each cell in the column has a little document thumbnail image which is what the hyperlink is actually linked to and I'm looking to verify that they will, in fact, open.

    Thank you so much!

  10. #10
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Checking for broken hyperlinks

    ryan_linz,

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  11. #11
    Registered User
    Join Date
    03-04-2013
    Location
    earth
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Checking for broken hyperlinks

    This cleaner version will fix it. Ron just forgot to use the entire path. Looks like caused by copying only portions of another code (hence the superfluous variables). Good algorithm though.

    Please Login or Register  to view this content.
    Last edited by arlu1201; 03-05-2013 at 01:11 AM.

  12. #12
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Checking for broken hyperlinks

    Solved it,

    Welcome to the forum and thank you for the solution.

    I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature below this post.

  13. #13
    Registered User
    Join Date
    03-14-2013
    Location
    hefei
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Checking for broken hyperlinks

    hi there I have checked all the codes you mentioned above for broken web hyperlinks. But still There are some links in my file which are not broken but code showing them broken.

  14. #14
    Registered User
    Join Date
    07-09-2013
    Location
    Stoke
    MS-Off Ver
    Excel 2010
    Posts
    0

    Re: Checking for broken hyperlinks

    Didn't work for me, all the links are still broken...

  15. #15
    Registered User
    Join Date
    08-03-2013
    Location
    london
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Checking for broken hyperlinks

    Try this

    Please Login or Register  to view this content.
    Last edited by arlu1201; 08-04-2013 at 10:42 AM.

  16. #16
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Checking for broken hyperlinks

    Danialadam,

    Welcome to the forum.

    I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.

  17. #17
    Registered User
    Join Date
    01-04-2012
    Location
    Ontario
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Checking for broken hyperlinks

    Quote Originally Posted by Ron Coderre View Post
    Assuming the hyperlinks refer to documents on your network...
    Try this

    Select any sheet in the workbook you want to test
    ALT+F11....to open the VBA Editor
    Insert.Module
    Copy the below code and paste it into that module
    Please Login or Register  to view this content.


    Select a sheet that contains hyperlinks you want tested.
    Select the range of cells that contains the hyperlinks....eg A1:Z100
    ALT+F8...to open the list of availlable macros
    Select: TestHLinkValidity
    Click: Run

    Any hyperlinks with invalid targets will be colored yellow.

    Is that something you can work with?
    After I press ALT + F11 where do I type Insert.Module and the code?

  18. #18
    Registered User
    Join Date
    01-04-2012
    Location
    Ontario
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Checking for broken hyperlinks

    As per Ron's instructions, after I press ALT+F11 to open the VBA Editor where would I type in Insert.Module? I just get a blank screen. And where would I paste the code into the module?

    Any help would be appreciated.

  19. #19
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Checking for broken hyperlinks

    After you press Atl+F11, on the left hand side you will Microsoft Excel Objects. Right click on it and select Insert - > Module. On the right hand side you will see a blank code window. Copy paste your code over there.

  20. #20
    Registered User
    Join Date
    12-10-2014
    Location
    Brisbane
    MS-Off Ver
    2013
    Posts
    1

    Re: Checking for broken hyperlinks

    Thank you Ron, Yours worked a treat for me... with one exception lol
    It will tell me of broken links into a specific file, but some of my links are into folders containing a list of files and are highlighted as broken when in fact they work as intended.
    If you or anyone else could help with this i'd be very appreciative.

    Sorry "Solved it" and "Danialadam" i tried yours, they both just highlight a hyperlink if its working or not.

  21. #21
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,837

    Re: Checking for broken hyperlinks

    Corey123,
    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Ben Van Johnson

  22. #22
    Registered User
    Join Date
    05-14-2015
    Location
    Washington DC
    MS-Off Ver
    2010
    Posts
    1

    Re: Checking for broken hyperlinks

    I keep getting this error message:

    "Run-time error'429':
    ActiveX component can't create object"

  23. #23
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,837

    Re: Checking for broken hyperlinks

    chrisfreire,
    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

  24. #24
    Registered User
    Join Date
    08-06-2015
    Location
    Egypt
    MS-Off Ver
    2013
    Posts
    1

    Re: Checking for broken hyperlinks

    will try it as I really need something like this

  25. #25
    Registered User
    Join Date
    11-24-2016
    Location
    Bangalore
    MS-Off Ver
    2010
    Posts
    1

    Re: Checking for broken hyperlinks

    It's Really Superb man.. Thankz A lot

  26. #26
    Registered User
    Join Date
    06-22-2017
    Location
    USA
    MS-Off Ver
    2013
    Posts
    1

    Re: Checking for broken hyperlinks

    Thank you. It worked great.

  27. #27
    Registered User
    Join Date
    06-22-2018
    Location
    Carbondale, IL
    MS-Off Ver
    2010
    Posts
    1

    Re: Checking for broken hyperlinks

    For me the previous examples continue to highlight all of the hyperlinks, not just the broken ones.
    This is what worked for me after adding a reference to Microsoft WinHTTP Services under Tools > References:
    Please Login or Register  to view this content.
    Last edited by Nylvada; 06-22-2018 at 02:51 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1