VBA Pull Data from Web table into Excel

[TaskyFuji]

Hi,

Would like to ask assistance on my code, please. I've tried a lot of ways of trying to pull the data from a table and i always encounter error when using the class, ID and ID only pulls the headers. I'm getting desperate. Sorry, this is a protected site so i can only give you sample names/sitesThank you in advance.

The information I need is the "Important" one below.

<div id="seareResultSection:>
<br>
<style?...</style>
<style type="text/css">...</style>
<div style="width:98%; margin: 0 auto;">...</div>
<div class="gridbox" style="width: 100%;">
<div class="k=widget k-grid k-reorderable" id="searchResultGrid" style="height: 612px; width: 98%; margin : 0px auto; touch-action: pan-y;" data-role="grid"> ==$0
<div class="k-grid-header" style="padding-right; 17px;">...</div> ==$0
<div class="k-grid-content" style="height: 551px;> ==$0
    <table role="grid" data-role="selectable" class="k-selectable" style="touch-action: pan-y;">
        <colgroup>...</colgroup>
        <tbody role="rowgroup">
            <tr data-uid="0bf44c93-9b27-4db7-b7b3-1ed34ev859c5" role="row" class aria-selected="false">...</tr> ==$0
            <tr class="k-alt k-state-selected" data-uid="35ea93ce-88f9-4e54-ac82-606235c4b2e6" rolse="row" aria-selected="true"> ==$0
                <td role="gridcell" class>
                    <span> GC Stuff </span>
                </td>
                <td role="gridcell">NORTHSTORE; North Store</td> ==$0
                <td role="gridcell"> ==$0
                    <span class="ViewPersonData">UNASSIGNED </span>
                </td>
                <td role="gridcell">Fax</td> ==$0
                <td role="gridcell">Important</td> == $0

I tried below but nothing pulls up. Not sure it's the table number because nothing pulls up in Immediate window

Private Sub CommandButton3_Click()
'dimension (set aside memory for) our variables
    Dim mySh As Worksheet
    Set mySh = ThisWorkbook.Sheets("Search")
    Dim IE As Object
    Dim doc As HTMLDocument
    
    Set IE = New InternetExplorerMedium

'Launch IE
IE.Visible = True
IE.Navigate "https://website"

Do While IE.Busy
    Application.Wait DateAdd("s", 1, Now)
Loop


    
Worksheets("Search").Range("B2").Select
Selection.Copy
    

   
    IE.document.getElementById("SearchValue").Value = ActiveCell.Value
    IE.document.getElementById("searchBtn").Click
    
    Dim table As HTMLTable
    Dim trCounter As Integer
    Dim tdCounter As Integer
        trCounter = 1
    
    tdCounter = 1
    For Each table In IE.document.getElementById("seareResultSection").getElementsByTagName("table")(2)
    For Each tr In table.getElementsByTagName("tr")
    For Each td In tr.getElementsByTagName("td")
    
    mySh.Cells(trCounter, tdCounter).Value = td.innerText
    tdCounter = tdCounter + 1
    Next td
    tdCounter = 1
    trCounter = trCounter = 1
    Next tr
        
    Next
    
End Sub

i have tried others but this is the last one i've tried. Kindly help, please. Thank you!

 

[Worf]

Welcome

Can you post more HTML, preferably down to the </tbody> tag?

 

[TaskyFuji]

Welcome

Can you post more HTML, preferably down to the </tbody> tag?

Thanks for responding. I hope this makes it clearer.

<div id="seareResultSection:>
<br>
<style?...</style>
<style type="text/css">...</style>
<div style="width:98%; margin: 0 auto;">...</div>
<div class="gridbox" style="width: 100%;">
<div class="k=widget k-grid k-reorderable" id="searchResultGrid" style="height: 612px; width: 98%; margin : 0px auto; touch-action: pan-y;" data-role="grid"> ==$0
<div class="k-grid-header" style="padding-right; 17px;">...</div> ==$0
<div class="k-grid-content" style="height: 551px;> ==$0
    <table role="grid" data-role="selectable" class="k-selectable" style="touch-action: pan-y;">
        <colgroup>...</colgroup>
        <tbody role="rowgroup">
            <tr data-uid="0bf44c93-9b27-4db7-b7b3-1ed34ev859c5" role="row" class aria-selected="false">...</tr> ==$0
            <tr class="k-alt k-state-selected" data-uid="35ea93ce-88f9-4e54-ac82-606235c4b2e6" rolse="row" aria-selected="true"> ==$0
                <td role="gridcell" class>
                    <span> GC Stuff </span>
                </td>
                <td role="gridcell">NORTHSTORE; North Store</td> ==$0
                <td role="gridcell"> ==$0
                    <span class="ViewPersonData">UNASSIGNED </span>
                </td>
                <td role="gridcell">Fax</td> ==$0
                <td role="gridcell">Important</td> == $0
        <td role="gridcell">Acute</td>
        <td role="gridcell">GC Stuff </td>
        <td role="gridcell">NY</td>
        <td role="gridcell">Northeast</td>
        <td role="gridcell">12345</td>
        <td role="gridcell">Yes</td>
        <td role="gridcell">234567890</td>
        <td role="gridcell">345678</td>
        <td role="gridcell">...</td>
            <a class="AssignmentDetail">Detail</a>
            </td>
        <td role="gridcell">456789</td>
        </tr>
        </tbody>

 

[Worf]

Try this one:

Private Sub CommandButton3_Click()
Dim mySh As Worksheet, table As Object, tr, td, trCounter%, _
tdCounter%, IE As Object, doc As HTMLDocument
Set mySh = ThisWorkbook.Sheets("Search")
Set IE = New InternetExplorerMedium
IE.Visible = True
IE.navigate "c:\test\sunday2.htm"       ' local page
Do While IE.Busy
    Application.Wait DateAdd("s", 1, Now)
Loop
Set table = IE.Document.getElementsByClassName("k-selectable")
For trCounter = 0 To table(0).Rows.Length - 1
    For tdCounter = 0 To table(0).Rows(trCounter).Cells.Length - 1
        mySh.Cells(trCounter + 1, tdCounter + 1) = _
        table(0).Rows(trCounter).Cells(tdCounter).innerText
    Next
Next
End Sub

 

[TaskyFuji]

Try this one:

View attachment 28628

Private Sub CommandButton3_Click()
Dim mySh As Worksheet, table As Object, tr, td, trCounter%, _
tdCounter%, IE As Object, doc As HTMLDocument
Set mySh = ThisWorkbook.Sheets("Search")
Set IE = New InternetExplorerMedium
IE.Visible = True
IE.navigate "c:\test\sunday2.htm"       ' local page
Do While IE.Busy
    Application.Wait DateAdd("s", 1, Now)
Loop
Set table = IE.Document.getElementsByClassName("k-selectable")
For trCounter = 0 To table(0).Rows.Length - 1
    For tdCounter = 0 To table(0).Rows(trCounter).Cells.Length - 1
        mySh.Cells(trCounter + 1, tdCounter + 1) = _
        table(0).Rows(trCounter).Cells(tdCounter).innerText
    Next
Next
End Sub

Thanks so much! it works! :D