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 "" ' change url
    Do While ie.Busy ' wait till page is loaded fully
    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
    Set ie = Nothing

End Sub

Download Working File

No comments:

Post a Comment

Import data from SQL

Macro to import data from SQL using ADO connection string: Sub Import_data_from_SQL() ' Tools -> References -> Microsoft Active...