Project Management

Get up-to-the minute data with Excel's automatic Web queries

When your job depends on having the latest stats, it behooves you to find the most efficient way to receive this information. Discover how Excel 2002 and 2003's Web query feature allows your spreadsheet to automatically update the data from the Internet each time you open the file.

If your job depends on having the most current data off the Web, you can make sure this information is at your fingertips with Microsoft Excel 2002 and 2003's Web query feature.

For example, suppose you work in the sports industry and need to keep apprised of the latest team statistics. You can set up a Web query in a spreadsheet that will give you the latest team standings. Here's how:

  1. Open a blank worksheet and click in A1.
  2. Click Data | Import External Data | New Web Query. (Excel will prompt you for an Internet connection if you're not already connected.)
  3. In the Address box, enter the address of the Web page that contains the team statistics; for example, http://msn.foxsports.com/mlb/standings, and then click Go.
  4. Click the yellow boxes with arrows next to the blocks of text you want to import. (When clicked, the yellow boxes turn into green boxes with check marks.)
  5. Click the Import button.

To set the Web query to run every time the file is opened, follow these steps:

  1. Right-click a blank area of the toolbar and select External Data.
  2. Click the Data Range Properties button.
  3. In the Refresh Control section, select Refresh Data On File Open. Click OK.

Now the spreadsheet will automatically update the data from the Internet each time the file is opened. Sometimes you may wish to take a snapshot of the data at a given time, but copying and pasting the data to another worksheet will only create another Web query that will update the next time the file is opened. To create a copy of the data that will not change automatically, follow these steps:

  1. Select the data you want to copy. Go to Edit | Copy.
  2. Click on the cell where you want the snapshot of the data to appear.
  3. Go to Edit | Paste Special.
  4. Click the Values And Number Formats checkbox and then click OK.
  5. Go to Edit | Paste Special.
  6. Click the Column Widths checkbox and then click OK.

The next time your boss asks for the latest figures in your industry, you can impress her by pulling up your custom spreadsheet and providing her with the latest stats.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

0 comments

Editor's Picks