Web pages often contain information needed for analysis in Microsoft Office Excel. For example, in MS Excel you can analyze stock quotes using data that comes directly from a web page, or a sales table from an organization’s private webpage.
If necessary, you can extract refreshable data (in which case it can be updated directly in MS Excel to reflect the latest changes on the web page) or static data from a web page to store on a worksheet.
Using a Web Query lets you obtain data from a web page—such as a single table, several tables, or all text—and analyze it with Excel’s tools.
To create a web query, do the following:
- Place the file Data.htm on the D: drive of your computer.
- Open a new workbook, go to the Data tab on the ribbon, and in the Get External Data group choose From Web.
- In the New Web Query window, in the address field enter a link to the file you placed: …/Data.htm.
NOTE
The Address field is for entering the URL of the required web page. For example, if Internet Information Services (IIS) is installed on your computer (described later), its root directory is C:\Inetpub\wwwroot. You can place in this directory, for instance, a file with the .asp extension (a web page that also contains data). In that case, you should enter the following link in the Address field:
http://localhost/filename.asp.
You must not specify a link in the form C:\Inetpub\wwwroot\filename.asp in this window.
- Using the arrow buttons, select the desired table on the web page.
- Click Import.
- In the Import Data window, set Where do you want to put the data? to Existing worksheet, and in the corresponding field enter the cell reference (for example, A1) where the upper-left corner of the imported table will be placed.
- Click Properties to specify query parameters.
- In the External Data Range Properties window, the Name field sets the query name. The Query definition group is used to save the password and the query definition. The Refresh control group specifies how the imported data will be refreshed. The Formatting and layout group sets formatting parameters. The If the number of rows in the range changes group specifies how to handle changes in the size of the range. The Fill down formulas in columns adjacent to data checkbox determines whether explanatory formulas should be filled along with the data. Leave all default settings in the External Data Range Properties window and click OK.
- The External Data Range Properties window will close. Click OK in the Import Data window.
- The data from the selected table on the web page will be imported to the worksheet.
