How to track shipment details in Excel without opening browser?

Question

I have so many shipment details that I need to track and record the input in an Excel spreadsheet daily. Doing it manually is so hectic. For example, I have tracking IDs in column A of Sheet1 and would like to pull tracking data from column B onwards. The shipping line website is www.msc.com. Suppose, tracking IDs (BL number) are MEDUUG990010, MEDUUG990093, MEDUUG990119 and each shipment is carrying five containers. In that case, details of all the containers of each shipment will be reflected in the Excel file like the attached picture.

I have tried below VBA but it is not working by saying Run time error 424: Object required.

Sub track_shipment()
Dim trackNum As String, IE As Object, table As MSHTML.HTMLTable, finalPODETA As String, row As Double, col As Double
trackNum = "MEDUUG931675"
Set IE = CreateObject("InternetExplorer.Application")
IE.navigate "https://www.msc.com"
IE.Visible = False
While IE.busy
    DoEvents
Wend
Call delay(2)
IE.document.getElementById("ctl00_ctl00_Header_TrackSearch_txtBolSearch_TextField").Value = trackNum
IE.document.getElementById("ctl00_ctl00_Header_TrackSearch_hlkSearch").Click
Call delay(2)
finalPODETA = IE.document.getElementById("ctl00_ctl00_plcMain_plcMain_rptBOL_ctl00_rptContainers_ctl01_pnlContainer").getElementsByClassName("containerStats singleRowTable table-equal-3")(0).getElementsByTagName("td")(2).textContent
finalPODETA = Trim(finalPODETA)
Set table = IE.document.getElementById("ctl00_ctl00_plcMain_plcMain_rptBOL_ctl00_rptContainers_ctl01_pnlContainer").getElementsByClassName("resultTable")(0)
For row = 0 To table.Rows.Length - 1
    For col = 0 To 4
        Sheet1.Cells(5 + row, col + 1).Value = table.Rows(row).Cells(col).innerText
    Next col
Next row
Sheets("Sheet1").Range("A1") = finalPODETA
Exit Sub

End Sub Function delay(seconds As Long) Dim endTime As Date endTime = DateAdd("s", seconds, Now()) Do While Now() < endTime DoEvents Loop End Function

Is there any way to track and record the data in an Excel file without opening the browser using VBA?

Answer 1

@S.Shotez you can use API of msc.com, you can test API response here: https://developerportal.msc.com/api-details#api=DPO-DCSATrackAndTrace-API-V2&operation=GET-events

just insert tracking item MEDUUG990010 in user form and it will return you URL: https://ovhweportalapim.azure-api.net/dpo/trackandtrace/v2.2/events?carrierBookingReference=MEDUUG990010

copy into browser or click on url above and you will see JSON that can be parsed using JsonCoverter, here the link: https://github.com/VBA-tools/VBA-JSON/blob/master/JsonConverter.bas

you need do import JsonConverter.bas into your VBAProject, here example how to work with api response:

Function MscApiJsonOutput(url As String) As Object
    Dim mscService As New MSXML2.XMLHTTP60 'you need to activate "Microsoft XM L, v6.0" in References
    With mscService
        .Open "Get", url, False
        .SetRequestHeader "Content-Type", "application/json"
        .Send ""
        '.Status ' you can chech status before parse, e.g. if .Status = "401" then ....
        Set MscApiJsonOutput = JsonConverter.ParseJson(.responseText)
    End With
End Function

and routine that will use function above:

Sub getDataFromMsc()

    Dim baseUrl As String
    baseUrl = "https://ovhweportalapim.azure-api.net/dpo/trackandtrace/v2.2/events?carrierBookingReference="
    Dim shipment As String
    Dim json As Object
    Dim item, key
    
    shipment = "MEDUUG990010"
    
    Set json = MscApiJsonOutput(baseUrl & shipment)

    'here you need to extract the data you required   
    For Each item In json
        For Each key In item
            On Error Resume Next
            Debug.Print key, " : ", item(key)
            If Err.Number <> 0 Then
                Debug.Print key, " : Array ... "
                Err.Clear
            End If
    Next key, item
    
End Sub

Also, you need to add "Microsoft Scripting Runtime" into References because JsonConverter.bas use Dictionary.

tested: