Pages

Saturday, October 8, 2011

Copy Hyperlinks and Text from a webpage to Spreadsheet

If you want to copy the text and hyperlinks behind that text from webpage to excel. Snapshot below-


Sub extract_hyperlink()

    Dim ie As Object
    Dim i As Long
    Set ie = CreateObject("InternetExplorer.application")
    
    ie.Visible = True
    ie.navigate "http://www.excelvbamacros.com/p/index_1.html" ' change url
    
    Do While ie.Busy ' wait till page is loaded fully
    Loop
    
    i = 2
    For Each Link In ie.Document.Links ' loop through all inks
        Sheets(1).Range("a" & i).Value = Link.innertext
        Sheets(1).Range("b" & i).Value = Link
        i = i + 1
    Next
    ie.Quit
    Set ie = Nothing


End Sub


Download Working File

No comments:

Post a Comment