XL98: Opening or Saving Earlier Version Files with External Data

• Microsoft Excel 98 Macintosh Edition

SUMMARY

The "More Information" section in this article contains information about migrating from an earlier version of Microsoft Excel or saving to an earlier version of Excel when the file contains a saved query that retrieves external data.

MORE INFORMATION

Forward Compatibility

When a Microsoft Excel 5.0 workbook that contains external data is opened in Microsoft Excel 98 Macintosh Edition, the query ranges are converted to QueryTables, and an unhidden defined name that references the external data range is created.

NOTE: The data source name (DSN) connection information is also preserved with the workbook so that the query range can be saved in the Microsoft Excel 5.0 file format.

When you refresh or update external data, Microsoft Excel may be unable to locate the data source. This behavior may occur when you open an earlier version of a file that contains a data source name, and the data source name is not found on the computer. This problem may also occur when the appropriate driver that is referenced in the data source information is not found. When this happens, an error message similar to the following appears:

   [INTERSOLV][Macintosh ODBC Driver Manager]
   Data source name not found and no default
   driver specified

   ARTICLE-ID: Q179124
   TITLE     : XL98: "INTERSOLV" Error Refreshing or Editing Saved Query

Backwards Compatibility

When you save a Excel 98 file that contains a saved query that uses external data in the Excel 5.0 file format, all of the external data ranges (QueryTables) are converted to Excel 5.0 query ranges that are compatible with the Microsoft Query add-in, Xlquery.xla. The query range definitions are stored as a series of hidden, local, sheet names. The following table lists the defined names that are created when this conversion occurs.

NOTE: The defined names that are created depend on the ODBC driver that you are using. Not all the defined names in the table are created when the workbook is saved in the earlier version format of Excel.

   Hidden name               Description
   ------------------------------------------------------------------------

   Query1.query_range        The cell range that the query table definition
                             covers (as a range).

   Query1.query_source       An array of strings that contain the Data
                             Source Name. This value is not in a connection
                             string format; instead, it is in the DSN name
                             as the DSN appears in the ODBC control panel,
                             for example, "Excel ODBCobjects:Raid4."

   Query1.query_connection   An array of string that contains the complete
                             ODBC connection string in the connection
                             string syntax, for example,
                             "DSN=BigIron:DATA001;UID=Edison;APP=Microsoft
                             Query;WSID=USERX;Language=us_english;
                             DATABASE=ODBC2."

   Query1.query_definition   The Structured Query Language (SQL) statement
                             for the query as an array of strings with
                             Microsoft Query syntax additions, including
                             items such as column title aliases.

   Query1.query_options      A two element array of two rows by one column
                             that contains Boolean operators. The first row
                             is the setting for the Include Field Names
                             check box in the Query Options dialog box. The
                             second row is the setting for the Include Row
                             Numbers check box.

   Query1.query_statement    Similar to query_definition except that
                             query_statement is raw SQL text with no
                             Microsoft Query-specific items, for example
                             no column title definitions are included.

   Query1.user_name          The user's name that is used to log on to
                             ODBC. The name is stored as a string value
                             not as an array of strings. This value is
                             duplicated in the query_connection string.

   Query1.keep_password      A single Boolean value (not an array)
                             that indicates whether the password is to be
                             retained for use when refreshing or editing
                             the query range. If TRUE, the password
                             connection string record is stored in
                             the query_connection name. If FALSE, the
                             password connection string record is stripped
                             from the query_connection name.