CODEWITHSUNDEEP
excelvbaweb-scraping
tutu
tutu

Reputation: 695

VBA scrape HTML from URL with JavaScript elements

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

Upvotes: 2

Views: 809

Answers (1)

QHarr
QHarr

Reputation: 84465

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.

Upvotes: 3

Related Questions