Search

Excel for the web

Excel for the web

This page outlines the steps to connect Microsoft Excel for the web to CData Connect.

Install the Add-In

You can install the Excel for the web add-in from the CData Connect dashboard or from Excel for the web.

From the CData Connect Dashboard

  1. Open the Client Tools page of the CData Connect dashboard.

  2. Locate the Excel for the web tile or use the search bar.

  3. Click Get Started. The Excel for the web add-in page opens.

  4. Click Get it now. If you are prompted, sign into your Microsoft account.

  5. On the Launch page, click try using Office Online.

    The Excel for the web page opens.

  6. Click Open in Excel Online.

  7. Authorize the add-in if you are prompted to do so.

  8. In Excel, click Add-ins on the Home tab of the navigation ribbon. The My Add-ins window opens.

  9. Continue to the steps in Connect to CData Connect.

From Microsoft Excel for the web

  1. Open Microsoft Excel for the web to a new or existing workbook.

  2. Click the File tab on the navigation ribbon.

  3. Click Get Add-ins. In the list of add-ins that appear, click More Add-ins.

  4. Search for CData in the window. In the search results, click Add next to CData Connect.

  5. Click Continue to agree to the license terms. An authorization dialog opens in your Excel workbook.

  6. Click Authorize.

  7. Enter your credentials and click Continue in the dialog.

  8. Continue to the steps in Connect to CData Connect.

Connect to CData Connect

After you install the Excel for the web add-in, follow these steps to connect to your CData Connect account:

  1. Open the Data tab on the Excel for the web navigation ribbon and click CData Connect.

    The CData add-in pane appears to the right of your Excel workbook.

Set Up Connection

If you do not yet have a data connection to CData Connect, you need to set one up.

  1. In the add-in pane, click Setup Connection. Allow CData Connect to display a new window.

    The new window displays the Add Connection screen for CData Connect.

  2. Select the connector for the data you wish to connect to Excel for the web.

  3. Enter your data connection settings. Save and test your connection.

    After you have a successful connection, you can Import Data.

Import Data

To import data from CData Connect to Excel for the web, follow these steps:

  1. Click Import.

  2. Choose a connection from the drop-down menu.

    You can now query the data you want to import.

  3. Select Query Builder to build the query in steps or select Custom SQL to enter a query manually. Follow the relevant instructions below.

Query Builder

  1. If using the Query Builder, choose a table from the drop-down list.

    A list of columns and column metadata for the table appears. By default, all columns are selected for inclusion in the query.

  2. (Optional) Deselect the unnecessary columns from your query.

  3. (Optional) Select one or more filters for your query.

  4. (Optional) Select one or more columns to sort by.

  5. Select Set Limit to set a limit of the number of results returned.

  6. Review the Generated query for accuracy.

  7. Click Execute to run your query. Excel imports the data that matches your query into the current sheet.

Custom SQL

  1. If using custom SQL, enter your SQL statement in the text box.

  2. Click Execute to run your query. Excel imports the data that matches your query into the current sheet.

Write Parameterized Queries

You can create dynamic spreadsheets by referencing cells in your custom SQL statement. The Excel for the web add-in executes the statement as a parameterized query. As you change the values in the sheet, the Excel for the web add-in executes a new query.

Create Searchable Spreadsheets

Use a parameterized SELECT WHERE query to create a spreadsheet that dynamically filters the data as you edit cells. The following filter criteria references the @InputSheet!A2 parameter, which is cell A2 of the sheet InputSheet. The @ prefix indicates that Industry is a required parameter.

SELECT BillingState, Name FROM Account WHERE Industry = @InputSheet!A2

Refresh Data

Once your data is imported, you can set it to periodically refresh automatically. You can also perform a manual update. The automatic refresh overwrites any uncommitted data. If you are only reading data, the automatic update is not an issue. If you have enabled auto refresh and are writing data, commit your changes often.

You must have the Excel workbook open in the browser in order for the automatic refresh to work properly. In addition, the automatic refresh will not execute if the workbook is idle for over 15 minutes.

  1. On the CData Connect screen, select Refresh.

  2. Select the data sheet or sheets to refresh.

  3. To manually refresh, click Refresh Now. To set up automatic updates, click Auto Refresh.

Note that the refresh interval is set in hours, and one hour is the minimum interval between refreshes.