How can I pull data from a specific table with VBA on a webpage with lots of tables.

[borski88]

Here is my current code:

[FONT=courier new]Sub Pull_URL_Data()

                Dim myURL As String: 'URL String  
                 myURL = "https://www.website.com
                Call pullDataSingleURL(myURL, Sheets("Sheet1"))
                   
End Sub

Function pullDataSingleURL(URL As String, ws As Worksheet)
    'Add: (menu) Tools/References/'Microsoft XML V6.0' & 'Microsoft Forms 2.0 Object Library'
    'Dim httpRequest As XMLHTTP      'XML V6.0
    Dim httpRequest As MSXML2.XMLHTTP60      'MSXML2.DOMDocument
    Dim DataObj As New MSForms.DataObject   'Forms 2.0
    
    'Set httpRequest = New XMLHTTP
    Set httpRequest = CreateObject("MSXML2.XMLHTTP.6.0")
    httpRequest.Open "GET", URL, False
    httpRequest.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    httpRequest.send ""
    
    DataObj.SetText httpRequest.responseText
    DataObj.PutInClipboard
    
    DoEvents
    
    With ws
        .Activate
        .Cells.Clear
        .Cells(1, 1).Select
        .PasteSpecial Format:="Text", link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True
    End With
End Function[/FONT]

The problem is the page I am trying to pull data from has a ton of tables on it.

In the source code I found the table id:

table id="function-4300002411" class="sortable result-table align-left"

Is there any way to use this info to only pull that specific data?

 

[VBA Geek]

Have you tried using a simple Web Query? At the top of Excel, go to DATA > Get External Data > From WEB

 

[borski88]

I get an error when trying to pull data from that site using the the Simple Web Query method.

The above method I posted seems to get around the error but I can't specify the table that I need.

 

[VBA Geek]

I get an error when trying to pull data from that site using the the Simple Web Query method.

The above method I posted seems to get around the error but I can't specify the table that I need.

what is the website and table?

 

[borski88]

It's an internal site, sorry.

 

[borski88]

what is the website and table?

Actually, I was able to get your method to work after using the macro recorder when making the connection, then making some edits to the VBA. thank you.