CODEWITHSUNDEEP
htmlexcelvbagetelementsbyclassname
A.siaK
A.siaK

Reputation: 1

VBA Web search button - GetElementsbyClassName

I have a problem with the VBA code. I would like to open this website: https://www.tnt.com/express/en_us/site/tracking.html and in Shipment numbers search box I would like to put active cells from Excel file. At the beginning I tried to put only a specific text for example: "777777".

I wrote the below code but unfortunately, the search button is empty and there is no error. I tried everything and I have no idea what should I change in my code. Any clues? Thank you in advance.

HTML:

<input class="__c-form-field__text ng-touched ng-dirty ng-invalid" formcontrolname="query" pbconvertnewlinestocommasonpaste="" pbsearchhistorynavigation="" shamselectalltextonfocus="" type="search">

VBA:

    Sub TNT2_tracker()
Dim objIE As InternetExplorer
Dim aEle As HTMLLinkElement
Dim y As Integer
Dim result As String

Set objIE = New InternetExplorer

objIE.Visible = True
objIE.navigate "https://www.tnt.com/express/en_us/site/tracking.html"
Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop

Dim webpageelement As Object
For Each webpageelement In objIE.document.getElementsByClassName("input")
If webpageelement.Class = "__c-form-field__text ng-pristine ng-invalid ng-touched" Then
webpageelement.Value = "777"
End If
Next webpageelement


End Sub

Upvotes: 0

Views: 1296

Answers (3)

QHarr
QHarr

Reputation: 84465

As @Stavros Jon alludes to..... there is a browserless way using xhr GET request via API. It returns json and thus you ideally need to use a json parser to handle the response.

I use jsonconverter.bas as the json parser to handle the response. Download raw code from here and add to standard module called JsonConverter . You then need to go VBE > Tools > References > Add reference to Microsoft Scripting Runtime. Remove the top Attribute line from the copied code.

Example request with dummy tracking number (deliberately passed as string):

Option Explicit

Public Sub TntTracking()
    Dim json As Object, ws As Worksheet, trackingNumber As String

    trackingNumber = "1234567" 'test input value. Currently this is not a valid input but is for demo.

    Set ws = ThisWorkbook.Worksheets("Sheet1") 'for later use if writing something specific out

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://www.tnt.com/api/v3/shipment?con=" & trackingNumber & "&searchType=CON&locale=en_US&channel=OPENTRACK", False
        .send
        Set json = JsonConverter.ParseJson(.responseText)
    End With

    'do something with results
    Debug.Print json("tracker.output")("notFound").Count > 0
    Debug.Print JsonConverter.ConvertToJson(json("tracker.output")("notFound"))
End Sub

Upvotes: 0

Zwenn
Zwenn

Reputation: 2267

It seems automating this page is a litte tricky. If you change the value of the input field it doesn' t work. Nothing happen by clicking the submit button.

A look in the dom inspector shows several events for the input field. I checked them out and it seems we need to paste the value over the clipboard by trigger the paste event of the shipping field.

In order for this to work without Internet Explorer prompting, its security settings for the Internet zone must be set to allow pasting from the clipboard. I'm using a German version of IE, so I have problems explaining how to find the setting.

This macro works for me:

Sub TNT2_tracker()

Dim browser As Object
Dim url As String
Dim nodeDivWithInputField As Object
Dim nodeInputShipmentNumber As Object
Dim textToClipboard As Object

  'Dataobject by late binding to use the clipboard
  Set textToClipboard = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

  url = "https://www.tnt.com/express/en_us/site/tracking.html"

  'Initialize Internet Explorer, set visibility,
  'call URL and wait until page is fully loaded
  Set browser = CreateObject("internetexplorer.application")
  browser.Visible = True
  browser.navigate url
  Do Until browser.ReadyState = 4: DoEvents: Loop
  'Manual break for loading the page complitly
  'Application.Wait (Now + TimeSerial(pause_hours, pause_minutes, pause_seconds))
  Application.Wait (Now + TimeSerial(0, 0, 3))

  'Get div element with input field for shipment number
  Set nodeDivWithInputField = browser.Document.getElementsByClassName("pb-search-form-input-group")(0)

  If Not nodeDivWithInputField Is Nothing Then
    'If we got the div element ...
    'First child element is the input field
    Set nodeInputShipmentNumber = nodeDivWithInputField.FirstChild
    'Put shipment number to clipboard
    textToClipboard.setText "7777777"
    textToClipboard.PutInClipboard
    'Insert value by trigger paste event of the input field
    Call TriggerEvent(browser.Document, nodeInputShipmentNumber, "paste")
    'Click button
    browser.Document.getElementsByClassName("__c-btn")(0).Click
  Else
    MsgBox "No input field for shipment number found."
  End If
End Sub

And this function to trigger a html event:

Private Sub TriggerEvent(htmlDocument As Object, htmlElementWithEvent As Object, eventType As String)

  Dim theEvent As Object

  htmlElementWithEvent.Focus
  Set theEvent = htmlDocument.createEvent("HTMLEvents")
  theEvent.initEvent eventType, True, False
  htmlElementWithEvent.dispatchEvent theEvent
End Sub

Upvotes: 0

Krish
Krish

Reputation: 5917

You could use the querySelector + class name to find an element.

something like

'Find the input box
objIE.document.querySelector("input.__c-form-field__text").value = "test"

'Find the search button and do a click
objIE.document.querySelector("button.__c-btn").Click

No need to loop through elements. Unless the site allows you to search multiple tracking numbers at the same time.

Upvotes: 1

Related Questions