html - Extract data from a website using VBA

Question

I am trying to extract the data from the webpage http://www.fdci.org/Member.aspx?mid=-1634884325&cat=1 and many others similar to this.

I need to get the Profile, Name, Address, Email, phone,fax, etc. from the webpage to different columns of an excel sheet. Would be great if you can share the VBA code for this or any help would be welcome.

PS: I am new to VBA Coding.

Answer 1

You can use MSXML2.XMLHTTP60 to get page, example for address.

' Add reference to MS XML, v6.0 and MS HTML Object Library

Public Sub test()

    Dim xmlObject As New MSXML2.XMLHTTP60
    Dim htmlDocumentObject As Object

    With xmlObject
        Call .Open("GET", "http://www.fdci.org/Member.aspx?mid=-1634884325&cat=1", False)
        Call .send

        If (.Status = 200) Then
            Set htmlDocumentObject = New HTMLDocument
            htmlDocumentObject.Open
            htmlDocumentObject.write .responseText
            htmlDocumentObject.Close

            Dim address As String
            address = htmlDocumentObject.getElementById("ctl00_ContentPlaceHolder1_lblAdd1").innerText

            [a1] = address
            ' and so on ...
        End If
    End With
End Sub