Dynamic Web Query Refresh?

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
788
Office Version
  1. 365
Platform
  1. Windows
Hi,
I have a query (get data from web > Table_2)

Will a URL like:
Code:
http://examplesite.com/go.php?account=1331&date=2019010301

Now I'm wanting to control account + date from cell values though unsure how.

I've recorded a macro and modified the URL but it is still fetching the same original table...cached maybe?

Thanks for any help
 
Last edited:
XMLHTTP only gets the initial page source. So, if data is added dynamically after the initial page has been loaded, you'll need to use Internet Explorer instead.
 
Upvote 0

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
hi @Domenic
i decided to load it this way which is slightly slower than XMLHTTP but there isnt much in it.

Rich (BB code):
Sub Web_Table_Option_Two()
    Dim HTMLDoc As New HTMLDocument
    Dim objTable As Object
    Dim lRow As Long
    Dim lngTable As Long
    Dim lngRow As Long
    Dim lngCol As Long
    Dim ActRw As Long
    Dim objIE As InternetExplorer
    Set objIE = New InternetExplorer
     
    myTeam = Range("B1").Value
    myDate = Range("B2").Value
    
    objIE.Navigate "https://www.covers.com/pageLoader/pageLoader.aspx?page=/data/mlb/teams/pastresults/" & myDate & "/team" & myTeam & ".html"


    Do Until objIE.ReadyState = 4 And Not objIE.Busy
        DoEvents
    Loop
    HTMLDoc.body.innerHTML = objIE.Document.body.innerHTML
    With HTMLDoc.body
        Set objTable = .getElementsByTagName("table")
        For lngTable = 0 To objTable.Length - 1
            For lngRow = 0 To objTable(lngTable).Rows.Length - 1
                For lngCol = 0 To objTable(lngTable).Rows(lngRow).Cells.Length - 1
                    ThisWorkbook.Sheets("Sheet2").Cells(ActRw + 4 + lngRow + 1, lngCol + 1) = objTable(lngTable).Rows(lngRow).Cells(lngCol).innerText
                Next lngCol
            Next lngRow
            ActRw = ActRw + objTable(lngTable).Rows.Length + 1
        Next lngTable
    End With
    objIE.Quit
End Sub

Now i tried to add your modification to this as in post #9 though im guessing it needs to be done slightly different as its debugging at:
Rich (BB code):
Set objTables = html.getElementsByTagName("table")

i tried:

Rich (BB code):
Sub Web_Table_Option_Two()
    Dim HTMLDoc As New HTMLDocument
    Dim objTable As Object
    Dim objTables As Object
    Dim lRow As Long
    Dim lngTable As Long
    Dim lngRow As Long
    Dim lngCol As Long
    Dim ActRw As Long
    Dim objIE As InternetExplorer
    Set objIE = New InternetExplorer
     
    myTeam = Range("B1").Value
    myDate = Range("B2").Value
    
    objIE.Navigate "https://www.covers.com/pageLoader/pageLoader.aspx?page=/data/mlb/teams/pastresults/" & myDate & "/team" & myTeam & ".html"


    Do Until objIE.ReadyState = 4 And Not objIE.Busy
        DoEvents
    Loop
    HTMLDoc.body.innerHTML = objIE.Document.body.innerHTML
    With HTMLDoc.body


Set objTables = html.getElementsByTagName("table")


If objTables.Length = 1 Then
    Set objTable = objTables(0)
Else
    Set objTable = objTables(1)
End If


        For lngTable = 0 To objTable.Length - 1
            For lngRow = 0 To objTable(lngTable).Rows.Length - 1
                For lngCol = 0 To objTable(lngTable).Rows(lngRow).Cells.Length - 1
                    ThisWorkbook.Sheets("Sheet2").Cells(ActRw + 4 + lngRow + 1, lngCol + 1) = objTable(lngTable).Rows(lngRow).Cells(lngCol).innerText
                Next lngCol
            Next lngRow
            ActRw = ActRw + objTable(lngTable).Rows.Length + 1
        Next lngTable
    End With
    objIE.Quit
End Sub
 
Upvote 0
Try...

Code:
Sub Web_Table_Option_Two()

    Dim IE As New SHDocVw.InternetExplorer
    Dim HTMLDoc As New MSHTML.HTMLDocument
    Dim HTMLTables As MSHTML.IHTMLElementCollection
    Dim HTMLTable As MSHTML.IHTMLTable
    Dim myTeam As Long
    Dim myDate As Long
    Dim lngRow As Long
    Dim lngCol As Long
    
    myTeam = Range("B1").Value
    myDate = Range("B2").Value
    
    With IE
        .Visible = False
        .navigate "https://www.covers.com/pageLoader/pageLoader.aspx?page=/data/mlb/teams/pastresults/" & myDate & "/team" & myTeam & ".html"
        Do While .Busy Or .readyState <> READYSTATE_COMPLETE
            DoEvents
        Loop
    End With
    
    Set HTMLDoc = IE.document
    
    Set HTMLTables = HTMLDoc.getElementsByTagName("table")
    
    If HTMLTables.Length = 1 Then
        Set HTMLTable = HTMLTables(0)
    Else
        Set HTMLTable = HTMLTables(1)
    End If
    
    For lngRow = 0 To HTMLTable.Rows.Length - 1
        For lngCol = 0 To HTMLTable.Rows(lngRow).Cells.Length - 1
            ThisWorkbook.Sheets("Sheet2").Cells(lngRow + 1, lngCol + 1).Value = HTMLTable.Rows(lngRow).Cells(lngCol).innerText
        Next lngCol
    Next lngRow
    
    IE.Quit
    
    Set IE = Nothing
    Set HTMLDoc = Nothing
    Set HTMLTables = Nothing
    Set HTMLTable = Nothing
    
End Sub
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,159
Members
453,021
Latest member
Justyna P

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
Back
Top