How to get specific value with getElementsByTagName with with overflow: hidden on VBA?

Question

Im trying to get some specific data from a website my workplace uses, however, I cannot figure out on how to get that specific data. Here is a snippet of the code from the website:

<tr class="outboundPlanAltRowStyle">
<td class="outboundPlanHour" style="height:25px;width:40px;white-space:nowrap;">11:00</td>
 <td onmouseover="this.className='outboundPlanHover'" onmouseout="this.className=''" 
     onclick="cellClicked(1019543,14)" 
     style="height:25px;width:150px;white-space:nowrap;" class="">
<table class="outboundPlan_PREBOOKED" style="width: 200px; table-layout: fixed" cellpadding="0px" cellspacing="0px">
   <tbody><tr>
    <td title="Purchase Order / Status" class="outboundCell"> 325839 / PREBOOKED</td></tr>
    <tr><td title="Subcontractor Name / Load Numbers " 
               class="outboundCell">Tesco FM /  - </td></tr>
    <tr><td title="Planned Destinations " class="outboundCell" 
       style="overflow: hidden"> 39019 (NDC Teresin Tesco)&nbsp;</td></tr>
    <tr><td title="Status Date" class="outboundCell">28.01.2021 12:02&nbsp;</td></tr></tbody>
</table></td>
<td onmouseover="this.className='outboundPlanHover'" onmouseout="this.className=''" 
onclick="cellClicked(1019544,14)" 
style="height:25px;width:150px;white-space:nowrap;" class="">
<table class="outboundPlan_CONFIRMED" style="width: 200px; table-layout: fixed" cellpadding="0px" cellspacing="0px"><tbody>

        <tr><td title="Purchase Order / Status" class="outboundCell"> 325676 / CONFIRMED</td>
        <td title="Released" class="outboundCell3">R</td></tr>
        <tr><td colspan="2" title="Subcontractor Name / Truck No / Trailer No " class="outboundCell">Tesco ESA  / 4H5 5484 / 4H2 6597</td></tr>
        <tr><td colspan="2" title="Load Numbers / Store Names" class="outboundCell"> 25060, 250601, 250602 /  Ceska Trebova 3k, HM USTI NO 3k, HM VYSOKE MYTO</td></tr><tr><td title="Status Date" class="outboundCell">28.01.2021 22:29</td>
        <td title="25060-31-B-HV" class="outboundCell2">32 DC2</td>
        </tr></tbody></table></td><td style="height:25px;width:150px;white-space:nowrap;"><table class="outboundPlanOpen" style="width: 200px; table-layout: fixed" cellpadding="0px" cellspacing="0px"><tbody><tr><td>&nbsp;</td></tr>

        <tr><td>(open)</td></tr>
        <tr><td>&nbsp;</td></tr><tr><td>&nbsp;</td></tr></tbody></table></td><td style="height:25px;width:150px;white-space:nowrap;"><table class="outboundPlanOpen" style="width: 200px; table-layout: fixed" cellpadding="0px" cellspacing="0px"><tbody><tr><td>&nbsp;</td></tr>
        <tr><td>(open)</td></tr>
        <tr><td>&nbsp;</td></tr><tr><td>&nbsp;</td></tr></tbody></table></td><td style="height:25px;width:150px;white-space:nowrap;"><table class="outboundPlanOpen" style="width: 200px; table-layout: fixed" cellpadding="0px" cellspacing="0px"><tbody><tr><td>&nbsp;</td></tr>
        <tr><td>(open)</td></tr>
        <tr><td>&nbsp;</td></tr><tr><td>&nbsp;</td></tr></tbody></table></td><td style="height:25px;width:150px;white-space:nowrap;"><table class="outboundPlanOpen" style="width: 200px; table-layout: fixed" cellpadding="0px" cellspacing="0px"><tbody><tr><td>&nbsp;</td></tr>
        <tr><td>(open)</td></tr>
        <tr><td>&nbsp;</td></tr><tr><td>&nbsp;</td></tr></tbody></table></td>
                        </tr>

Im trying to get the value "39019 (NDC Teresin Tesco)" from the class="outboundCell"> 325839 / PREBOOKED< class into cell A1 on Sheet1, however, i cannot define it properly. Here is the code ive got so far:

Sub WebNavigate()

im objIE As Variant
Dim WebSite As Variant
Dim Element As Variant
Dim i As Variant
    Set objIE = CreateObject("InternetExplorer.Application")
    WebSite = "https://*******.aspx"
    
    Sleep 1000
    
    With objIE
        .Visible = True
        .Navigate WebSite
        Do While .Busy Or .ReadyState <> 4
            DoEvents
        Loop

        Sleep 1000
               
        objIE.Document.all.Item("ctl00$Content$Login1$UserName").Value = "*****"
        objIE.Document.all.Item("ctl00$Content$Login1$Password").Value = "*****"
        
        Sleep 1000
        
        objIE.Document.getElementsByName("ctl00$Content$Login1$LoginButton")(0).Click
        
        Sleep 1000
               
        End With
         If objIE.ReadyState = 4 Then
        objIE.Navigate "https://******"
        End If
        
        Sleep 1000
        
        
        Do While objIE.Busy Or objIE.ReadyState <> 4
            DoEvents
        Loop
        
        Sleep 1000
        
        If objIE.ReadyState = 4 Then
        Dim getTodayDay As Variant
        getTodayDay = Day(Now) '- 1
        objIE.Document.getElementsByClassName("CalendarDayNo")(getTodayDay).Click
        End If
        
        Sleep 1000
        
        Do While objIE.Busy Or objIE.ReadyState <> 4
            DoEvents
        Loop
        
        Sleep 1000
        
        If objIE.ReadyState = 4 Then

Dim text As String
Dim Data As Variant
text = " 325839 / PREBOOKED"
Set Data =objIE.Document.getElementsByClassName("outboundCell")(text).

Sleep 1000
Do While objIE.Busy Or objIE.ReadyState <> 4
            DoEvents
        Loop

        
        End If

End Sub

I intentionally left out the name of the site and login name and password. The snippet of code I worte successfully takes me to the relevant page, where the information is written, however, I just cant figure out on how to get it. That is why the Set Data =objIE.Document.getElementsByClassName("outboundCell")(text). part isn't finished.

`getElementsByClassName("outboundCell")` gives you a collection of elements - you will need to loop over those elements and check for the item which has title = "Planned Destinations " — Tim Williams, Jan 28 '21 at 23:47

QHarr · Accepted Answer · 2021-02-20T00:02:54.913

0

Use an attribute = value css selector to target the Title attribute by its value

 objIE.Document.querySelector("[Title='Planned Destinations']").innerText

Read about attribute selectors here: https://developer.mozilla.org/en-US/docs/Web/CSS/CSS_Selectors

More than one matching node, gather a nodeList with querySelectorAll and loop

Dim nodes As Object, i As Long

Set nodes = objIE.Document.querySelectorAll("[Title='Planned Destinations']")

For i = 0 To nodes.Length -1

    If nodes.item(i).innerText = " 325839 / PREBOOKED" Then
        'Do something here
        Exit For
    End If

Next

You can use LIKE with wildcard(s), or Instr, if you only know part of the desired string to match on. If you cannot predict the string/substring to match on then you will need to look at positional matching (whether absolute - index; or, relative - in relation to other element(s)).

Additional link from @TimWilliams:

querySelector and querySelectorAll vs getElementsByClassName and getElementById in JavaScript

edited Feb 20 '21 at 00:02

answered Jan 29 '21 at 00:28

QHarr

83,427
12
54
101

Im trying `Data =objIE.Document.getElementsByClassName("outboundCell")(text).objIE.Document.querySelector("[Title='Planned Destinations']").innerText` , but it gives me an `Automation Error` – Tomas Perlecky Jan 29 '21 at 01:43
Did you try as I have written above? If you want to write it to a cell e.g. `ActiveSheet.Cells(1,1) = objIE.Document.querySelector("[Title='Planned Destinations']").innerText` – QHarr Jan 29 '21 at 01:45
Yes, it says `Object Required` . But on the site, there are multiple of these Planned Destinations Titles, thats why I need the `getElementsByClassName("outboundCell")` and the `text = " 325839 / PREBOOKED"` along with it. – Tomas Perlecky Jan 29 '21 at 02:02
If it can't even find one then it doesn't matter how many there are at present. If you step through the code with F8 executing line by line does the error message go away? Is the target element inside a parent iframe/frame? – QHarr Jan 29 '21 at 02:07
bed time for me now – QHarr Jan 29 '21 at 02:11
Okey, I finally fixed it, now it finds one, I just didnt noticed that there was an another space at the name of the title. Now how to find the one I need ? – Tomas Perlecky Jan 29 '21 at 02:54
https://stackoverflow.com/questions/14377590/queryselector-and-queryselectorall-vs-getelementsbyclassname-and-getelementbyid – Tim Williams Jan 29 '21 at 05:45
@TimWilliams Nice link. Haven't seen that one before. Thanks. – QHarr Jan 29 '21 at 08:28
@TomasPerlecky At this point switch to using querySelectorAll and then loop the returned nodeList examining the .innerText of each node until you find the required node, then exit for (or exhaust list). – QHarr Jan 29 '21 at 08:29
I'm trying, but it just doesm't return anything at all. `Dim nodes As Object, i2 As Long Set nodes = objIE.Document.querySelectorAll("[Title='Planned Destinations']") For i2 = 0 To nodes.Length - 1 If nodes.Item(i2).innerText Like "*325955*" Then MsgBox "It's working" Exit For End If Next` It should be showing me the text "Its working", but it just doenst show anything. Even if there is only exactly the one I need. – Tomas Perlecky Feb 01 '21 at 16:58
1

Fixed it by referring "Purchase Order" instead of "Planned Destinations." – Tomas Perlecky Feb 01 '21 at 17:53

How to get specific value with getElementsByTagName with with overflow: hidden on VBA?

1 Answers1