Copy Text from a webpage to Excel sheet using VBA, error "Object Required"-CodePudding

I want the delivery status of a shipment from a website.

For this I need to copy a text from a particular url as below -

From the website url - "https://www.aftership.com/track/safexpress/105431095", I need output to excel cell as "04/06/2022 00:00 DELIVERED" (copied from website).

Inspect element for the text I require from the website goes like this -

"< div style="width: calc(100% - 500px);">

< div style="max-width: 100%;width: 315px; overflow : hidden;text-overflow: ellipsis;display: -webkit-box;-webkit-line-clamp: 2;-webkit-box-orient: vertical;">

< div slot="information"> < div>04/06/2022 00:00 DELIVERED< /div>"

I have referred to other posts, and this is the code I ran -

Sub get_text_from_web()
  Dim html As Object
  Dim website As String
  
  website = "https://www.aftership.com/track/safexpress/107081775"
  Set html = CreateObject("htmlFile")
  With CreateObject("MSXML2.ServerXMLHTTP.6.0")
    .Open "GET", website, False
    .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:91.0) Gecko/20100101 Firefox/91.0"
    .Send
    
    If .Status = 200 Then
      html.body.innerHTML = .responseText
      Range("A1").Value = html.getElementsByClassName("flex flex-col justify-center")(0).innerText
    Else
      MsgBox "Page not loaded. HTTP status " & .Status
    End If
  End With
End Sub

I am getting " Error "424" Object required "

Could someone please help me with this code, or a better code, I am not versed with any type of coding other than basic vba. I would then put this code in a loop with a few changes and run it for all tracking numbers in my excel.

Thanks

CodePudding user response：

I think the problem is that the data is retrieved through a XHR request. You want to query the API endpoint https://track.aftership.com/api/v2/direct-trackings/batch. This will give you a response as JSON object, which you will need to parse (use Google to find JSON libraries for VBA). Your code would look something like this (not tested):

Dim url As String, body As String, json As String

url = "https://track.aftership.com/api/v2/direct-trackings/batch"
body = "{""direct_trackings"":[{""tracking_number"":""105431095"",""additional_fields"":{},""slug"":""safexpress""}],""translate_to"":""en""}"    
With CreateObject("MSXML2.ServerXMLHTTP.6.0")
    .Open "POST", url, False
    .SetRequestHeader "Content-Type", "application/json"
    .Send body

    If .Status = 201 Then
        json = .responseText
        'parse JSON response here
    Else
       MsgBox "Page not loaded. HTTP status " & .Status
    End If
End With