Connecting to Excel

AQT can be used to access Excel files, which gives you an alternate way to read Excel data. With a "database-style" interface into Excel, you can build searches, sorts and summaries in way that (arguably) is easier than in Excel.

For more on the use of Excel with AQT, see Database-specific notes - Excel.

Prerequisite Software

No software is needed in order to access an Excel file (xls file). You access the Excel file using the Microsoft ODBC Driver for Excel; this is present in the standard Windows install.

You do not need Microsoft Excel on your machine in order to access an Excel file with AQT.

Accessing xlsx, xlsm, xlsb files

The ODBC Driver that comes standard on your Windows PC will only be able to access xls files.

If you wish to open xls, xlsm or xlsn files you will need to install a more modern ODBC Driver. This can be downloaded from:

https://www.microsoft.com/en-us/download/details.aspx?id=54920

Signing onto an Excel file

You have three methods of signing accessing an Excel file:

• setting up an ODBC Datasource for an Excel file
• setting up / using a Generic Datasource
• open the xls file with AQT

The first method is useful if you are accessing a particular Excel file on a frequent basis.

Setting up an ODBC Driver for your Excel File

This sets up a datasource which you can use for accessing any Excel file.

The general procedure for this is descibed in Configuring a Database Connection.

• select a Driver of Microsoft Excel Driver
• you select your xls file by clicking on the Select Workbook button (in the Database frame).

Once you have done this, the ODBC Datasource name for your Excel file will appear in the list of databases in the AQT signon window (make sure you have select Show All (not Show Recent)).

Setting up and using a Generic Datasource

To set this up, follow the steps in the previous section, however do not select a workbook (leave this blank). It is recommended that you give this Datasource a name such as Excel Files.

To use this generic datasource:

• from the AQT sign-on window, sign onto the Excel Files datasource.
• you will be prompted with the name of the xls file to use. Select this.

Open the xls file with AQT

To do this, right-click the xls file, select Open With > Choose Program. Click on Browse to select AQT. You can check Always use the selected program to open this kind of file if you want to always open xls files with AQT.

Read-only Mode

By default, when you set up an Excel datasource, it will be in read-only mode. You will not be able to update any of the Excel data. If you wish to update your data, you need to switch off the Read Only flag when you configure the datasource. This is done by clicking on Options and de-selecting Read Only.

When you use the Open the xls file with AQT method of connecting, the connection will be in non readonly mode (so you can update data).