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.
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.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks I'll try it.
ASKER
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
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:
*****
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?
The "With" is followed by a series of things that start with "dot"; like
.webtables = 1I 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*****
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks!
The case you describe results from not having selected any specific table in the HTML stream, so the whole page is used.