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]

Let's say that A2 contains the account and B2 contains the date...

"http://examplesite.com/go.php?account=" & Range("a2").value & "&date=" & Format(Range("b2").value, "yyyymmdd")

Hope this helps!

 

[JumboCactuar]

@Domenic

Yes I know to create URL like this, the problem is it always refreshs the very first table link. It's like it's cached or something.

I read something about setting Parameters but unsure how

 

[Domenic]

Can you post your code?

 

[JumboCactuar]

@Domenic this is an example

from this site:
https://www.covers.com/pageLoader/p...data/mlb/teams/pastresults/2018/team2978.html

I want to control the team number from cell B1 and year by cell B2

this is the code i get when recording macro

Sub Macro3()'
    ActiveWorkbook.Queries.Add Name:="Regular Season", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(""https://www.covers.com/pageLoader/pageLoader.aspx?page=/data/mlb/teams/pastresults/2018/team2978.html""))," & Chr(13) & "" & Chr(10) & "    Data0 = Source{0}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Data0,{{""Date"", type text}, {""Vs"", type text}, {""Score"", type text}, {""Away Starter"", type text}, {""Home Starter"", type text}, " & _
        "{""DET Line"", type text}, {""O/U"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Regular Season"";Extended Properties=""""" _
        , Destination:=Range("$A$5")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Regular Season]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Regular_Season"
        .Refresh BackgroundQuery:=False
    End With
End Sub

I know i can build URL like this but unsure how to load this into the existing query, if i clear table then re-run macro it says "Reqular Season already Exists"

TeamNo = Range("B1").Value
DateVal = Range("B2").Value

URL = "https://www.covers.com/pageLoader/pageLoader.aspx?page=/data/mlb/teams/pastresults/" & DateVal & "/team" & TeamNo & ".html"

basically want it to work like this, enter date/team then refresh

some team numbers
2958
2959
2962
2971

dates go 2018 and under
appreciate any help

 

[JumboCactuar]

i figured this out using the following code, but still would like any suggestions to improve this

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

 

[Domenic]

Just in case you might be interested, with regards to your original method, it looks like the column headers/fields for each table that results from a query can be different. If this was not the case, and you only needed to change the team and year, you could simply first update the formula for your query...

    ActiveWorkbook.Queries("Regular Season").Formula = " . . . "

Then you could refresh your query...

    ActiveSheet.ListObjects("Regular_Season").QueryTable.Refresh

 

[JumboCactuar]

@Domenic thankyou i will save this if needed

im actually using this way to extract table as im finding it much faster than any other method
(with references: Microsoft HTML Object Library and Internet Controls)

Sub Web_Table_Option_One()Dim xml    As Object
Dim html   As Object
Dim objTable As Object
Dim result As String
Dim lRow As Long
Dim lngTable As Long
Dim lngRow As Long
Dim lngCol As Long
Dim ActRw As Long
Set xml = CreateObject("MSXML2.XMLHTTP.6.0")
With xml


.Open "GET", "https://www.covers.com/pageLoader/pageLoader.aspx?page=/data/mlb/teams/pastresults/2017/team2978.html", False
.send
End With
result = xml.responseText
Set html = CreateObject("htmlfile")
html.body.innerHTML = result
Set objTable = html.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("Sheet1").Cells(ActRw + 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 Sub

though for websites with multiple tables im unsure how to extract specific ones as it grabs them all.

 

[Domenic]

First, since you're using the XMLHTTP object instead of Internet Explorer, I assume you meant you set a reference to Microsoft XML, vX.0. In any case, since your code is using late binding, you don't need to set a reference to the library for either Microsoft HTML or Microsoft XML.

Now, assuming that you're interested in only the regular season stats, not the playoff stats, the following amended code first checks how many tables exists. If more than one table exists, it chooses the second one. Otherwise, it chooses the first one. Note that the index for the tables are 0-based.

Sub Web_Table_Option_One()
Dim xml    As Object
Dim html   As Object
[COLOR=#ff0000]Dim objTables As Object[/COLOR]
Dim objTable As Object
Dim result As String
'Dim lRow As Long
'Dim lngTable As Long
Dim lngRow As Long
Dim lngCol As Long
'Dim ActRw As Long
Set xml = CreateObject("MSXML2.XMLHTTP.6.0")
With xml
.Open "GET", "https://www.covers.com/pageLoader/pageLoader.aspx?page=/data/mlb/teams/pastresults/[COLOR=#ff0000]2013[/COLOR]/team2978.html", False
.send
End With
result = xml.responseText
Set html = CreateObject("htmlfile")
html.body.innerHTML = result

[COLOR=#ff0000]Set objTables = html.getelementsbytagname("table")[/COLOR]
[COLOR=#ff0000]
[/COLOR][COLOR=#ff0000]If objTables.Length = 1 Then[/COLOR]
[COLOR=#ff0000]    Set objTable = objTables(0)[/COLOR]
[COLOR=#ff0000]Else[/COLOR]
[COLOR=#ff0000]    Set objTable = objTables(1)[/COLOR]
[COLOR=#ff0000]End If[/COLOR]

[COLOR=#ff0000]For lngRow = 0 To objTable.Rows.Length - 1[/COLOR]
[COLOR=#ff0000]    For lngCol = 0 To objTable.Rows(lngRow).Cells.Length - 1[/COLOR]
[COLOR=#ff0000]        ThisWorkbook.Sheets("Sheet1").Cells(lngRow + 1, lngCol + 1) = objTable.Rows(lngRow).Cells(lngCol).innerText[/COLOR]
[COLOR=#ff0000]    Next lngCol[/COLOR]
[COLOR=#ff0000]Next lngRow[/COLOR]

End Sub

Hope this helps!

 

[JumboCactuar]

Thanks it works

Only problem I've found with using XMLHTTP object

If you are grabbing a table with live data, and you refresh this macro - it's like it redownloads the cache if that makes sense?
As it loads instantly and is a replica of previous data.