Excel Teams



Enable the Azure DevOps or Team Foundation Add-in From the Excel File menu, choose Options. Choose Add-ins and from the Manage picklist, choose COM Add-ins, and then choose Go. Make sure that a check is placed in the Team Foundation Add-in checkbox. You should now see the Team ribbon. The Excel worksheet you open from Teams has the same experience as Excel for the web. You can think of it as an integration of Excel for the web into Teams. We are excited to announce the general availability of the Edit in Excel feature in Dataverse for Teams. This Dataverse feature is now available in Dataverse for Teams as an option from the ellipse menu in the Table designer. This feature enables customers to move data both into Excel and edit it but also to add or change data and publish it back into Dataverse for Teams. Microsoft Excel and Microsoft Teams Integration Send instant messages to users or channels in Microsoft Teams. Create new rows, search and update them in Microsoft Excel. Do much more by connecting Microsoft Excel and Microsoft Teams.

We are excited to announce the general availability of the Edit in Excel feature in Dataverse for Teams. This Dataverse feature is now available in Dataverse for Teams as an option from the ellipse menu in the Table designer. This feature enables customers to move data both into Excel and edit it but also to add or change data and publish it back into Dataverse for Teams. Adding data into Dataverse for Teams is one of the more important and frequent feature requests we’ve gotten. You can use the Edit in Excel feature to do this.

Plugin

Download your Excel file for your Dataverse for Teams database.

First you will need to get the Excel spreadsheet that will allow you to add your data.

  • From the Build tab select Tables, and then select your table.
  • Expand the ellipses menu to access the “Edit in Excel” feature.
  • Select “Edit in Excel.” This will download an Excel file into your Download folder.
Excel Teams

Importing data into Dataverse with Edit in Excel.

  • Open the downloaded Excel file.
  • Choose to Enable Editing when you open the Excel file. This will allow the Edit in Excel add-in to retrieve and publish data to and from Dataverse for Teams.
  • View your data. Opened, you can now see the data in your Dataverse for Teams database. In this example, I already have 4 records.
Excel Teams
  • Add new data. There are two ways to add new data.
    1. Single records. Choose the “New” menu in the Edit in Excel pane on the right and a new row will be added to the table in Excel.
    2. Multiple records. The database table is represented in an Excel Table. You can add multiple records into Excel the way you normally add new records to an Excel table with a Paste operation.
Excel teams tabExcel teams vba

Paste immediately below the table and it will add new rows to the Excel Table. In the example below, I have added five new records by pasting names into the Name field.

Choose “Publish” from the Edit in Excel pane on the right. Once published, notice that fields like “Status” and “Created On” are automatically updated.

Excel Teams Vba

Choose “Refresh” from the Edit and Excel pane on the right. Once refreshed notice that additional fields are updated like “Owner (Looup)”, and “Created By (Lookup)”. Also note that the data is sorted differently. The “George” record now sorts to the bottom of the list.

Editing your data.

  • You can edit the existing records as well. The Edit in Excel add-in pane on the far right will guide you by letting you know which fields are editable and what legal values are possible for a given column. Select any column and you will get information about that column. In the image below, the “Favorite City” column is selected and there are four possible lookup values you can choose “London”, “New York”, “Paris”, and “Los Angeles.”

Configuring Edit in Excel.

The Edit in Excel pane can be configured for specific situations

  • Select your Name in the pane and you can sign in or out.
  • Selecting the Gear icon (next to name) will allow you to set various parameters for how the Edit in Excel Add in works including control of which columns you can see in the Excel table.

Limits

  • By default, Edit in Excel can support refresh and edit of up to a maximum of 1, 000, 000 fields (cells).

Documentation

Edit in Excel is an established Dataverse feature. For more details on see our Edit in Excel documentation.

The CData ODBC driver for Microsoft Teams uses the standard ODBC interface to link Microsoft Teams data with applications like Microsoft Access and Excel. Follow the steps below to use Microsoft Query to import Microsoft Teams data into a spreadsheet and provide values to a parameterized query from cells in a spreadsheet.

If you have not already, first specify connection properties in an ODBC DSN (data source name). This is the last step of the driver installation. You can use the Microsoft ODBC Data Source Administrator to create and configure ODBC DSNs.

You can connect to MS Teams using the embedded OAuth connectivity. When you connect, the MS Teams OAuth endpoint opens in your browser. Log in and grant permissions to complete the OAuth process. See the OAuth section in the online Help documentation for more information on other OAuth authentication flows.

You can then work with live Microsoft Teams data in Excel.

  1. In Excel, open the Data tab and choose From Other Sources -> From Microsoft Query.
  2. Choose the MSTeams DSN. Select the option to use Query Wizard to create/edit queries.
  3. In the Query Wizard, expand the node for the table you would like to import into your spreadsheet. Select the columns you want to import and click the arrow to add them to your query. Alternatively, select the table name to add all columns for that table.
  4. The Filter Data page allows you to specify criteria. For example, you can limit results by setting a date range.
  5. If you want to use parameters in your query, select the option to edit the query in Microsoft Query.

  6. To set a parameter in the query, you will need to modify the SQL statement directly. To do this, click the SQL button in the Query Editor. If you set filter criteria earlier, you should have a WHERE clause already in the query.

    To use a parameter, use a '?' character as the wildcard character for a field's value in the WHERE clause. For example, if you are importing the Teams, you can set 'Id=?'.

  7. Close the SQL dialog when you are finished editing the SQL statement. You will be prompted to enter a parameter value. In the next step, you will select a cell to provide this value. So, leave the box in the dialog blank.

  8. Close Microsoft Query. The Import Data dialog is displayed. Enter a cell where results should be imported.

  9. Close the Import Data dialog. You will be prompted to enter a parameter value. Click the button next to the parameter box to select a cell. Select the option to automatically refresh the spreadsheet when the value changes.

Excel Teams Macros

The data is now imported into Excel. When you change the value in cell B1, the data will be filtered by the specified search criteria.