Excel VBA doc.querySelectorAll("p") doesn't capture/select all p elements


Yoshi

I'm trying to fix an Excel calculator that pulls the latest international surcharges for TNT from this site : https://www.tnt.com/express/en_nz/site/shipping-services/fuel-surcharges-apac . htmlAs you can see, 15.75% is the latest premium rate.

The screenshot I uploaded is a specific p tag where I want to extract the content in "15.75%". webpage Screenshot

I have the following VBA code to test that I am getting the correct elements:

Sub GetFuelSurchargeWeb()

    Dim xhr As Object
    Dim doc As MSHTML.HTMLDocument
    Dim table As Object
    Dim tableCell As HTMLHtmlElement
    Dim valCharge As String, url As String, inrText As String, searchTag1 As String, searchTag2 As String, valFrom As String
    Dim i As Integer, tag1Indx As Integer, tag2Indx As Integer, tag3Indx As Integer
    Dim searchTag3 As String
    Dim ObjP As Object

    url = "https://www.tnt.com/express/en_nz/site/shipping-services/fuel-surcharges-apac.html"
    searchTag1 = "FROM"
    searchTag2 = ":"
    searchTag3 = ":"

    On Error GoTo ErrHndlr
    Application.ScreenUpdating = False

    Set xhr = CreateObject("MSXML2.XMLHTTP")

    With xhr
        .Open "GET", url, False
        .send
        If .readyState = 4 And .status = 200 Then
            Set doc = New MSHTML.HTMLDocument
            doc.body.innerHTML = .responseText
        Else
            MsgBox "Error" & vbNewLine & "Ready state: " & .readyState & _
            vbNewLine & "HTTP request status: " & .status
        End If
    End With

    Set ObjP = doc.querySelectorAll("p")
    Debug.Print (ObjP.Length)
    For Each table In ObjP
        Debug.Print (table.innerHTML)
    Next table

When I print the innerHTML of the p tag element, it seems to grab the first paragraph like "week" "dollars per gallon" "all services" but then skips "September 23, 2019 - 2019" September 29", "1.833" "15.75%", even though they are both contained in the p tag.

I'm just getting started with VBA and am confused as to how to get this value. I would appreciate if someone could help me with a solution or alternative to get the value I want. Ideally, I'd also like to include the element "23 Sep 2019 - 29 Sep 2019" for the current week, but I'm only concerned with the premium rate right now.

Hal

The content was retrieved dynamically from another endpoint that you didn't capture. You can find it in the network tab. It returns json, so ideally you can use a json parser like jsonconverter.bas to process the response and extract the values ​​of interest. The endpoint in question is https://www.tnt.com/express/getDynamicData.apac.json

Example of extracting latest data point from json

Option Explicit

Public Sub GetData()
    Dim json As Object

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://www.tnt.com/express/getDynamicData.apac.json", False
        .setRequestHeader "User-Agent", "Mozilla/5.0"
        .send
        Set json = JsonConverter.ParseJson(.responsetext)("list")(1)
        Debug.Print json("week"), json("weeklyPrice"), json("surcharge")
    End With
End Sub

json library:

I use jsonconverter.bas. Download the original code from here and add it to the standard module called jsonConverter. Then you need to go to VBE > Tools > References > Add a reference to Microsoft Scripting Runtime. Remove the first line from the copied code .Attribute

Explore json here : https://jsoneditoronline.org/?id=7266ab97d0ac463cb934083fc549038b _

Related


Why doesn't querySelectorAll select all my elements?

Navin Rauniyar In this example, querySelectorAll selects tdelements 2 to 4: document.querySelectorAll("td + td"); <table> <tr> <td>1</td> <td>2</td> <td>3</td> <td>4</td> </tr> </table> Therefore, all tdelements greate

Why doesn't querySelectorAll select all my elements?

Navin Rauniyar In this example, querySelectorAll selects tdelements 2 to 4: document.querySelectorAll("td + td"); <table> <tr> <td>1</td> <td>2</td> <td>3</td> <td>4</td> </tr> </table> Therefore, all tdelements greate

VBA Find Text in Word Doc in Excel doesn't work - stuck

good people This is half the build process, sorry if confused I have this code where I define a dictionary in excel. From there, I want to find text from "keywords" in the Word document, and once found, I want to move on to other encodings. The problem is, I o

VBA Find Text in Word Doc in Excel doesn't work - stuck

good people This is half the build process, sorry if confused I have this code where I define a dictionary in excel. From there, I want to find the text from the "keywords" in the Word document, and once found, I want to move on to other encodings. The problem

Puppeteer querySelectorAll doesn't get elements correctly

Ryan H I'm using Puppeteer and trying to use document.querySelectorAllget a list of elements and then loop and do something, however, there seems to be a problem in my code, it either returns nothing or nothing undefined, {}although my elements are on the page

git log -p path/to/file doesn't show all changes

x-yuri I want it to show all changes (commits) to the specified file, but it doesn't do that. It only shows some of them. I guess it has something to do with merging, but I don't see what could be wrong here. Can someone explain? UPD I was able to reproduce my

P4V doesn't sync all files/folders

JuniorIncanter I'm fairly new to P4V, so I apologize if this is obvious, but I can't find a solution to my problem. I have a depot that I can connect to just fine and then navigate to the folder where I want to get the latest version. Great, no issues so far.

p:commandButton in p:dialog doesn't work

Eduardo Santos I had this problem, I searched some pages on Google here, but none of the answers worked for me. I have the following dialog: <!-- Dialog Fechar Pedido --> <p:dialog header="Fechar Pedido" widgetVar="clsPedido" id="clsPedido" minWidth="550">

p:commandButton in p:dialog doesn't work

Eduardo Santos I had this problem, I searched some pages on Google here, but none of the answers worked for me. I have the following dialog: <!-- Dialog Fechar Pedido --> <p:dialog header="Fechar Pedido" widgetVar="clsPedido" id="clsPedido" minWidth="550">

Why doesn't Esc-p or Ctrl-p work in one terminal but all others?

Alexis Wilke Somehow, when I start an X session in Ubuntu 16.04, I get a Gnome Terminal window with 6 tabs, all working as expected except for the last one. That loses the Esc- p/ Alt- Pfunctionality. Since it's available in the other 5 tabs, my thinking is, s

Why doesn't Esc-p or Ctrl-p work in one terminal but all others?

Alexis Wilke Somehow, when I start an X session in Ubuntu 16.04, I get a Gnome Terminal window with 6 tabs, all working as expected except for the last one. That loses the Esc- p/ Alt- Pfunctionality. Since it's available in the other 5 tabs, my thinking is, s

VBA code doesn't work with all Excel worksheets

Mr. Curious environment: Using VBA in Access Use DoCmd.TransferSpreadsheet acExport to export four sheets as four worksheets in a new excel file Modify the exported file using vba code in access What I want to do: There are four worksheets in the excel file. I

querySelectorAll doesn't work?

I stock up on tea Here is my javascript code: function answer(){ var list = document.querySelectorAll("#fish"); list[1].onclick = talk(); } function talk(){ alert('hello!'); } window.onload = answer(); When running, it pops up a browser window with a warnin

querySelectorAll doesn't work

username I have a requirement where I have .divto pick the last container in the container and apply some business logic to it. The selection of the last option .divmust be dynamic, as the user can choose to add/remove .divelements. I tried it initially, query

How to loop through all <p> elements?

Jack Wood I want to loop through all paragraph elements in a document and adjust their html. Currently, I use some jQuery: console.log("loop through <p> elements"); $("p").each(function() { console.log("next <p> element with id: " + this.id); }); How

Change all <p> elements with classes to <code>

Justin Balaguer This usage is working fine document.getElementsByID, but how do you change all the classes pwith labels so that it doesn't appear ?prgrphcode<p class="prgrph"></p><code class="prgrph"></code> var b = document.querySelectorAll('p'); for (var i

How to give all p elements a class name?

John Carlson I've tried to give all my p elements a class name and can't seem to figure out what the problem is. var para = document.getElementsByTag("p"); para.className += "myClass"; tyme joint venture You have to iterate over the collection and assign (als

Change all <p> elements with classes to <code>

Justin Balaguer This usage is working fine document.getElementsByID, but how do you change all the classes pwith labels so that it doesn't appear ?prgrphcode<p class="prgrph"></p><code class="prgrph"></code> var b = document.querySelectorAll('p'); for (var i

How to loop through all <p> elements?

Jack Wood I want to loop through all paragraph elements in a document and adjust their html. Currently, I use some jQuery: console.log("loop through <p> elements"); $("p").each(function() { console.log("next <p> element with id: " + this.id); }); How