How to track details of a shipment in excel using VBA.

Topic

New Reply

I have so many shipment details that I need to track in an Excel spreadsheet daily. Doing it manually is so hectic. For example, I have tracking IDs in a row of Sheet1 and would like to pull tracking data in the message box. The shipping line website is http://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 a message box using VBA.

Is it possible to reflect the tracking details of the shipment by running a VBA keeping the cursor on the tracking number?

Reply | Quote

Replies

Do you mean pull data from the web site?
There are plenty of examples on the web showing how this is done.

If you can work out the correct page to download the shipping data (get the code running to do the data collection) we can help make it work in your spreadsheet.

cheers, Paul

Reply | Quote

Hi Paul,

Thanks for your feedback. I have tried with the below code but it is not pulling data properly.

`Sub a()
Dim trackNum As String, IE As Object, table As MSHTML.HTMLTable, finalPODETA As String, row As Double, col As Double
On Error GoTo error1
trackNum = "CAIU2704696"
Set IE = CreateObject("InternetExplorer.Application")
IE.navigate "https://www.msc.com/nld#"
IE.Visible = True
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
error1:
MsgBox "Tracking data retrieval failed, please check accuracy of tracking number."
End Sub
Function delay(seconds As Long)
Dim endTime As Date
endTime = DateAdd("s", seconds, Now())
Do While Now() < endTime
DoEvents
Loop
End Function`

Could you please help me to pull data on the message box?

[Moderator edit] put code into code block

Reply | Quote

What data are you getting back? Can you post a screenshot? (Save as PNG, click Select File.)

cheers, Paul

Reply | Quote

Hi Paul,

I am getting below error.
However, I have applied the VBA found on the internet. But I am trying to get the tracking data in VBA message box.
Thanks for your time.

Regards
Shazzad

Reply | Quote

Label “error1” is shown in the code you posted earlier – there are quite a few difference between the two.
Can you use the original code and see what it does?

cheers, Paul

p.s. I don’t have Excel so I can’t test the code for you.

Reply | Quote

Hi Paul
It did the same as error. I am not expert enough to fix the issue. And that is why I am seeking support.

Thanks.

Shazzad

Reply | Quote