Excel Web Query based off Cell Contents?

[str8sNUTZ]

So I've got excel setup to perfom web queries on a number of different websites. No problem there. However, what I'd like to do is to have a webquery (say in WorkSheet_3) pull the website to query off of WorkSheet_2 cell D1.

So the result would be:

WorkSheet_2 performs a webquery on a site that contains links to other pages, I'd have excel spit out the web address in column D

WorkSheet_3 through WorkSheet_15 (for example), would perform a web query based off of the corresponding website in WorkSheet_2

Example:
WorkSheet_2 performs Web Query on Covers.com matchups for NFL (covers.com/sports/nfl/football-matchups.aspx) and in cell D1 the result would be the 1st link found on that page for trends (covers.com/pageloader/pageLoader.aspx?page=/data/nfl/matchups/g1_trends_1.html)

Now Worksheet_3 would perform a webquery using WorkSheet_2 Cell D1 as the website.

Hope that makes sense. I've researched Dynamic Web Query and cannot seem to find any information on getting Excel to do what I want. Any help would be appeciated. Thanks.

[matekus]

str8sNUTZ,

1. In Excel, using first web query capture target url minus leading "http://" in Sheet1!$H$3 (e.g. www.google.com/webhp?hl=en).
2. In text editor, create text file ParamQry.iqy:

WEB
1
http://["Web Address"]

Selection=EntirePage
Formatting=All
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=True
DisableDateRecognition=True
DisableRedirections=True

3. In Excel, on Sheet2 select Data ¦ Connections ¦ Add ¦ Browse for More and choose data source ParamQry.iqy from folder. Select Properties ¦ Definition ¦ Parameters and enter location of first web query url (e.g. =Sheet1!$H$3) in "Get the value from the following cell" entry field. Select "Refresh automatically when cell value changes". Enter OK twice and Close.

4. In Excel, on Sheet2 select Data ¦ Existing Connections ¦ Open connection created in step 3 above. Select Properties and change, as required. Enter OK twice and newly created query will automatically refresh.

matekus

[TomG]

I was just gonna say, "Google search 'dynamtic web query'".

Your excellent response deserves SBR points even though it wasn't even my Q.

[matekus]

Thanks TomG.

Much appreciated.

matekus

[str8sNUTZ]

matekus,

Freaking AWESOME explanation. I've read through numerous tutorials on Dynamic Web Queries, and your simple short explanation explains it so much better than anything I've seen. Thanks a ton. Sent some points.

[matekus]

str8sNUTZ,

Thanks for the kind words and the points.

matekus

[str8sNUTZ]

matekus, ran into a problem, i've followed the instructions step by step, and I'm not getting any data on Sheet2. I thought it might be an issue with the way that I'm pulling the URL from the original query, and then stripping off the http:// but I hand typed a URL (with no http://) and it still isn't pulling data. I've attached the spreadsheet. I'm pulling the URL for the query in Sheet2 from Sheet1 H53.

Just trying to get this to work on a basic spreadsheet first. Thanks for any help you can provide.

[str8sNUTZ]

Maybe I was supposed to do this in the first place, but I went into Sheet2 A2 and clicked "Existing Connection" and picked the .iqy file i created. I hit "Refresh All" and it pulled data in, but for some reason it is pulling in an error page from Covers.com. I copied the URL from Worksheet 1 and pasted it into IE and it takes me to the correct page, I checked the cell reference in the .iqy file and it is correct. Not sure why the query is pulling in an error page.

Also, on the above attache worksheet I screwed up the formula to remove the http:// from the URL, and have sense fixed it by using: =RIGHT(G53,LEN(G53)-7)

Not sure where I'm screwing up here. Thanks for your help and your patience.

[matekus]

NFL Matchups

str8sNUTZ

Apologies for the late response.

Have a look at the attached spreadsheet and let me know if it works.

matekus

[str8sNUTZ]

matekus

It does exactly what I was trying to do. Question, what exactly is the formula in StaticWebQueryH53 doing? And why didn't the formula I used to extract the URL (showaddress(cell)) and then to remove the http:// work?

Last question, is it possible to setup multiple queries on the same worksheet using the dynamic webquery?

[rise]

great stuff thanks iam learning alot here.

[matekus]

ASP.NET Web Application Framework

str8sNUTZ,

First, in simple terms, ASP.NET (.aspx) is a server-based web application framework for dynamically generating web pages at run-time, whereas HTML files (.htm and .html) are simply rendered on the client. This makes aspx files trickier to work with so a certain amount of trial-and-error is required before you get a working dynamic web query. Also, I strongly advise that you carefully read the "Terms of Use" for any web site that you wish to access via excel web queries to ensure compliance.

Second, though multiple queries on the same worksheet are possible, I would encourage you to use only one query per sheet to avoid confusion.

matekus

[str8sNUTZ]

Thanks for all the help Matekus.

Not real sure what you are referring to when mentioning ASP.NET (.aspx) as I'm not sure where that comes into play here. But I greatly appreciate your help.

I ended up changing the fomula in H53 to:

=LEFT(RIGHT(ShowAddress(F53),7),2) this outputs the preceding 2 characters before .html

then in cell I53:

=IF(LEFT(J55,1)="_",LEFT(RIGHT(ShowAddre ss(F55),6),1),LEFT(RIGHT(ShowAddress(F55 ),7),2)) which gets rid of the preceding "_" for single digits

Only reason I made your one simpler formula into 2 was so that I understand it. As I did some googling to try and come up with some answers on your formula and still don't have a clue.

Thanks again.

[Flying Dutchman]

Good stuff!

[str8sNUTZ]

I get the .aspx reference now, duh. The matchups page on Covers.com is .aspx not .html guess I should have opened my eyes first.

So the end result is that due to the .aspx webpage you have to setup the web query with parameters, in this case the trailing 2 digits in the URL. So you have to extract those digits to get the webquery to work properly.

That sound about right?

[Miz]

Awesome thread! Thanks!

[jane2geo]

That is a terrific expiation. I tried and failed using Dynamic Web Queries over a year ago. I got my sheet working using this add-in. http://finance.groups.yahoo.com/group/smf_addin/ It allows you to fill a cell with any single piece of information on a web page. I believe this should become part of everyone’s arsenal, as some sites are now blocking web quires.

[oddsfellow]

jane2geo,

that is an excellent add-in. Is there a way to use it on password protected sites?

[jane2geo]

A quote from, Randy Harmelink creator of the add-in ""I use web queries to grab data from password-protected sites all the time. The key is to login to the web site first. That creates a login cookie for later queries to use (provided that is how security is handled on the site). You can go through a manual web query process, but don't need to actually import anything, but just go through the login process. In most cases, you can alternatively use IE to login to the web site first -- that usually creates the login cookie as well. For some sites, I've only had to do that once. Other times, I need to do it once per EXCEL session. It depends on when the cookie expires.""

[oddsfellow]

Thanks for your help Jane. Really is a useful little tool.

[jairocon]

Will definitely give the plugin a try as I'm having a hard time with excel queries.