Breaking News: Grepper is joining You.com. Read the official announcement!

extract info from a website into XLS using VBA

ShowGo Shane answered on May 9, 2023 Popularity 1/10 Helpfulness 1/10

answer extract info from a website into XLS using VBA

extract info from a website into XLS using VBA

Comment

Public Sub ExtractNameFromWeb2()

    Dim htmlDoc As New HTMLDocument
    Dim htmlElements As IHTMLElementCollection
    Dim url As String
    Dim i As Long, n As Long
    Dim parts As Variant, city As String, p As Long

    Dim xmlhttp As Object
    Set xmlhttp = CreateObject("MSXML2.serverXMLHTTP")

    'Loop through all rows in column F and extract the parcel number from the URL
    For i = 2 To Cells(Rows.Count, 7).End(xlUp).Row
        url = "https://tax.norrycopa.net/taxclaim/trirsp2pp.asp?parcel=" & Cells(i, 7).Value
        
        'Send a request to the web server and receive the HTML response
        Debug.Print "Request URL: " & url
        xmlhttp.Open "GET", url, False
        xmlhttp.send
        htmlDoc.body.innerHTML = xmlhttp.responseText
        'Debug.Print "HTML Response:"
        'Debug.Print htmlDoc.body.innerHTML
        
        Set htmlElements = htmlDoc.getElementsByTagName("td")
        
        For n = 0 To htmlElements.Length - 1
            If htmlElements(n).innerText = "NAME: " Then
                Range("B" & i).Value = htmlElements(n + 1).innerText
                Debug.Print "Name: " & Range("B" & i).Value
            ElseIf htmlElements(n).innerText = "ADDRESS: " Then
                Range("C" & i).Value = htmlElements(n + 1).innerText
                Debug.Print "Address: " & Range("C" & i).Value
                If htmlElements(n + 2).innerText = "" Then
                    Debug.Print htmlElements(n + 3).innerText
                    parts = Split(Application.Trim(htmlElements(n + 3).innerText), " ")
                    city = ""
                    For p = 0 To UBound(parts) - 2
                        city = city & parts(p) & " "
                    Next
                    Range("D" & i).Value = Left(city, Len(city) - 1)
                    Range("E" & i).Value = parts(UBound(parts) - 1)
                    Range("F" & i).Value = parts(UBound(parts))
                End If
            End If
        Next
        
    Next
    
End Sub

Popularity 1/10 Helpfulness 1/10 Language vb

Source: www.mrexcel.com

Tags: extract info using vba xls

Link to this answer
Share Copy Link

Contributed on May 09 2023

ShowGo Shane

0 Answers Avg Quality 2/10

extract info from a website into XLS using VBA

Contents

More Related Answers

extract info from a website into XLS using VBA

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.