Change a Web query

Microsoft Office Excel 2003

  1. Click a cell in the region of the worksheet where your query data was returned (also known as an external data range).

    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 and select your query name from the list.

  2. Click Edit Query Button image on the External Data toolbar.

  3. Do one or more of the following:

    ShowChange the address for the Web page you want to query

    Select or type a new URL in the Address box.

    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:

      • None    Returns none of the formatting on the Web page you are querying, only text. Formatting that exists on the worksheet is applied.

      • Rich text formatting only    Returns the type of formatting that Microsoft Excel can reproduce most closely, such as font styles. Does not include HTML formatting, such as hyperlink formatting.

      • Full HTML formatting    Returns all HTML formatting that Excel supports, such as hyperlink formatting. This option clears the Preserve cell formatting check box in the External Data Range Properties dialog box.

      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.

  4. 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).

  2. Click Data Range Properties Button image on the External Data toolbar.

  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.

  4. 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 x 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.

  5. 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.