Microsoft Excel 2000/2002/2003/2007

Microsoft Windows

 

Web queries let you display current data in your worksheet directly from the Internet. But what if you aren’t sure what data you’ll need from a website? Instead of creating queries that pull data for any conceivable situation, you can make your web queries dynamic so that you decide the query’s parameters on the spot.

 

•     Learn how web queries work by studying a built-in web query.

•     Analyze a web query’s URL to learn its search parameters.

•     Build and save a custom dynamic web query that asks you for search criteria each time you run the query.

 

While you may be able to get by with static web queries, if you really want to deliver the goods with web data in Excel you’ll need to use dynamic queries. This feature lets users specify query parameters at the time that the query is sent to a web server.

Example: Instead of simply pulling the latest Microsoft stock data from the web, your dynamic query will allow you to grab data for any company’s stock on the fly. We’ll show you how to build dynamic queries that allow you to import web data into an Excel worksheet based on variable criteria.

 

Dynamic web queries in Excel prompt users for the criteria they’re looking for at the time that they execute the query. For instance, if you wanted to retrieve the names of people living in a specific ZIP code, the query would prompt you to fill in a ZIP code parameter.

Behind the scenes, there are two ways to pass parameters to a web server from Excel, using HTML’s GET and POST methods. However, you don’t really need to know any HTML to take advantage of dynamic web queries.

Web queries in Excel 2003 are extremely user-friendly. On the front end, you see the website as you might see it online. Excel splits it into sections using yellow arrow indicators and you can pick and choose what parts of the website you want to pull data from. But behind the scenes, each web query has a corresponding IQY file that holds HTML code dictating how the web query works. If you learn how to edit the IQY file, you can transform a static query into a dynamic one.

 

One of the best ways to get a feel for how dynamic queries work is to dissect an existing one. To demonstrate, let’s run one of Excel’s existing web queries and then edit the related IQY file.

 

Version differences: This article is written specifically for Excel 2003 and 2007, but you can still use the technique in Excel 2000 and 2002. However, some menu item names and dialog boxes may vary.

 

1.       Open a new workbook and choose Data | Import External Data | Import Data from the menu bar. (In 2007, go to the Data tab and select Get External Data, then select Existing Connections.)

2.      In the Select Data Source dialog box, select the MSN MoneyCentral Investor Stock Quotes.iqy file and click Open to display the Import Data dialog box.

3.      Choose a location for the retrieved data in your workbook and click OK to display the Enter Parameter Value dialog box, as shown in Figure A.

4. Enter Microsoft’s stock symbol, MSFT, in the text box and click OK to import the web data into Excel. 

A:

Dynamic queries prompt you for parameters when you run the query.

 

1.       Click in an unused worksheet cell.

2.      Choose Data | Import External Data | Import Data from the menu bar again.

3.      Right-click on the MSN MoneyCentral Investor Stock Quotes.iqy file and choose Edit With Notepad from the resulting shortcut me