Initializing the Microsoft Excel Driver

Microsoft Office Access 2003

Initializing the Microsoft Excel Driver

When you install the Microsoft® Excel driver, the Setup program writes a set of default values to the Microsoft Windows® Registry in the Engines and ISAM Formats subkeys. You should not modify these settings directly; use the setup program for your application to add, remove, or change these settings. The following sections describe initialization and ISAM Format settings for the Microsoft Excel database driver.

Microsoft Excel Initialization Settings

The Jet\4.0\Engines\Excel folder includes initialization settings for the msexcl40.dll driver, used for external access to Microsoft Excel worksheets. Typical settings for the entries in this folder are shown in the following example.

win32=<path>\ msexcl40.dll

TypeGuessRows=8

ImportMixedTypes=Text

AppendBlankRows=1

FirstRowHasNames=Yes

The Microsoft Jet database engine uses the Excel folder entries as follows.

Entry Description
win32 The location of msexcl40.dll. The full path is determined at the time of installation.
TypeGuessRows The number of rows to be checked for the data type. The data type is determined given the maximum number of kinds of data found. If there is a tie, the data type is determined in the following order: Number, Currency, Date, Text, Boolean. If data is encountered that does not match the data type guessed for the column, it is returned as a Null value. On import, if a column has mixed data types, the entire column will be cast according to the ImportMixedTypes setting.

The default number of rows to be checked is 8.

ImportMixedTypes Can be set to MajorityType or Text. If set to MajorityType, columns of mixed data types will be cast to the predominate data type on import. If set to Text, columns of mixed data types will be cast to Text on import. The default is Text.
AppendBlankRows The number of blank rows to be appended to the end of a Version 3.5 or Version 4.0 worksheet before new data is added. For example, if AppendBlankRows is set to 4, Microsoft Jet will append 4 blank rows to the end of the worksheet before appending rows that contain data. Integer values for this setting can range from 0 to 16; the default is 01 (one additional row appended).
FirstRowHasNames A binary value that indicates whether the first row of the table contains column names. A value of 01 indicates that, during import, column names are taken from the first row. A value of 00 indicates no column names in the first row; column names appear as F1, F2, F3, and so on. The default is 01.
Microsoft Excel ISAM Formats

The Jet\4.0\ISAM Formats\Excel 3.0 folder contains the following entries.



Entry name

Type


Value
Engine REG_SZ Excel
ExportFilter REG_SZ Microsoft Excel 3 (*.xls)
CanLink REG_BINARY 01
OneTablePerFile REG_BINARY 00
IsamType REG_DWORD 1
IndexDialog REG_BINARY 00
CreateDBOnExport REG_BINARY 01
ResultTextExport REG_SZ Export data from the current database into a Microsoft Excel 3.0 file. This process will overwrite the data if exported to an existing file.
SupportsLongNames REG_BINARY 01

The Jet\4.0\ISAM Formats\Excel 4.0 folder contains the following entries.



Entry name

Type


Value
Engine REG_SZ Excel
ExportFilter REG_SZ Microsoft Excel 4 (*.xls)
CanLink REG_BINARY 01
OneTablePerFile REG_BINARY 00
IsamType REG_DWORD 1
IndexDialog REG_BINARY 00
CreateDBOnExport REG_BINARY 01
ResultTextExport REG_SZ Export data from the current database into a Microsoft Excel 4.0 file. This process will overwrite the data if exported to an existing file.
SupportsLongNames REG_BINARY 01

The Jet\4.0\ISAM Formats\Excel 5.0 folder contains the following entries, which apply to Microsoft Excel versions 5.0 and 7.0.



Entry name

Type


Value
Engine REG_SZ Excel
ExportFilter REG_SZ Microsoft Excel 5-7 (*.xls)
ImportFilter REG_SZ Microsoft Excel (*.xls)
CanLink REG_BINARY 01
OneTablePerFile REG_BINARY 00
IsamType REG_DWORD 1
IndexDialog REG_BINARY 00
CreateDBOnExport REG_BINARY 01
ResultTextImport REG_SZ Import data from the external file into the current database. Changing data in the current database will not change data in the external file.
ResultTextLink REG_SZ Create a table in the current database that is linked to the external file. Changing data in the current database will change data in the external file.
ResultTextExport REG_SZ Export data from the current database into a Microsoft Excel 5.0 file. This process will overwrite the data if exported to an existing file.
SupportsLongNames REG_BINARY 01

The Jet\4.0\ISAM Formats\Excel 8.0 folder contains the following entries, which apply to Microsoft Excel 97.



Entry name

Type


Value
Engine REG_SZ Excel
ExportFilter REG_SZ Microsoft Excel 97-2000 (*.xls)
CanLink REG_BINARY 01
OneTablePerFile REG_BINARY 00
IsamType REG_DWORD 1
IndexDialog REG_BINARY 00
CreateDBOnExport REG_BINARY 01
ResultTextExport REG_SZ Export data from the current database into a Microsoft Excel 97 file. This process will overwrite the data if exported to an existing file.
SupportsLongNames REG_BINARY 01

Note  When you change Windows Registry settings, you must exit and then restart the database engine for the new settings to take effect.

See Also

Customizing Windows Registry Settings for Microsoft Jet