VBA - working with data videos | Excel VBA Part 46 - Querying Web Pages with Query Tables

Posted by Andrew Gould on 14 November 2016

Querying web pages in Excel VBA is simple when you use Query Tables. This video shows you how to create a query table linked to a web page, how to choose which tables from the web page to return, and how to pass values to a URL query string to create dynamic, updatable web queries in your Excel workbooks.

You can download any files that you need to follow the video here.

You can increase the size of the video:

Full screen mode for YouTube

You can view the video in full screen mode as shown on the left, using the icon at the bottom right of the frame.

You can also increase the quality of the video:

Changing resolution

You can improve the resolution of the video using another icon at the bottom right of the frame. This will slow down the connection speed, but increase the display and sound quality. This icon only becomes visible when you start playing the video.

Finally, if nothing happens when you play the video, check that you're not using IE in compatibility view.

This page has 3 threads Add post
04 Apr 19 at 16:46

I have followed your code and produced this, which works perfectly:

 

Sub ImportWebPage()

    Dim qt As QueryTable
    Dim URL As String
   
    URL = "SomeWebPage"
   
    Set qt = Sheet1.QueryTables.Add(Connection:="URL;" & URL, Destination:=Sheet1.Range("A1"))
   
    With qt
   
        .RefreshOnFileOpen = True
   
        .Name = "SomeName"
       
        .WebFormatting = xlWebFormattingRTF
       
        .WebSelectionType = xlSpecifiedTables
       
        .WebTables = "1"
       
        .Refresh
   
    End With
   
End Sub

 

The problem is that I have additional code running afterwards but the code needs to run AFTER the code above has refreshed.

 

Adding Wait or DoEvents hasn't helped.

How can I ensure the web page has completely refreshed before I continue with the next part of the code?

Thanks

 

 

 

05 Apr 19 at 07:03

You could adapt the code shown here https://docs.microsoft.com/en-us/office/vba/api/excel.querytable.refreshing to wait until the refresh has finished.  Perhaps a simple Do Loop immediately after the .Refresh statement:

Do While .Refreshing

Loop

Hope it helps!

05 Apr 19 at 14:11

Thanks.

I found adding this line seemed to work around 90% of the time:

.BackgroundQuery = False

though I'm not sure if it was a fluke!

 

 

Andrew G  
08 Apr 19 at 08:15

Hopefully 90% is good enough!

07 Sep 18 at 10:21

Hi,

What can we do If the URL does not include the specific structure to concatenate the parameters and select specific values from a table? For example the URL for Google Finance bellow:

https://www.google.com/search?stick=H4sIAAAAAAAAA...q=finance&tbm=fin#wptab=s:H4sIA...

Thanks in advance for the assistance.

11 Sep 17 at 23:42

I am curious as to getting to a web page that requires a log in, in my case I am attempting to retrieve data from my online brokerage account. Primarily my watch lists and maybe a couple of other tables that I can reference in the Excel workbook.

After watching several of your tutorials I have a basic understanding of VBA (and growing). HTML is completely beyond my abilities, therefore, I am attempting to watch the videos again an adapt them to my specific instance.

While this isn't really required, it is an attempt to keep learning Excel and VBA.

Thank you for all of the great lessons.

13 Sep 17 at 07:57

Hi, I haven't done this myself but here's an example of someone attempting the same thing using QueryTables and VBA which you might find useful:

https://stackoverflow.com/questions/23657843/excel-vba-passing-username-password-to-web-query-connection

If it's not important to use VBA then I'd recommend using Excel's Power Query tool instead (it's called Get & Transform in Excel 2016). This will almost certainly be the easiest way to achieve what you're attempting! 

https://support.office.com/en-gb/article/Connect-to-a-web-page-Power-Query-b2725d67-c9e8-43e6-a590-c0a175bd64d8

I hope that helps!

14 Sep 17 at 10:28

Thanks for the links, I will look them over.