VBA Pull Data from Web table into Excel

TaskyFuji

New Member
Joined
Dec 19, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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. o_O 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.
Rich (BB code):
<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

VBA Code:
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!
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,093
Welcome

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

TaskyFuji

New Member
Joined
Dec 19, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Welcome

Can you post more HTML, preferably down to the </tbody> tag?
Thanks for responding. I hope this makes it clearer. :)

Rich (BB code):
<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

Well-known Member
Joined
Oct 30, 2011
Messages
4,093
Try this one:

table HTML.PNG

VBA Code:
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
 
Solution

TaskyFuji

New Member
Joined
Dec 19, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Try this one:

View attachment 28628
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,356
Messages
5,595,681
Members
414,009
Latest member
SNesbyCarr

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