(Solved) – Web scrapping data using Excel VBA

  • by

I want to have a record of parcels having tracking numbers in excel column A and details of the available field on other columns so that whenever I press the button to run module it updates me about the parcel details fetching from the website. The website I am targeting is “http://trackandtrace.courierpost.co.nz/Search/“. I have made the code to embed tracking number after that link and to fetch other fields but the code is not fetching current status description & I want a status to be colored with same color as in the website
Here is my code:

Sub update_from_web()
Application.ScreenUpdating = False

Set sh = ActiveSheet

lRow = sh.Range("A" & Rows.Count).End(xlUp).Row

loadUnit = frm_loading.lbl_load.Width / (lRow - 1)
frm_loading.lbl_load.Width = 0

sh.Range("B2:C" & lRow).ClearContents

For i = 2 To ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

    GetUrl = "http://trackandtrace.courierpost.co.nz/Search/" & Replace(sh.Range("A" & i), "'", "")

    Dim oHtml As HTMLDocument
    Dim oElement As Object

    Set oHtml = New HTMLDocument

    With CreateObject("WINHTTP.WinHTTPRequest.5.1")
        .Open "GET", GetUrl, False
        oHtml.body.innerHTML = .responseText
    End With

    Set statusElem = oHtml.getElementsByClassName("status")(1).getElementsByTagName("span")
    statusVal = statusElem(0).innerText
    statusDate = statusElem(1).innerText

    sh.Range("B" & i) = statusVal
    sh.Range("C" & i) = statusDate

    Set statusElem = oHtml.getElementsByClassName("current-status")(1).getElementsByTagName("div")
    statusVal = statusElem(0).innerText
    sh.Range("D" & i) = statusVal

    frm_loading.lbl_load.Width = frm_loading.lbl_load.Width   loadUnit

Next i


Application.ScreenUpdating = True
End Sub

Here are sample details which I am trying to fetch
enter image description here

It gives error on this line

Set statusElem =

Leave a Reply

Your email address will not be published. Required fields are marked *