Excel VBA - Web Scraping - Get Value from HTML Table Cell

Question

I am trying to create a function that tracks the status of a cargo Airway Bill. I have created the following function with the help of SO community. I am not too familiar with HTML. I did trial and error with a couple of other airlines with GetElementby Tag/ID, etc and it worked.

However, for this particular airline, there is no Class name, tag name or id. I cannot figure out how to get the highlighted value (the value needed will be the value in the top row but the same column every time) in the attached image .

Can someone please help me how to pull that "Received" value in the table.

Function written that work's for the most part Sample Cargo Number for testing - 59473805

Function FlightStat_VA(cargoNo As Variant) As String

    Const Url = "https://www.virginatlanticcargo.com/gb/en/track/track-your-cargo.html?prefix=932&number="
    Dim dStatCheck$, deliveryStat$, S$
    
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", Url & cargoNo & "&track=go", False
        .send
        S = .responseText
    End With
    
    With CreateObject("HTMLFile")
        .write S
        On Error Resume Next


    'Need Help Here     
dStatCheck = UCase(.getElementById("...").getElementsByTagName("...")(0).innerText)
            On Error GoTo 0
            If dStatCheck <> "" Then
                deliveryStat = dStatCheck
            Else
               deliveryStat = "Not Found"
            End If
        End With
        
        FlightStat_VA = deliveryStat
    End Function

@Mech Sorry for that. I forgot to add a sample Cargo Number. Here is one - 59473805. I will edit the question to include the number as well. — Achal Desai
– Achal Desai, Commented Oct 19, 2020 at 19:41
I want "Received" for this one. And this will be the last entry in this table as the shipment was delivered. But basically what I want is to grab the inner value of latest update event (Top row) in that particular column where for this cargo number it says "Received". For example if it was another cargo number which was quite new. The table would only be of 2 rows with the tracking events displayed in reverse chronological order. 1 status saying "Shipment Booked" in bottom row. And the top row would say "Booked on Flight XYZ24" in place of "Received". So I would want "Booked on Flight XYZ24" — Achal Desai
– Achal Desai, Commented Oct 19, 2020 at 19:57
I updated my answer to make it easier to change data around. Just modify what you want in deliveryStat — Mech
– Mech, Commented Oct 19, 2020 at 20:24

QHarr · Accepted Answer · 2020-10-22 22:20:42Z

Not really any reason not to use early bound MSHTML.HTMLDocument in Microsoft HTML Object Library, as Microsoft paid a lot of money to ensure it shipped, same version, in every Excel product.

You then gain access to querySelector which allows for faster node matching via css selectors.

Option Explicit

Public Sub test()

    Debug.Print getFlightStat_VA("59473805")

End Sub

Public Function getFlightStat_VA(ByVal cargoNo As Variant) As String
    'VBE > Tools > References > Microsoft HTML Object Library
    Const URL As String = "https://www.virginatlanticcargo.com/gb/en/track/track-your-cargo.html?prefix=932&number="
    Dim dStatCheck As String, html As MSHTML.HTMLDocument
     
    Set html = New MSHTML.HTMLDocument
    
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", URL & cargoNo & "&track=go", False
        .send
        html.body.innerHTML = .responseText
    End With

    On Error Resume Next
    dStatCheck = UCase$(html.querySelector(".searchResults table").Rows(1).Children(3).innerText)
    On Error GoTo 0

    getFlightStat_VA = IIf(dStatCheck <> vbNullString, dStatCheck, "Not Found")
  
End Function

Collectives™ on Stack Overflow

Excel VBA - Web Scraping - Get Value from HTML Table Cell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related