Excel Add-In for Microsoft SharePoint

Build 24.0.8963

Querying Data

The add-in integrates with the Excel toolbar and ribbon, adding controls for querying and editing Microsoft SharePoint.

Building an SQL SELECT Query

After Establishing a Connection, select tables, columns, and filters -- as you make changes, the add-in generates the underlying SELECT query. Edit the query directly to execute joins, aggregations, or more complex SELECT Statements. See SOAP Data Model for more information on how the add-in models Microsoft SharePoint as a database, including any required columns to search or update data.

  1. If you have already gotten the data, click Edit Query... in the CData ribbon. Otherwise, click Get Data and select "From Microsoft SharePoint" connection/s from the CData ribbon to open the CData Query window or create a new connection prior to that.
  2. To change the table you wish to query, click the Select button.
  3. You can define filters, column aliases, and a limit on the records to return.

Querying Data with Cell References and Formulas

By Writing Parameterized Queries, you can reference cells to dynamically search Microsoft SharePoint. The following query filters on cell A2 of the sheet InputSheet:

SELECT * FROM Calendar WHERE Location = @InputSheet!A2

To execute parameterized queries in the Data Selection dialog, you need to edit the query directly.

Setting the Location of Results

In the Sheet Name box, enter the name of the worksheet that should contain the retrieved data. Additionally, in the Start Data at Row box enter the row where the query results should start.

Refreshing Data Manually

You can automatically refresh the data using the Refresh Worksheet button in the ribbon.

Refreshing Data Automatically

Select the Auto Refresh option to poll Microsoft SharePoint for changes over an interval specified in seconds. You can also refresh the spreadsheet on demand from the ribbon.

The Auto Refresh refreshes only one spreadsheet at a time. To activate the Auto Refresh on another spreadsheet the currently running spreadsheet must be paused.

Note that the refresh overwrites local changes -- if you have local changes, you are prompted to accept a warning before refreshing the sheet.

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.8963