This section describes how to use the driver with two Excel utilities, the Data Connection Wizard and Microsoft Query. If you are interested in using data from Microsoft Excel Online in Power Pivot, see the section: From PowerPivot.

macOS Configuration

On macOS, you need to set the AnsiCodePage configuration setting in the cdata.odbc.excelonline.ini file, which is located in the folder containing the driver library, the lib subfolder under /Applications/CData ODBC Driver for ExcelOnline. Add the AnsiCodePage property in the Driver section.

You need to set the value of this property to the code page of your machine's current system locale. Here are several examples:

Configuring OAuth

On macOS, Excel may be unable to initialize the OAuth process to retrieve an Access Token. When authenticating to Microsoft Excel Online with OAuth, you may need to test the connection and authorize access to your data in the ODBC Manager, or another application, prior to connecting with your DSN in Excel.

Data Connection Wizard

Select From Other Sources > From Data Connection Wizard in the Data tab. Select ODBC DSN as your chosen data source type and click Next. Here, you can view all of the ODBC data sources on your machine. See "DSN Configuration" to configure a new ODBC data source if the CData Microsoft Excel Online data source is not listed. Once you select the data source, you will be presented with a list of tables. On this page, you can select the table and then import the data.

Microsoft Query in Windows

Select From Other Sources > From Microsoft Query from the Data tab. Here, you can view all of the ODBC data sources on your machine. See "DSN Configuration" to configure an ODBC data source if you do not see the CData Microsoft Excel Online data source. Once you select the correct data source, you will be guided through the following windows:

• Choose Columns: Preview the data in any column and select the columns you want to include.
• Filter Data: Add conditions that will filter data based on your criteria.
• Sort Order: Select how the data should be sorted.

Microsoft Query in macOS

Select New Database Query > From Database from the Data tab. Here, you can view all of the ODBC data sources on your machine. See "DSN Configuration" to configure an ODBC data source if you do not see the CData Microsoft Excel Online data source.

Once you select the correct data source, you will need to build your query in the SQL Statement section. Clicking the table names will add the select statement that retrieves all data from that table. You will now have an external data connection.

Refreshing Data

Both the Data Connection Wizard and Microsoft Query create an external data connection in Excel. External data connections have options that control refreshing the data; you can control these settings from the WorkBook Connections window under the Data tab. There are options available to refresh the data each time the file is opened, or periodically at a specified interval.