• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1144
  • 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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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 MarshallPrincipalAuthor 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 MarshallPrincipalAuthor 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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 MarshallPrincipalAuthor Commented:
Thanks I'll try it.
0
 
Fred MarshallPrincipalAuthor 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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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 MarshallPrincipalAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

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