Change a Web query

You can change the address of the Web page you're querying, change the selection of data returned from the Web page, and change formatting and other settings for your Web query (Web query: A query that retrieves data stored on your intranet or the Internet.) in the Edit Web Query dialog box. You can change how the query is refreshed and other properties of the data range returned by your query in the External Data Range Properties dialog box.

ShowChange address, selection of data, or formatting

  1. Click a cell in the region of the worksheet where your query data was returned (also known as an external data range (external data range: A range of data that is brought into a worksheet but that originates outside of Excel, such as in a database or text file. In Excel, you can format the data or use it in calculations as you would any other data.)).

If you're not sure which region of the worksheet contains your query data, but you know the name of the query, click the Name box (Name box: Box at left end of the formula bar that identifies the selected cell, chart item, or drawing object. To name a cell or range, type the name in the Name box and press ENTER. To move to and select a named cell, click its name in the Name box.) and select your query name from the list.

  1. Click Edit Query Button image on the External Data toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.).
  2. Do one or more of the following:

ShowChange the address for the Web page you want to query

ShowChange the selection of data returned from the Web page

Click Select Table to cancel the selection of a selected table or click Select a Table to select a table.

If there are no Select a Table next to tables on the page, click Show Icons Show/Hide icons at the top of the dialog box to display them.

ShowChange import and formatting settings

  1. Click Options.
  2. Do one of the following:

Under Formatting, click one of the following:

Under Import settings for preformatted <PRE> blocks, select one or more of the following:

  • Import <PRE> blocks into columns     Blocks of data surrounded by <PRE> tags will be imported into separate columns on the worksheet. Clear this box to return data into a single column.
  • Treat consecutive delimiters as one     If you have characters that define, or delimit, the text for each column in your <PRE> sections (such as commas), you can specify that when Excel encounters more than one of these delimiters together, they will be treated as one so that a blank column is not placed between each consecutive delimiter. This check box is available only when you select the Import <PRE> blocks into columns check box.
  • Use the same import settings for the entire section     Select to use your setting for Treat consecutive delimiters as one for all preformatted sections on the Web page. Clear this check box to use your setting for the first preformatted section only or if you want Excel to determine the best settings. This check box is available only when you select the Import <PRE> blocks into columns check box.

Under Other Import settings, select one or more of the following:

  • Disable date recognition     Ensures that numbers on a Web page that appear similar to dates appear as numbers on the worksheet. For example, a sports standing score of 03-07 is recognized by Excel as the date March 7 unless this option is selected.
  • Disable Web query redirections     Ensures that the Web query is not redirected to a different data source than what you see on the Web page you're querying. Select this check box for compatibility with queries created in previous versions of Excel.
  1. When you're done making changes in the Edit Web Query dialog box, click Import.

ShowChange refresh options and other properties

  1. Click a cell in the region of the worksheet where your query data was returned (also known as an external data range (external data range: A range of data that is brought into a worksheet but that originates outside of Excel, such as in a database or text file. In Excel, you can format the data or use it in calculations as you would any other data.)).
  2. Click Data Range Properties Button image on the External Data toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.).
  3. Under Query definition, make sure the Save query definition box is selected to allow future refreshes of the data and to have other options in the dialog box available. To prevent further refreshes of the data, clear the box.

The Save password option is not applicable to Web queries, so appears disabled.

  1. Under Refresh control, select one or more of the following:
    • Enable background refresh     Runs the query in the background so that you can continue to work in Microsoft Excel while the data is refreshing.
    • Refresh every minutes     Allows you to set specific intervals of time in minutes at which Excel automatically refreshes the data.
    • Refresh data on file open Automatically refreshes the data each time the file is opened.
    • Remove external data from worksheet before saving     Removes the external data, but not the query, from your worksheet before you save. The Refresh data on file open box must be selected for this option to be available.
  2. Under Data formatting and layout, select one or more of the following:
    • Adjust column width     Automatically adjusts column widths to fit refreshed data.
    • Preserve cell formatting     When the data is refreshed, cell formatting changes you made in Excel are preserved. This option is automatically cleared, however, when you select Full HTML Formatting in the Options dialog box (Edit Query dialog box).
    • If the number of rows in the data range changes upon refresh     Specifies how to handle a smaller or larger data set returned to Excel when data is refreshed. You cannot select or clear this option; rather you must select one of three options in the dialog box.
    • Fill down formulas in columns adjacent to data     Copies formulas in columns to the right of the external data range to new rows of data added when the data is refreshed.

Note Include field names, Include row numbers, and Preserve column sort/filter/layout are not applicable to Web queries, and are disabled in the dialog box.

 
 
Applies to:
Excel 2003