Adding Edit in Excel for List Parts or Custom Pages

Introduction:

Business Central provides us an easy method of modifying our data from within Excel using Web Services commonly found in the Edit in Excel action. This can be seen in the commonly used List Pages for example Payment Terms. However this functionality can be missing for certain pages or you might want to have additional logic or filtering before executing this. For this I’ll be demonstrating how to add the “Edit in Excel” action in Business Central pages.

References:

Viewing and Editing in Excel From Business Central – Business Central | Microsoft Docs

Using Filter Expressions in OData URIs – Dynamics NAV | Microsoft Docs

Configuration:

In the above piece of code, I’ve added the “Edit in Excel” action onto the Blanket Sales Order SubForm to allow for easily adding lines using Excel.

Firstly, we define the filters that we will be using on the page that we will be passing in the “EditWorksheetInExcel” procedure of the “OdataUtility” codeunit.

Note that these filters are defined as Odata expressions as the “Edit in Excel” functionality uses Excel behind the scenes.

In the next lines, we see that we pass the Page Caption, the page ID and the filters to be set on the page.

Under the hood, the procedure creates a Published Web Service using the provided Page ID and uses that for the data manipulation.

We had to prepend the additional “00000” as the procedure has been hardcoded to use “COPYSTR(“00000″ + {PageID}, 5)” meaning it starts reading after the 5th character.

Conclusion:

Thus we saw how to configure Edit in Excel for pages that do not have built in Excel functionality.

Also note that Microsoft is expanding the Excel functionality to List Parts from Business Central v19 Wave 2, you can read more about it here.