2

Using the following code

Sub Test()
'Must have the Microsoft HTML Object Library reference enabled
Dim oHtml As HTMLDocument
Dim oElement As Object
Dim link As String

Set oHtml = New HTMLDocument

With CreateObject("WINHTTP.WinHTTPRequest.5.1")
    .Open "GET", "https://www.afklcargo.com/WW/en/local/app/index.jsp#/tntdetails/074-26126063", False
    .Send
    oHtml.Body.innerHTML = .responseText
End With

End Sub

I am unable to get the actual HTML, I believe it's because the website is using Javascript? How can I circumvent this so I can get my value?

enter image description here

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
tutu
  • 673
  • 2
  • 13
  • 31

1 Answers1

3

It is dynamically added via another xhr call which you can find in the network tab of browser. So change your url to that and then use a json parser to parse the response.

Use a json parser, such as jsonconverter.bas to handle the response. After installing the code from that link in a standard module called JsonConverter, go to VBE > Tools > References > Add a reference to Microsoft Scripting Runtime.

You extract your value from the json which is returned as unix timestamp.

1561791600000  >  2019-06-29T07:00:00.000Z

The json response actually has all the info regarding the tracking. You can explore it here.

Option Explicit

Public Sub Test()
    Dim json As Object

    With CreateObject("WINHTTP.WinHTTPRequest.5.1")
        .Open "GET", "https://www.afklcargo.com/resources/tnt/singleAwbDetails?awbId=074-26126063", False
        .send
        Set json = JsonConverter.ParseJson(.responseText)
        Debug.Print json("booking")(1)("arrivalDateLong")("local")
    End With
End Sub

Two functions, by Schmidt and Navion for doing the conversion to datetime from stamp copied directly from here.

Function Epoch2Date(ByVal E As Currency, Optional msFrac) As Date
    Const Estart As Double = #1/1/1970#
    msFrac = 0
    If E > 10000000000@ Then E = E * 0.001: msFrac = E - Int(E)
    Epoch2Date = Estart + (E - msFrac) / 86400
End Function

Function Epoch2DateS(ByVal epochstamp$) As Date
    Epoch2DateS = CVDate(CDbl(DateSerial(1970, 1, 1)) + Int(Val(epochstamp$) / 1000#) / 86400)
End Function

N.B.

Using json parser is the reliable and recommended way, but you can also regex or split function to get the value.

QHarr
  • 83,427
  • 12
  • 54
  • 101
  • How did you get this URL in your code? The URL in the main post is different from yours. Can you explain this point for us please? – YasserKhalil Jul 03 '19 at 06:34
  • 1
    @YasserKhalil Open Chrome /FF - enter the original url and load the page. Then press F12 and goto the network tab > press F5 to refresh page. Filter on XHR in the network tab. You will see it there. See [this](https://stackoverflow.com/a/56279841/6241235) – QHarr Jul 03 '19 at 06:37
  • Thanks a lot. Another point: the UDF Epoch2DateS I got it to convert the unix timestamp (1561791600000) .. But what about the first UDF 'Epoch2Date'. Is this UDF related to the topic? Can you give me example of how to use this Epoch2Date UDF? – YasserKhalil Jul 03 '19 at 06:58
  • 1
    Both functions do the same thing i.e. you pass them the unix timestamp and a datetime is returned. It was just to show a couple of different ways. – QHarr Jul 03 '19 at 07:00