Excel VBA - Web scraping - Track parcel - deal with error where tracking number is incorrect

Question

I am trying to create a function that grabs the status of an airway bill by using a tracking number. I have managed to create a function that grabs the status correctly with the help of the stackoverflow community.

However, I am trying to add in the error handling where the tracking number may be incorrect. With the current function, it correctly gets the result if the tracking number is valid. But when an incorrect number is provided, the function returns a 0 value and keeps running in a loop in the background. When stopped from the VBA editor, excel crashes.

This is the code I have come up with so far. Any help to add this error handling would be appreciated. Sample Correct Cargo Number: 92366691 Sample Incorrect Cargo Number: 59473805

 Function FlightStat_AF(cargoNo As Variant) As String
 Dim url As String, ie As Object, result As String

  url = "https://www.afklcargo.com/mycargo/shipment/detail/057-" & cargoNo

  Set ie = CreateObject("InternetExplorer.Application")
  With ie
    .Visible = False
    .navigate url
    Do Until .readyState = 4: DoEvents: Loop
  End With
  'wait a little for dynamic content to be loaded
  Application.Wait (Now + TimeSerial(0, 0, 1))

  'Get the status from the table
  Do While result = ""
    DoEvents
    On Error Resume Next
     result = Trim(ie.document.getElementsByClassName("fs-12 body-font-bold")(1).innerText)
    On Error GoTo 0
    Application.Wait (Now + TimeSerial(0, 0, 1))
  Loop

  ie.Quit: Set ie = Nothing
  
  'Return value of the function
  FlightStat_AF = result
End Function

Zwenn · Accepted Answer · 2020-09-27T15:17:32.373

3

I learned a lot today and I'am very happy about that. My code based on this answer, I learned all the new things from^^
Scraping specific data inside a table II (Answer by SIM)

You ask about how to avoid an error when you send a wrong ID. Here is the answer how you can deal with that error and the error when you send an ID in the wrong format of an ID.

This is the Sub() to test the function:

Sub test()
  'A valid ID
  MsgBox FlightStat_AF("92366691")
  
  'A wrong ID
  'The whole string is "The provided AWB(s) is either invalid, not found or you are not authorized for it."
  'The function FlightStat_AF cuts the string by comma
  'So it delivers "The provided AWB(s) is either invalid"
  'I'am not clear with regex till now and used it like the macro this code is based on ;-)
  MsgBox FlightStat_AF("59473805")
  
  'Somthing else than a valid ID format
  MsgBox FlightStat_AF("blub")
End Sub

This is the function() to get the answer you want:

Function FlightStat_AF(cargoNo As Variant) As String

  Const url = "https://www.afklcargo.com/mycargo/api/shipment/detail/057-"
  Dim elem As Object
  Dim result As String
  Dim askFor As String
  
  With CreateObject("MSXML2.XMLHTTP")
    .Open "GET", url & cargoNo, False
    .send
    result = .responseText
    
    If .Status = 200 Then
      If InStr(1, result, "faultDescription") = 0 Then
        askFor = """metaStatus"""
      Else
        askFor = """faultDescription"""
      End If
      
      With CreateObject("VBScript.RegExp")
        .Global = True
        .MultiLine = True
        .Pattern = askFor & ":(.*?),"
        Set elem = .Execute(result)
      End With
      
      If Not elem Is Nothing Then
        result = Replace(elem(0).SubMatches(0), Chr(34), "")
      Else
        result = "No Value"
      End If
    Else
      result = "No cargoID"
    End If
  End With
  
  FlightStat_AF = result
End Function

edited Sep 27 '20 at 15:17

answered Sep 24 '20 at 20:18

Zwenn

2,147
2
8
14

What happens when no match found? You might want to add a test in there of elem is not nothing. – QHarr Sep 24 '20 at 20:21
@QHarr You are a hard teacher ;-) Thats ok :-) I thought I had covered all possible cases. Do you have an example of a passed parameter that gives a VBA runtime error? – Zwenn Sep 24 '20 at 20:27
I'm sorry Zwenn. I don't want to seem mean! I like seeing your answers. I often am lazy and don't always put in all the good checks but I saw you had put in a lot of effort for checks. With respect to your runtime error are you asking for a case that might fail with your code as is? – QHarr Sep 24 '20 at 20:28
It may not simply be a case of the passed param being a problem but that the regex does not return a match. You are likely ok but belts and braces. At least you would cater for change to html/page. Whilst being tough you could pass ByVal as string if entering as shown in code – QHarr Sep 24 '20 at 20:37
@QHarr You don't seem mean to me. Everything is ok :-) The case that might fail was the "blub" parameter. But I intercepted it. I have no idea what else can happen to throw a runtime error in the function. (Sorry, I use SO also to learn more english. So my answers come slowly.) – Zwenn Sep 24 '20 at 20:42
@Zwenn the code worked perfectly!! thank you so much! Just one question though. I have to create functions for 5 different airlines. Is there a way I can replicate this code for 4 other airlines as well? And another question was on the MSXML2.XMLHTTP method, some of the airlines load the data dynamically after the page is loaded and I have heard from the community that this method cannot grab the data I want if the element is loaded after the HTML loads. And if you could also please explain the `result = Replace(elem(0).SubMatches(0), Chr(34), "")` because that will change for each airline. – Achal Desai Sep 25 '20 at 09:38
@AchalDesai As discussed in the past, a separate solution must be programmed for each page. https://stackoverflow.com/questions/63742560/excel-vba-web-scraping-get-value-in-html-table-cell For Reaplace() look here https://learn.microsoft.com/de-de/office/vba/language/reference/user-interface-help/replace-function I'm also not familiar with RegExp and have taken `elem(0).SubMatches(0)` from the linked macro. I have an idea what happens there, but I cannot explain it in detail. – Zwenn Sep 27 '20 at 15:16
@QHarr Now even I understood what you meant ;-) For the sake of completeness I have added it to the function. – Zwenn Sep 27 '20 at 15:18

score 0 · Answer 2 · answered Sep 23 '20 at 19:37

0

a way to check that the url is valid is to use the function below :

Public Function URLexist(urlToCheck  As String) As Boolean
    'source :  https://excel-malin.com

    On Error GoTo Err

    Dim oXHTTP As Object
    Set oXHTTP = CreateObject("MSXML2.XMLHTTP")

    oXHTTP.Open "HEAD", urlToCheck , False
    oXHTTP.send

    URLexist = (oXHTTP.Status = 200)
    Exit Function

    Err:
    URLexist = False
End Function

answered Sep 23 '20 at 19:37

E Jestin

121
4

Hi Jestin. Thank you for the answer. But I am new to VBA and web scraping. Could you tell me how I could integrate this into my existing function? – Achal Desai Sep 24 '20 at 16:35
before calling ie.navigate(url) you should test if the url exists : If URLexist(url) Then ..... – E Jestin Sep 25 '20 at 06:17

Excel VBA - Web scraping - Track parcel - deal with error where tracking number is incorrect

2 Answers2