Microsoft Excel integration with Dynamics 365 Business Central – Konrad Marszałek

While working with Business Central 365 we often operate with big amount of data such as: payment information, purchase and sales documents or customer and supplier data. Some data may change in the course of the operation and should be corrected or changed. This can of course be done directly in the system. But what to do when we have up to several hundred records to improve? Excel comes in handy, as it can be integrated with Dynamics 365 Business Central.

Open and edit data in Excel

We can use the capabilities of an Excel spreadsheet in Dynamics 365 Business Central in two ways:

• Open the content of a given form.
• Editing data in a given form.

For example, to open a list of buyers, select the action Page -> Open / Edit in Excel from the level of the activity bar of this form.

After selecting action „Open in Excel” the system will generate .xlsx file and automatically will download it. This spreadsheet will contain exactly the same data as in form in Business Central application.

Much more interesting and useful functionality is the possibility to edit data from Business Central with Excel. However, to do this, you must first install the appropriate add-on

To do this, from the ‘Inserting’ tab in Excel, select the ‘Download add-ons’ option and install Microsoft Dynamics Office Add-In.

Installing this add-in will allow you to edit the form and configure the connection to Business Central. After opening the sheet, the connection window will appear on the right. You must log in to the Microsoft account, from the level of which we also connect to the Business Central application. After logging in, a connection will be automatically established that will allow you to publish your changes.

Editing and publishing changes – Microsoft Excel integration with Dynamics 365 Business Central

An exemplary case: We want to block some buyers and define or change accounting groups.

This kind of changes would require a significant amount of time in Business Central, so it is much easier and faster to do it by editing the list of buyers in Excel.

There are several options available in the Excel add-in window:

• New– adds a new row on the spreadsheet that matches a record in the selected table.
• Refresh – updates data from all connected sources.
• Publish – sends the changes to Business Central.
• Filter – allows to filter the relevant data from the table.
• Project – allows to add new data sources (tables), extra fields and labels.

After enetering changes we were interested in we press Publish button. If data have been entered in the correct format, the system will accept them and update the data in Business Central. In the event that we enter incorrect data, e.g. in the option field, we enter a value that cannot appear there, the system will not allow the publication of changes, will show an error and mark the line with an illegal value in red. In this case, you only need to correct the record and republish the file to Business Central.

Summary – The integration of Microsoft Excel with Dynamics Business Central 365

The integration of Microsoft Excel with Dynamics Business Central 365 is a great way to optimize your working time. In addition to the possibility of mass editing of many different data in Business Central, users can also use this data to create charts or pivot tables, which can be used, for example, when creating presentations.

The integration presented in the article is one of the several possibilities of using Excel in Business Central. In addition to it, we have many other functions, such as the ability to export entire forms (pages) and reports to the .xlsx format. A useful functionality is also the use of the so-called configuration packages for quick transfer of settings or data between companies or databases.

Author

Konrad Marszałek –Navision / Business Central 365 developer at IT Vision. He programs in AL on a daily basis, implements modifications and extensions to Business Central and provides technical support to customers