Macro to open excel hyperlinks

I would like to know if anyone can help me do a macro to open a list of hyper links.

I have a list of about 600 hyper links all in a column in separate rows, I have to open each one to see if the link works, yet it is boring and tedious as I have to wait sometimes for excel to catch up I would much rather leave it doing 100 at a time while I do other things

admintech

Posted 2009-10-22T15:11:54.673

Reputation: 6 970

For whatever solution you find, there's a surprisingly odd feature in Office you might need to take into account: it first uses an Internet Explorer component (but is not identifying itself as Internet Explorer) to see if the URL one clicks is valid. After that, it hands the resulting URL to the default browser (or not, if the web site for some reason blocks the User Agent "Microsoft Office Existence Discovery"). Details at http://superuser.com/questions/41935/clicking-hyperlinks-in-email-messages-becomes-painfully-slow/42237#42237

– Arjan – 2009-10-22T16:06:58.120

Answers

Sub FollowLinks()
    Dim c As Range

    For Each c In Range(Cells(1, 3), Cells(1, 3).End(xlDown))
        If c <> vbNullString Then
            ThisWorkbook.FollowHyperlink (c.Offset(, 1).Value)
        End If
    Next
End Sub

Source

joe

Posted 2009-10-22T15:11:54.673

Reputation: 11 615

If you automate Internet Explorer, you may be able to print the browser window from within Excel. You'll need to set a reference to Microsoft Internet Controls.
Here's an example:

Sub printweb()

    Dim ie As InternetExplorer

    Set ie = New InternetExplorer
    ie.Visible = True
    ie.Navigate Range("A1").Value 'A1 holds the URL

    Do
        DoEvents
    Loop Until ie.ReadyState = READYSTATE_COMPLETE

    'This prints it
    ie.ExecWB OLECMDID_PRINT, OLECMDEXECOPT_DONTPROMPTUSER

End Sub

Source

joe

Posted 2009-10-22T15:11:54.673

Reputation: 11 615

This doesn't work, the range will be D237 to D337 – admintech – 2009-10-22T15:21:25.217