Setting up the Excel Add-In for Editing Dynamics NAV Data

  • Artigo

You can set up the Dynamics NAV deployment to support an Excel add-in that enables users in the Dynamics NAV client to work with data from list pages in Excel. Users can get fresh data from Dynamics NAV and update the data in Dynamics NAV based on their work in Excel.

Without this add-in, users can open a list page in Excel from the Open in Excel action on the page, which does not allow them to push changed data back to Dynamics NAV. When this add-in is set up, the Open in Excel action is replaced by the Edit in Excel action.

Note

This Excel add-in is different than the Microsoft Office Excel Add-in that can be installed with the Dynamics NAV client by using the the Dynamics NAV Setup. For more information about Microsoft Office Excel Add-in, see Client Option.

Prerequisites

Your deployment must meet the following prerequisites:

Register and configure an Azure AD Application for the Excel Add-in in Microsoft Azure

When Azure AD authentication was set up for your Dynamics NAV deployment, an Azure AD tenant was created in Microsoft Azure, and an application for Dynamics NAV was registered in the tenant. The Excel add-in requires that you add (or register) a separate Azure AD application in the tenant.

You can add the Azure AD application by using the Azure portal. The following procedure outlines the steps you must perform to add and configure the Excel add-in application in your Azure AD tenant. For more specific guidelines about how to use the Azure portal, see Register your application with your Azure Active Directory tenant.

  1. Add an Azure AD application for the Excel add-in.

    When you add an application to an Azure AD tenant, you must specify the following information:

    Setting Description
    Name The name of your application as it will display to your users, such as Excel Add-in for Dynamics NAV.
    Type Choose Web application and/or web app.
    Sign-on URL (App URL) The URI for signing in to your Microsoft Dynamics NAV Web Server components, such as https://www.solutions.com/DynamicsNAV or https://www.solutions.com/DynamicsNAV/WebClient/ (for Microsoft Dynamics NAV 2017 and earlier versions).
    App ID URI The URI to a domain in your Azure AD tenant, such as `https://solutions.onmicrosoft.com/ExcelAddinforDynamicsNAV`. Important: The App ID URI must be unique within the Azure AD tenant and not the same as you specified for your Dynamics NAV solution.
    Directory Access Choose Single Sign-On.

  2. Grant the Excel add-in application permission to the Dynamics NAV application.

    You must give the Azure AD application for the Excel add-in delegated permission to access the Dynamics NAV application in Azure AD. This allows users of the Excel add-in to access the OData web services to read and write data.

    In the portal, you set up permissions on the configuration/settings page of Excel add-in application, in the permissions section.

  3. Configure OAuth2 authentication in the Excel add-in application manifest.

    The Excel add-in requires OAuth2 implicit grant flow to be enabled on the application. The manifest for the application is a .json file type. To enable OAuth2 implicit grant flow in the manifest file, change the oauth2AllowImplicitFlow key to true.

  4. Add the following URL to the ReplyUrl list:

    https://az689774.vo.msecnd.net/dynamicsofficeapp/v1.3.0.0/*

  5. Copy the Client ID (Application ID) that is assigned to Excel add-in application. You'll need this in the procedure.

This completes the work you have to do in the Azure portal. The final configuration is to add the Excel add-in to the Microsoft Dynamics NAV Server instances.

Configure the Microsoft Dynamics NAV Server Instances

You must add the Excel add-in to the Microsoft Dynamics NAV Server instances in your deployment. You can use either the Microsoft Dynamics NAV Server Administration tool or Set-NAVServerConfiguration cmdlet in the Microsoft Dynamics NAV Administration Shell.

  1. In the Microsoft Dynamics NAV Server Administration tool, in the Azure Active Directory section, set the Excel add-in AAD client ID field to the client ID (or application ID) for the Excel add-in application that you copied from the Azure portal.

    With the Set-NAVServerConfiguration cmdlet, set the ExcelAddInAzureActiveDirectoryClientId key.

  2. In the Client Services section, set the Web Client Base URL field to the base URL of the Microsoft Dynamics NAV Web client.

    This is the root portion of all URLs that are used to access pages in the web client. This must have the format https://[hostname:port]/[instance] or https://www.solutions.com/DynamicsNAV/WebClient/ (for Microsoft Dynamics NAV 2017 and earlier versions), such as https://MyNavWebServer/DynamicsNAV and https://MyNavWebServer/DynamicsNAV/WebClient/.

    With the Set-NAVServerConfiguration cmdlet, set the PublicWebBaseUrl key.

  3. In the OData Services section, set the OData Base URL field to the public URL for accessing OData services.

    The URL must have the following format https://<hostname>:<port>/<instance>/ODataV4/, such as https://Cronus.Nav.net:7047/dynamicsnav/ODataV4/.

    With the Set-NAVServerConfiguration cmdlet, set the PublicODataBaseUrl key.

Use the Excel Add-In

Your users can now use the Excel add-in. When a list page shows the Edit in Excel action, then users can open lists such as the Customers page in Excel and work with the data there. They can use the add-in to update data in Dynamics NAV, and they can get fresh data from the database.

See Also

Configuring Microsoft Dynamics NAV Server
Authenticating Users with Azure Active Directory
Walkthrough: Configuring Web Services to Use SSL (SOAP and OData)
How to: Configure the Microsoft Dynamics NAV Web client to Accept Host Names for Tenants