Overview
Once the Jet Excel add-in has been installed, Dynamics 365 Business Central has been configured to access your local Business Central data, and Web Services have been configured for access from the Jet add-in, it is an easy matter to configure your data source settings. You will need to know the Server Name, Instance, SOAP Services Port, and whether SSL is enabled for SOAP Services.
Gathering Information
-
We recommend contacting your system administrator to determine the correct Server name and other settings. You may be able to determine some of this info from within the Role Center itself.
- A Server Name and Instance can be determined by clicking the Settings arrow and then Select Server...
- The default OData Services Port is 7048
- Whether or not SSL is enabled varies by installation
Note: your installation may include more than one BC or NAV instance and the instance shown within the Role Center may not be the optimal one for you to use from within the Jet Excel add-in. Please contact your system administrator to confirm. - A Server Name and Instance can be determined by clicking the Settings arrow and then Select Server...
Configuring the Data Source
-
Within Excel, select Data Source Settings from the Jet Ribbon...
-
Click the Add button at the top of the Data Source Settings window...
-
In the New Data Source window...
- Give your data source a descriptive name
- For the Database Type, select Dynamics 365 Business Central
- For the Connection Method, select Web Services
- Click OK
-
On the Authentication tab, select the credential type which is configured for your Dynamics Web Services...
-
On the Web Services Tab...
- Enter the Server, OData Services Port, and Instance
- If your Web Service is configured to use SSL encryption, check the Use SSL Encryption box
- Pick the Jet_Data_Source (JetDataSource in later versions) codeunit
- Select the name of the Company you would like to use
Please note, Microsoft announced changes in Business Central 2021 release wave 2 (version 19.0) that SOAP has been superseded by OData V4. It's recommended that integrations are migrated to OData V4 as soon as possible.
Please reference the Using Odata section of our knowledgebase article for more information.- Click the Test Connection button at the top of the window
-
On the Display tab, you have the option to specify the language in which the Jet Excel add-in will display database captions including tables, fields and option values.
From the Language pull-down, select the language code which matches that used by Business Central.
Your Business Central language setting can be determined from within the Dynamics Role Center by clicking the Settings arrow and then Select Langauge…
Troubleshooting
The message at the bottom of the Data Source Settings says “You are licensed as a Viewer” and you should be licensed as a Designer.
Check with your system administrator.
If your Jet license is set to use your Excel user name, check to make sure that your Excel user name EXACTLY matches your designer name on the Jet License.
If your Jet license is configured to use your Windows login information, ensure that you are listed on your organization's license as a Designer.
Comments