+ Reply to Thread
Results 1 to 16 of 16

Automatically Test Hyperlinks in Excel 2007

Hybrid View

friedboudinball Automatically Test Hyperlinks... 11-10-2011, 04:08 PM
friedboudinball Re: Automatically Test... 11-11-2011, 09:38 AM
snb Re: Automatically Test... 11-11-2011, 09:47 AM
friedboudinball Re: Automatically Test... 11-11-2011, 10:26 AM
snb Re: Automatically Test... 11-11-2011, 10:53 AM
friedboudinball Re: Automatically Test... 11-14-2011, 11:08 AM
snb Re: Automatically Test... 11-14-2011, 11:31 AM
Learner22 Re: Automatically Test... 07-31-2012, 09:43 AM
arlu1201 Re: Automatically Test... 07-31-2012, 10:13 AM
Learner22 Re: Automatically Test... 08-01-2012, 05:02 AM
  1. #1
    Registered User
    Join Date
    11-10-2011
    Location
    Paris, France
    MS-Off Ver
    Excel 2007
    Posts
    13

    Automatically Test Hyperlinks in Excel 2007

    I have seen numerous posts on how to get Excel to automatically test hundreds of links in a worksheet. however, i have never been able to get it to work for my case.

    I have a worksheet and would like to test each of the links automatically. If they are valid, do nothing, if they are invalid either color them yellow or put a comment in Column Z that says Invalid, or similar.

    I am using Excel 2007. Thank you, in advance, for your help.
    Last edited by friedboudinball; 11-11-2011 at 09:37 AM.

  2. #2
    Registered User
    Join Date
    11-10-2011
    Location
    Paris, France
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Automatically Test Hyperlinks in Excel 2007

    any thoughts?

  3. #3
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Automatically Test Hyperlinks in Excel 2007

    Yes, many based on your sample workbook.

    Basically:

    sub snb()
      on error resume next
      for each hp in sheets(1).hyperlinks
        thisworkbook.followhyperlink hp.address
        if err.number<>0 then hp.Range.Interior.ColorIndex = 3
        err.clear
      next
    end sub
    Last edited by snb; 11-11-2011 at 09:58 AM.



  4. #4
    Registered User
    Join Date
    11-10-2011
    Location
    Paris, France
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Automatically Test Hyperlinks in Excel 2007

    that works well ... thanks ...

    is there a way it can test but not open the windows? If i have thousands to test, i'm afraid that would overload my PC.

    if that is not an option, can I test a range?

  5. #5
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Automatically Test Hyperlinks in Excel 2007

    after loading a reference to microsoft XML, version 2.0 (or another that contains XMLHTTPRequest)

    Sub snb()
     On Error Resume Next
    
     With New XMLHTTPRequest
      For Each hp In Sheets(1).Hyperlinks
       .Open "get", hp.Address
       .send
       If Err.Number <> 0 Then hp.Range.Interior.ColorIndex = 3
       Err.Clear
      Next
     End With
    End Sub

  6. #6
    Registered User
    Join Date
    11-10-2011
    Location
    Paris, France
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Automatically Test Hyperlinks in Excel 2007

    anyway to limit it to a certain range of cells?

  7. #7
    Registered User
    Join Date
    11-10-2011
    Location
    Paris, France
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Automatically Test Hyperlinks in Excel 2007

    So how do i check? I have added the XML add-in. Is there somewhere else i should be looking?

  8. #8
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Automatically Test Hyperlinks in Excel 2007

    Load the Microsoft XML 2.0 version.

  9. #9
    Registered User
    Join Date
    07-31-2012
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Automatically Test Hyperlinks in Excel 2007

    Nice simple programs, thanks.
    The first one reports an error value of '0', so it works great - except for leaving all the files open.
    The second one, requiring XML, is returning an error code value of '-2147467259' for the same files!?
    I have tried adding a 10 second delay after the '.send' line in case the error is caused by a process timeout, as the files on on our network. The delay works but the error still exists. The 'Err.Clear' is changing the value to '0' as expected, so the error must be generated by the code.
    Does anyone know where I can find out what the error value relates to and better still, how to eradicate it.

  10. #10
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,167

    Re: Automatically Test Hyperlinks in Excel 2007

    Learner22,

    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
    07-31-2012
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Automatically Test Hyperlinks in Excel 2007

    Please refer to new thread with the same title, thanks.

+ 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