VBA Selenium / string Query

[lochnagar2020]

Hello,

Many thanks for taking the trouble to click on this thread. This is my first time using Selenium / VBA to interact with a webpage, so apologies in advance.


I have a large number of fuel gas compositions which I wish to calculate the methane number and propane knock index of using the Wartsila online calculator, found here:

Wärtsilä methane number calculator

This calculation tool provides guidance about the Methane Number and the result gives an indication if gas is suitable for Wärtsilä Dual Fuel engines.

www.wartsila.com

After some trial and error, it seems that I needed to Copy Full XPath and use FindElementByXPath, in order for VBA to paste the relevant MOL% figures into the correct boxes, click the CALCULATE button and copy the results into Excel.

However, as you'll see below, the XPaths are quite long - I had hoped to use the FindElementByName function, and use the component names e.g. methane, ethane etc., but the boxes don't seem to have html Name(s) or ID(s).
1) methane box XPath: /html/body/main/div[2]/div[2]/div/div[1]/table[1]/tbody/tr[1]/td[2]/input
2) ethane box XPath: /html/body/main/div[2]/div[2]/div/div[1]/table[1]/tbody/tr[2]/td[2]/input

I was hoping to use a loop for populating the MOL% boxes by incrementing the tr index, but the FindElementByXPath expects the string in parentheses e.g.
.FindElementByXPath("/html/body/main/div[2]/div[2]/div/div[1]/table[1]/tbody/tr[1]/td[2]/input")

I had tried the following:
.FindElementByXPath(xPathStart & 1 & xPathEnd)

where:
xPathString = "/html/body/main/div[2]/div[2]/div/div[1]/table[1]/tbody/tr["
xPathEnd = "]/td[2]/input"

...but no joy!

I appreciate it's easy enough to paste /html/body/main/div[2]/div[2]/div/div[1]/table[1]/tbody/tr[1]/td[2]/input into a worksheet, generate the correct XPaths (using excel functions) for all components and paste them into VBA; but a loop is more elegant. I just lack the skill / knowledge, and was hoping someone could help.

I've pasted a copy of my test subroutine below, it only changes the methane and ethane values, hence the test.

Any help etc. would be greatly appreciated.

Kind regards,
Lochnagar2020



Private mybrowser As Selenium.ChromeDriver
Sub Macro1()
Set mybrowser = New Selenium.ChromeDriver

mybrowser.Start baseUrl:="Wärtsilä methane number calculator"
mybrowser.Get "/"

Set methaneNumber = mybrowser.FindElementByXPath("/html/body/main/div[2]/div[2]/div/div[1]/table[3]/tbody/tr[2]/td[2]/input")
Set PKI = mybrowser.FindElementByXPath("/html/body/main/div[2]/div[2]/div/div[1]/table[3]/tbody/tr[3]/td[2]/input")

'// click Tracking Consent Dialog box
mybrowser.FindElementById("tracking-consent-dialog-accept").Click

'// clear pre-populate methane mole percentage then add mol% = 70
mybrowser.FindElementByXPath("/html/body/main/div[2]/div[2]/div/div[1]/table[1]/tbody/tr[1]/td[2]/input").Clear
mybrowser.FindElementByXPath("/html/body/main/div[2]/div[2]/div/div[1]/table[1]/tbody/tr[1]/td[2]/input").SendKeys "70"

'// enter ethane mole percentage then add mol% = 30
mybrowser.FindElementByXPath("/html/body/main/div[2]/div[2]/div/div[1]/table[1]/tbody/tr[2]/td[2]/input").Clear
mybrowser.FindElementByXPath("/html/body/main/div[2]/div[2]/div/div[1]/table[1]/tbody/tr[2]/td[2]/input").SendKeys "30"

'// click calculate button
mybrowser.FindElementByXPath("/html/body/main/div[2]/div[2]/div/div[1]/table[3]/tbody/tr[1]/td/button").Click

'// wait for calculator to calculate methane number and propane knock index
While methaneNumber.Value = 0
Wend

'// print methane number and propane knock index to Immediate window
Debug.Print methaneNumber.Value
Debug.Print PKI.Value

End Sub

 

[Anthony47]

Don't know if the problem is still pending, I was able to populate the inputboxes and get the result of the calculation using the following macro:

Sub Wartsilia()
Dim WPage As Object, IColl As Object
Dim myUrl As String
Dim OArr(1 To 2), I As Long
Dim kKeys As New Selenium.Keys
Dim cWm As String, nWm As String
'
'Crea Driver:
If WPage Is Nothing Then
    Set WPage = CreateObject("Selenium.CHRomedriver")
End If
'
myUrl = "https://www.wartsila.com/marine/products/gas-solutions/methane-number-calculator"
Range("D2:E2").ClearContents        'Clear output area

If WPage Is Nothing Then
     Set WPage = CreateObject("Selenium.CHRomedriver")
End If

'''On Error Resume Next
reUrl:
WPage.Get myUrl
'
WPage.Wait 1000                     'Extra delay
Debug.Print "Ready", myUrl, WPage.Url

Set IColl = WPage.FindElementsByClass("js-param-input")
'Populate InputBoxes from B2:B19
For I = 1 To IColl.Count
    IColl(I).Clear
    IColl(I).SendKeys (Range("B2").Cells(I, 1))
Next I
WPage.Wait 200
cWm = WPage.FindElementsByClass("js-wmn")(1).Attribute("value")
'Calculate:
WPage.FindElementsByClass("js-calculate")(1).SendKeys (kKeys.Enter)
'Wait for results ready:
For I = 1 To 10
    WPage.Wait 300
    nWm = WPage.FindElementsByClass("js-wmn")(1).Attribute("value")
    If Len(nWm) > 0 And cWm <> nWm Then Exit For
Next I
Debug.Print "Out ready, I=" & I
WPage.Wait 200
'
OArr(1) = WPage.FindElementsByClass("js-wmn")(1).Attribute("value")
OArr(2) = WPage.FindElementsByClass("js-pki")(1).Attribute("value")
'
'write result:
Range("D2:E2") = OArr
'
SQuit:
MsgBox ("Completed")
WPage.Quit
Set WPage = Nothing
End Sub

I prefer catching the elements by their attributes; in this case using CLASS was suitable for all of them

The code assumes that the 18 datas are on the activesheet from B2 to B19, those for the first column up to B0 and those for the second column from B11
The result, WMN and PKI, will be written in D2:E2
Some comments are in the listing, hope they can help to clarify how it works, so that you can adapt to your needs

 

[lochnagar2020]

Don't know if the problem is still pending, I was able to populate the inputboxes and get the result of the calculation using the following macro:

Sub Wartsilia()
Dim WPage As Object, IColl As Object
Dim myUrl As String
Dim OArr(1 To 2), I As Long
Dim kKeys As New Selenium.Keys
Dim cWm As String, nWm As String
'
'Crea Driver:
If WPage Is Nothing Then
    Set WPage = CreateObject("Selenium.CHRomedriver")
End If
'
myUrl = "https://www.wartsila.com/marine/products/gas-solutions/methane-number-calculator"
Range("D2:E2").ClearContents        'Clear output area

If WPage Is Nothing Then
     Set WPage = CreateObject("Selenium.CHRomedriver")
End If

'''On Error Resume Next
reUrl:
WPage.Get myUrl
'
WPage.Wait 1000                     'Extra delay
Debug.Print "Ready", myUrl, WPage.Url

Set IColl = WPage.FindElementsByClass("js-param-input")
'Populate InputBoxes from B2:B19
For I = 1 To IColl.Count
    IColl(I).Clear
    IColl(I).SendKeys (Range("B2").Cells(I, 1))
Next I
WPage.Wait 200
cWm = WPage.FindElementsByClass("js-wmn")(1).Attribute("value")
'Calculate:
WPage.FindElementsByClass("js-calculate")(1).SendKeys (kKeys.Enter)
'Wait for results ready:
For I = 1 To 10
    WPage.Wait 300
    nWm = WPage.FindElementsByClass("js-wmn")(1).Attribute("value")
    If Len(nWm) > 0 And cWm <> nWm Then Exit For
Next I
Debug.Print "Out ready, I=" & I
WPage.Wait 200
'
OArr(1) = WPage.FindElementsByClass("js-wmn")(1).Attribute("value")
OArr(2) = WPage.FindElementsByClass("js-pki")(1).Attribute("value")
'
'write result:
Range("D2:E2") = OArr
'
SQuit:
MsgBox ("Completed")
WPage.Quit
Set WPage = Nothing
End Sub

I prefer catching the elements by their attributes; in this case using CLASS was suitable for all of them

The code assumes that the 18 datas are on the activesheet from B2 to B19, those for the first column up to B0 and those for the second column from B11
The result, WMN and PKI, will be written in D2:E2
Some comments are in the listing, hope they can help to clarify how it works, so that you can adapt to your needs

Many thanks for taking the trouble to answer my query Anthony47, it's very much appreciated. I had tried FindElementsByClass, but was using "js-param-input input-box uiTextBox" instead, which returned an error, so tried XPath. How did you know to use "js-param-ws", as opposed to "js-param-input input-box uiTextBox"?

Kind regaerds,
Lochnagar2020

 

[Anthony47]

"js-param-input input-box uiTextBox" means that the element has three classes, each class assigning peculiar attributes. You have to search for one of them.
Furthermore, if your "FindElem..." returned an error, probably you used FindElementByClass, that returns a single element if it exists, or an error; and not FindElementsByClass, that returns a collection of elements even if no any element is found (in which case the collection will have a .Count=0)