Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 996
  • Last Modified:

Excel QueryTable from WebTable

From:
http://msdn.microsoft.com/en-us/library/office/aa203721(v=office.11).aspx#odc_xlwebquery_retrievingdatafromurls

"You can copy the third line from any Web query [.iqy] file and use it as the URL."

But I can't find any .iqy files on my computer after creating a Get External Data / From Web query that gets a table.

I'm trying to construct vba code that will do the same thing exactly when run.

If I copy the URL from the Connection that's created then instead of getting the table intended, it appears to grab stuff from the entire page.  

e.g. https://finance.yahoo.com/q?s=aapl&uhb=uhb2&type=2button&fr=uh3_finance_vert_gs
The table that's grabbed with the Get External Data connection is the table of 7 items in the mid-left part of the page starting with Prev. Close.
0
Fred Marshall
Asked:
Fred Marshall
  • 5
  • 5
5 Solutions
 
QlemoC++ DeveloperCommented:
Instead of trying to do all manually, start with inserting a Web Query result manually, including marking the correct table, and then read the properties of the QueryTable via VBA debugger.
The case you describe results from not having selected any specific table in the HTML stream, so the whole page is used.
0
 
Fred MarshallAuthor Commented:
Qlemo: That sounds like a good idea but I've already tried that.  And just confirmed with a blank spreadsheet by adding an External Source Web connection.  There is *no* vba generated as a result.
Also, I followed verbatim the instructions for vba code on the cited msdn link that I originally posted.  The result was supposed to be the MSFT stock quote with 3 items only.  Instead, doing that results in a similar outcome, I get tables from the page instead of the intended table.  
Now, I believe that some of that was due to the fact that the URL had changed, etc. but it remains an obscure connection between those query structures and the vba code I need.
0
 
Fred MarshallAuthor Commented:
In reading about this and seeing how the External Data Web process works, there's the matter of selecting a table.  And, I believe, it's a matter of selecting the table number in the order they appear.  But, I don't see anything in the vba code that suggests a "table number" selection.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
QlemoC++ DeveloperCommented:
Of course not. You have to write up VBA yourself. But finding out which table to select (and setting the WebTables attribute of QueryTable accordingly) can be difficult in VBA.

First thing to try is to do it manually, as said above. As soon as you have your data in a suitable format, you can try to build the Web Query via VBA. Framework code is
Dim qt as QueryTable
set qt = ActiveSheet.QueryTables.Add(Connection:="URL;http://whatsoever.com", Destination:=[A1])
With qt
    ' First HTML table
    .WebTables = 1
    .WebSelectionType = xlSpecifiedTables
    ' Use this instead if there is no table - will insert complete page
    ' .WebSelectionType = xlEntirePage
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .AdjustColumnWidth = False
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = False
    .SaveData = False
    .BackgroundQuery = False
    .RefreshStyle = xlOverwriteCells
    .Refresh
End With

Open in new window

0
 
QlemoC++ DeveloperCommented:
If you have set up the Web Query manually, use
? ActiveSheet.QueryTables.Item(1).WebTables

Open in new window

in VBA Direct WIndow to get the number.
0
 
Fred MarshallAuthor Commented:
Thanks I'll try it.
0
 
Fred MarshallAuthor Commented:
OK.  So now I'm looking at the code you offered and I see something that I've certainly seen before but now want to understand better:
The "With" is followed by a series of things that start with "dot"; like
.webtables = 1

Open in new window

What is the vba language terminology for a list like this?
I imagine that all of these are objects of the QueryTable but where do you find all these things and what they mean?  Another example:
.RefreshStyle = xlOverwriteCells

Open in new window

I have a couple of good books on vba (which are terrible re: QueryTables) and I can access the MSDN but this level of detail escapes any documentation I've found.  So a pointer or two in that direction would be very helpful.
*****
OK.  So now I'm getting some success by modifying the .WebTables value with 1,2,3,4, etc. and am able to get the data I'm looking for.
THANKS!
But, it appears that each time the worksheet is updated using this vba code, a *new* connection is created.  That seems a very bad idea so how might I prevent that?
0
 
QlemoC++ DeveloperCommented:
Yes, everything between With and End With and starting with just a dot refers to the (static!) expression following With. I don't know how it is called officially, but would name it with block.

.RefreshStyle is a property (or attribute in some languages), no object. A property can refer to a single or a list of objects (collation). A method like .Refresh can have objects as result too, of course.

Where do I find details about objects and their properties? Usually I just watch them in the VBA debugger, and/or look up properties, events and methods in Object Catalog (F2), pressing F1 if I need detailed help.
Most of my Web Table knowledge has been found with and for http://www.experts-exchange.com/A_3887-Getting-your-EE-Ranking-statistics-in-Excel-The-Next-Generation.html, btw.
0
 
QlemoC++ DeveloperCommented:
New connections are created if you use the QueryTables.Add method, as I suggested. I usually destroy the created QT when finished with qt.Delete, if the single object is still available, or with a loop if not:
  Dim qt As QueryTable
  For Each qt In ActiveSheet.QueryTables
    qt.Delete
  Next qt

Open in new window

The latter performs perfect cleanup, but might be too much if you need life tables (but I don't think that is the case here).
0
 
Fred MarshallAuthor Commented:
Thanks!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now