creating web query: whole page only option!

T

touque

Board Regular
Joined
Jan 22, 2009
Messages
107
I am creating a Masters golf tournament pool sheet and need to set up a web query for the scores.
But every website I try to import only have the option (as indicated by small yellow box and arrow) at the very top left corner, which is the whole page.
I wish to import just the leaderboard table but it is not available to me.
Any suggestions?
 
Last edited:

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
In that case, you'll need to use an alternate method. Post the URL and someone will likely be able to provide you with a solution.
 
Upvote 0
For the following code, you'll need to set a reference (VBE > Tools > References) to Microsoft Internet Controls and Microsoft HTML Object Library. Note that while the code uses Internet Explorer to obtain the data, it remains invisible. The code also pauses for 5 seconds to ensure that the data has been fully loaded. You can probably change it to 3 seconds. Also, the data is placed in a newly created workbook.

Code: 
[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] GetLeaderboardTable()


    [COLOR=green]'Set a reference (VBE > Tools > References) to the following libraries:[/COLOR]
    [COLOR=green]'   1) Microsoft Internet Controls[/COLOR]
    [COLOR=green]'   2) Microsoft HTML Object Library[/COLOR]
    
    [COLOR=darkblue]Dim[/COLOR] IE [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]New[/COLOR] SHDocVw.InternetExplorer
    [COLOR=darkblue]Dim[/COLOR] HTMLDoc [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]New[/COLOR] MSHTML.HTMLDocument
    [COLOR=darkblue]Dim[/COLOR] HTMLTable [COLOR=darkblue]As[/COLOR] MSHTML.IHTMLTable
    [COLOR=darkblue]Dim[/COLOR] HTMLRow [COLOR=darkblue]As[/COLOR] MSHTML.IHTMLElement
    [COLOR=darkblue]Dim[/COLOR] r [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] c [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] EndTime [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Single[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] IE
        .Visible = [COLOR=darkblue]False[/COLOR]
        .navigate "https://www.foxsports.com/golf/leaderboard"
        [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]While[/COLOR] .Busy [COLOR=darkblue]Or[/COLOR] .readyState <> READYSTATE_COMPLETE
            DoEvents
        [COLOR=darkblue]Loop[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    [COLOR=green]'Pause for 5 seconds to ensure that the data has fully loaded[/COLOR]
    EndTime = Timer + 5
    [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]While[/COLOR] Timer < EndTime
        DoEvents
    [COLOR=darkblue]Loop[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] HTMLDoc = IE.document
    
    [COLOR=darkblue]Set[/COLOR] HTMLTable = HTMLDoc.getElementsByClassName("wisfb_standard wisfb_standings wisfb_hoverHighlight wisfb_golfLeaderTable")(0)
    
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] HTMLTable [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
        Workbooks.Add xlWBATWorksheet [COLOR=green]'Create a new workbook that contains one worksheet[/COLOR]
        r = 1 [COLOR=green]'Start at Row 1[/COLOR]
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] HTMLRow [COLOR=darkblue]In[/COLOR] HTMLTable.Rows
            [COLOR=darkblue]If[/COLOR] HTMLRow.Cells.Length > 1 [COLOR=darkblue]Then[/COLOR]
                [COLOR=darkblue]For[/COLOR] c = 0 [COLOR=darkblue]To[/COLOR] HTMLRow.Cells.Length - 2
                    Cells(r, c + 1).Value = HTMLRow.Cells(c).innerText
                [COLOR=darkblue]Next[/COLOR] c
                r = r + 1
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]Next[/COLOR] HTMLRow
    [COLOR=darkblue]Else[/COLOR]
        MsgBox "Leaderboard table not found!", vbExclamation
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    IE.Quit
    
    [COLOR=darkblue]Set[/COLOR] IE = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] HTMLDoc = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] HTMLTable = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] HTMLRow = [COLOR=darkblue]Nothing[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!
 
Upvote 0
it worked, thanks Domenic!
But is there a way I can get it to automatically update every minute?
 
Upvote 0
First, since you want to update the data at regular intervals, I've modified my previous code. As it stands, it loops through the leaderboard table and writes those values to the each worksheet cell individually. This method is very inefficient. Now, with my modified code, it fills an array with the data from the table, and then it transfers the content of the array to the worksheet all at once. This method should be more efficient. Also, note that it now creates a new worksheet for the table in the same workbook as the one that contains the code. However, when updating the data, it first deletes the existing worksheet, and then it creates a new one. Here is the revised code, along with the additional code to automatically update the data at regular intervals...

[In one regular module]

Code: 
[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] GetLeaderboard()


    [COLOR=green]'Set a reference (VBE > Tools > References) to the following libraries:[/COLOR]
    [COLOR=green]'   1) Microsoft Internet Controls[/COLOR]
    [COLOR=green]'   2) Microsoft HTML Object Library[/COLOR]
    
    [COLOR=darkblue]Dim[/COLOR] IE [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]New[/COLOR] SHDocVw.InternetExplorer
    [COLOR=darkblue]Dim[/COLOR] HTMLDoc [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]New[/COLOR] MSHTML.HTMLDocument
    [COLOR=darkblue]Dim[/COLOR] HTMLTable [COLOR=darkblue]As[/COLOR] MSHTML.IHTMLTable
    [COLOR=darkblue]Dim[/COLOR] HTMLRow [COLOR=darkblue]As[/COLOR] MSHTML.IHTMLElement
    [COLOR=darkblue]Dim[/COLOR] arrLeaderboard() [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] EndTime [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Single[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] r [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] c [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] IE
        .Visible = [COLOR=darkblue]False[/COLOR]
        .navigate "https://www.foxsports.com/golf/leaderboard"
        [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]While[/COLOR] .Busy [COLOR=darkblue]Or[/COLOR] .readyState <> READYSTATE_COMPLETE
            DoEvents
        [COLOR=darkblue]Loop[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    [COLOR=green]'Pause for 5 seconds to ensure data has fully loaded[/COLOR]
    EndTime = Timer + 5
    [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]While[/COLOR] Timer < EndTime
        DoEvents
    [COLOR=darkblue]Loop[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] HTMLDoc = IE.document
    
    [COLOR=darkblue]Set[/COLOR] HTMLTable = HTMLDoc.getElementsByClassName("wisfb_standard wisfb_standings wisfb_hoverHighlight wisfb_golfLeaderTable")(0)
    
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] HTMLTable [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
    
        [COLOR=green]'Allocate storage space for arrLeaderboard to hold data from the leaderboard table[/COLOR]
        [COLOR=darkblue]ReDim[/COLOR] arrLeaderboard(0 [COLOR=darkblue]To[/COLOR] HTMLTable.Rows.Length - 1, 0 [COLOR=darkblue]To[/COLOR] HTMLTable.Rows(0).Cells.Length - 1)
        
        [COLOR=green]'Fill arrLeaderboard with data from the leaderboard table[/COLOR]
        r = 0
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] HTMLRow [COLOR=darkblue]In[/COLOR] HTMLTable.Rows
            [COLOR=darkblue]If[/COLOR] HTMLRow.Cells.Length > 1 [COLOR=darkblue]Then[/COLOR]
                [COLOR=darkblue]For[/COLOR] c = 0 [COLOR=darkblue]To[/COLOR] HTMLRow.Cells.Length - 2
                    arrLeaderboard(r, c) = HTMLRow.Cells(c).innerText
                [COLOR=darkblue]Next[/COLOR] c
                r = r + 1
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]Next[/COLOR] HTMLRow
        
        [COLOR=green]'Delete the Leaderboard worksheet, if it already exists[/COLOR]
        [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
        Application.DisplayAlerts = [COLOR=darkblue]False[/COLOR]
        ThisWorkbook.Worksheets("Leaderboard").Delete
        Application.DisplayAlerts = [COLOR=darkblue]True[/COLOR]
        [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
        
        [COLOR=green]'Create a new worksheet and name it Leaderboard[/COLOR]
        ThisWorkbook.Worksheets.Add(Before:=ThisWorkbook.Worksheets(1)).Name = "Leaderboard"
        
        [COLOR=green]'Transfer the contents of arrLeaderboard to the Leaderboard worksheet[/COLOR]
        Range("A1").Resize(UBound(arrLeaderboard, 1), UBound(arrLeaderboard, 2)).Value = arrLeaderboard
        
        [COLOR=green]'Format the Leaderboard worksheet[/COLOR]
        Cells.WrapText = [COLOR=darkblue]False[/COLOR]
        Columns.AutoFit
        
    [COLOR=darkblue]Else[/COLOR]
        MsgBox "Leaderboard table not found!", vbExclamation
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    IE.Quit
    
    [COLOR=darkblue]Set[/COLOR] IE = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] HTMLDoc = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] HTMLTable = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] HTMLRow = [COLOR=darkblue]Nothing[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[In another regular module]

Code: 
[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Const[/COLOR] UpdateInterval [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR] = 1 [COLOR=green]'minute[/COLOR]

[COLOR=darkblue]Dim[/COLOR] dtNextTime [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Date[/COLOR]


[COLOR=darkblue]Sub[/COLOR] StartTimer()
    dtNextTime = Now() + TimeSerial(0, UpdateInterval, 0)
    Application.OnTime EarliestTime:=dtNextTime, Procedure:="UpdateLeaderboard"
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]


[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] UpdateLeaderboard()
    [COLOR=darkblue]Call[/COLOR] GetLeaderboard
    [COLOR=darkblue]Call[/COLOR] StartTimer
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]


[COLOR=darkblue]Sub[/COLOR] StopTimer()
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
    Application.OnTime EarliestTime:=dtNextTime, Procedure:="UpdateLeaderboard", Schedule:=[COLOR=darkblue]False[/COLOR]
[COLOR=darkblue]End[/COLOR] Sub

To generate the table, run "GetLeaderboard". To automatically update the data at regular intervals, run "StartTimer". And to stop the data from being updated, run "StopTimer". Of course, you can always attach these macros to buttons. And, lastly, you can set it up so that the table gets automatically updated at regular intervals when the workbook is opened so that you don't have to manually run a macro or click on a button.

Hope this helps!
 
Last edited:
Upvote 0
Thanks Domenic, unfortunately I did not attempt to apply your codes last Sunday.
but I will keep for a future tourney. We usually have a couple of these pools and will adjust the coding as needed. I should be able to do that.
Once again thanks!
 
Upvote 0
You're very welcome.

I hope it comes in handy in the future.

Cheers!
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Import Data from Multiple Web Pages
Replies
0
Views
247
Ka001
K
T
  • Question Question
Web query basics
2
Replies
13
Views
810
Domenic
D
P
  • Question Question
Web query works on one computer but not another
Replies
3
Views
1K
dave85716
D
MrDB4Excel
  • Solved
DuckDuckGo Conditional Format
2
Replies
12
Views
797
MrDB4Excel
MrDB4Excel
sitewolf
  • Question Question
Using JSON to pull data from web
Replies
3
Views
2K
sitewolf
sitewolf

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,619
Latest member
Shiv1198

Share this page

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