How can I make queryselector to properly loop through a range of cells and get results from a webpage

Tomkomaster

New Member
Joined
Feb 3, 2021
Messages
1
Office Version
  1. 2013
Platform
  1. Windows
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:

HTML:
<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)&nbsp;</td></tr>
    <tr><td title="Status Date" class="outboundCell">28.01.2021 12:02&nbsp;</td></tr></tbody>
</table></td>

I have written a code, which succesfully opens the webpage and gets to the desired page on that site, even it loops through the whole page, however, it gets the same data for every number, even though they are supposed to be different. Here is a part of my code, everything before this works like it should be.

VBA Code:
Dim Source As Range
        Dim Cell As Range

lastRow = Worksheets("Sheet1").Range("A1000").End(xlUp).Row
Set Source = Worksheets("Sheet1").Range("A2:A" & lastRow)

For Each Cell In Source


Dim nodes As Object, i2 As Long

Set nodes = objIE.Document.querySelectorAll("[Title='Purchase Order / Status']")

For i2 = 0 To nodes.Length - 1

    If nodes.Item(i2).innerText Like "*" & Cell.Value & "*" Then
        Cell.Offset(0, 3).Value = objIE.Document.querySelector("[Title='Planned Destinations ']").innerText
        Exit For
    End If

Next
Next

The problem is with the ...

VBA Code:
If nodes.Item(i2).innerText Like "*" & Cell.Value & "*" Then
            Cell.Offset(0, 3).Value = objIE.Document.querySelector("[Title='Planned Destinations ']").innerText
            Exit For
        End If

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)&nbsp;</td></tr>
<tr><td title="Status Date" class="outboundCell">28.01.2021 12:02&nbsp;</td></tr></tbody>
</table></td>

I have written a code, which succesfully opens the webpage and gets to the desired page on that site, even it loops through the whole page, however, it gets the same data for every number, even though they are supposed to be different. Here is a part of my code, everything before this works like it should be.

Dim Source As Range
Dim Cell As Range

lastRow = Worksheets("Sheet1").Range("A1000").End(xlUp).Row
Set Source = Worksheets("Sheet1").Range("A2:A" & lastRow)

For Each Cell In Source


Dim nodes As Object, i2 As Long

Set nodes = objIE.Document.querySelectorAll("[Title='Purchase Order / Status']")

For i2 = 0 To nodes.Length - 1

If nodes.Item(i2).innerText Like "*" & Cell.Value & "*" Then
Cell.Offset(0, 3).Value = objIE.Document.querySelector("[Title='Planned Destinations ']").innerText
Exit For
End If

Next
Next

The problem is with the ...

If nodes.Item(i2).innerText Like "*" & Cell.Value & "*" Then
Cell.Offset(0, 3).Value = objIE.Document.querySelector("[Title='Planned Destinations ']").innerText
Exit For
End If

part, where it gets the same data for each cell, the first part. its supposed to look for the "325839 / PREBOOKED" ('Purchase Order / Status') part of the code, and get the "39019 (NDC Teresin)" ('Planned Destinations ') part to cells in my workbook. There are multiple rows in my workbook, for which the code looks and loops through, but it gets the first found ('Planned Destinations ') value to each row. I just cant define properly the

VBA Code:
Cell.Offset(0, 3).Value = objIE.Document.querySelector("[Title='Planned Destinations ']").innerText

part of the code. This is an example of my workbook. It should be looking through each cell in column A, get the data and fill it into the column D, however, it fetches the same data to each of the cells in column D.

b25a0uxq3af61.png

Also tried

VBA Code:
Cell.Offset(0, 3).Value = objIE.Document.querySelector("[Title='Planned Destinations ']").Item(i2).innerText

but it just gives me an "Object doesn't support this property or method." error.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,514
Messages
5,854,168
Members
431,623
Latest member
ncorkren

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top