VBA - scraping websites 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 6 threads Add post
duggie   Reply to duggieReply
20 May 19 at 16:51

Andrew,

Quick question here:

Assuming I have some data (10 rows) on Sheet1 via a query, if I execute this:

Sheet1.Range("A1").ListObject.QueryTable.Refresh BackgroundQuery:=False

will it clear the the entire contents of Sheet1 first before populating it?

My concern is if the webpage which the query is fetching from has been updated / changed and now only contains fewer than 10 rows (say only 3 rows), I don't want the query to simply grab those 3 rows and paste onto Range("A1") (and leaving the old rows 4 to 10 data still on the sheet).

Thanks

 

Andrew G   Reply to Andrew GReply
21 May 19 at 21:29

Hi Duggie,

From memory (it's been a while) the query table will grow and shrink according to the number of rows returned when it's refreshed so it won't leave historical data sitting in the worksheet if the query returns fewer rows the next time it's refreshed.

I hope that helps!

duggie   Reply to duggieReply
22 May 19 at 23:21

Thanks Andrew.

What I have noticed is the columns DO NOT alter, ie if the query only contains 10 columns and I manually add an eleventh, after clicking refresh, the eleventh column remains.

 

 

duggie   Reply to duggieReply
08 May 19 at 20:58

Andrew,

Do you know if it's possible to delete date from a worksheet but still preserving the query behind it?

 

Andrew G   Reply to Andrew GReply
09 May 19 at 08:10

Hi duggie,

Yes, if you create a query table and then subsequently delete the cells the connection will still exist.  You can test that pretty easily with the code in the sample workbook:

Sub ImportXratesThenDeleteTable()

    Dim ws As Worksheet
    Dim qt As QueryTable
    Dim URL As String
    
    URL = "http://www.x-rates.com/table/?from=USD&amount=1"
    
    Set ws = Worksheets.Add
    
    Debug.Print "Count of query tables at start " & ws.QueryTables.Count
    
    Set qt = ws.QueryTables.Add( _
        Connection:="URL;" & URL, _
        Destination:=Range("A5"))
        
    With qt
        .RefreshOnFileOpen = True
        .Name = "XRates"
        .WebFormatting = xlWebFormattingRTF
        .WebSelectionType = xlSpecifiedTables
        .WebTables = "1"
    
        .Refresh
    End With
    
    Debug.Print "Count of query tables after creating one " & ws.QueryTables.Count
    
    Range("A5").CurrentRegion.Delete
    
    Debug.Print "Count of query tables after deleting cells " & ws.QueryTables.Count
    
End Sub

Output:

Count of query tables at start 0
Count of query tables after creating one 1
Count of query tables after deleting cells 1

I hope that helps!

duggie   Reply to duggieReply
09 May 19 at 11:01

Andrew,

I think I stumbled across a solution myself instead!

Instead of

    Range("A5").CurrentRegion.Delete

I changed it to 

     Range("A5").CurrentRegion.Clear

and after the worksheet was wiped, I could stil right-click and click refresh to retrieve the table.

Thanks again for your help.

 

 

duggie   Reply to duggieReply
09 May 19 at 10:54

Andrew,

Thanks for your prompt response.

I tried your code and it worked - sort of!

I put a break point in this line:

Range("A5").CurrentRegion.Delete

and could see in the immediate window that the count of query tables is 1.

After executing the Delete line, the count of query tables is still 1.

By now the worksheet contains no data. However, right-clicking on cell A5, I do not see the option to refresh the query.

I tested this in Excel 2010.

 

 

Andrew G  
10 May 19 at 06:52

Oh, but that's what you asked for!  The cells are deleted but the query remains in the workbook.  You didn't mention that you wanted to retain the ability to right-click the cells to refresh the table!

Anyway, glad that you found a solution that worked for you!

duggie   Reply to duggieReply
02 May 19 at 14:12

Hi,

If the table on a webpage contains 100 records, yet only the first 50 is shown on the first webpage and the remaining on the second webpage, how can I adapt the code so it brings back the full 100 records onto my worksheet?

Thanks

 

Andrew G   Reply to Andrew GReply
03 May 19 at 07:23

Hi duggie,

It's difficult to answer that one without seeing the pages you mention but can you just import each table separately and place one below the other in a worksheet?  It sounds like the simplest approach to what you've described!

duggie   Reply to duggieReply
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

Andrew G   Reply to Andrew GReply
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!

duggie   Reply to duggieReply
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!

Costas   Reply to CostasReply
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.

Andrew G   Reply to Andrew GReply
07 Sep 18 at 14:12
ODogNTricks   Reply to ODogNTricksReply
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.

Andrew G   Reply to Andrew GReply
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!

ODogNTricks   Reply to ODogNTricksReply
14 Sep 17 at 10:28

Thanks for the links, I will look them over.

Add a new post