Excel Files as Data Sources


The name of the driver will usually be:
Microsoft Excel Driver (*.xls)

Please note that the file extension information at the end is actually part of the driver name.

Either a single Excel workbook or a directory may be specified in the DBQ. If only a directory is specified in the DBQ then the Excel file must be specified as the prefix of the table name in the query. The individual tables may be specified in any of three ways.

  1. A named range may be defined in the work book and referenced
  2. An entire worksheet may be specified using the nomenclature Sheet1$. This must be quoted for SQL with square brackets, therefore the final name is [Sheet1$]
  3. If a workbook contains only one worksheet and neither of the above methods are used, Excel will create a range automatically called "TABLE". This is fairly haphazard.

Although using a DSN is recommended, a typical configuration without one would be:

<query> <driver>Microsoft Excel Driver (*.xls)</driver> <dbq>C:\MyFiles\Book1.xls</dbq> <sql>Select * From Names Order By X</sql> <query>

Or

<query> <driver>Microsoft Excel Driver (*.xls)</driver> <dbq>C:\MyFiles</dbq> <sql>Select * From Book1.Names Order By X</sql> <query>