Dynamic Web Query Refresh?

[JumboCactuar]

Hi,
I have a query (get data from web > Table_2)

Will a URL like:

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

 

[Domenic]

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.

 

[JumboCactuar]

hi @Domenic
i decided to load it this way which is slightly slower than XMLHTTP but there isnt much in it.

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:

Set objTables = html.getElementsByTagName("table")

i tried:

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

 

[Domenic]

Try...

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