VBA - WORKING WITH DATA VIDEOS
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:
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:
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.
I have followed your code and produced this, which works perfectly:
Dim qt As QueryTable
Dim URL As String
URL = "SomeWebPage"
Set qt = Sheet1.QueryTables.Add(Connection:="URL;" & URL, Destination:=Sheet1.Range("A1"))
.RefreshOnFileOpen = True
.Name = "SomeName"
.WebFormatting = xlWebFormattingRTF
.WebSelectionType = xlSpecifiedTables
.WebTables = "1"
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?
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
Hope it helps!
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:
Thanks in advance for the assistance.
I haven't done this myself but it seems that many others have. You may find the following links useful:
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.
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:
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!
I hope that helps!