Implementing Edit in Excel from Business Central

The Edit in Excel action is available on most lists. With the Edit in Excel action, you make changes to records in Excel and then publish the changes back to Business Central. This blog will guide you through the steps needed to implement and effectively use the Edit in Excel functionality in Business Central.

❌ This functionality is only available on Business Central cloud environment.

---

“Edit in Excel” implementation

You can add it to any page in Business Central. For our example, we will use the Sales Price List and focus on the lines sub-page Price List Lines.

As you can see currently, it has only Open in Excel procedure, which opens the data in Excel but not Edit in Excel. I will show you now how easy it is to add it.

To implement, we need following codeunit:

EditinExcel: Codeunit "Edit in Excel";

It should look like this:

Let’s publish and check the Sales Price List page now:

Action is shown after Open in Excel and now it doesn’t only open the data, but it also allows you to edit and sync back to Business Central.

This is not fully correct for now, you will need to filter data received, otherwise it will show you Price List Lines for all Price Lists!

---

Filter data being received to Excel

If you need to filter the data before it gets sent to Excel, you will need to use procedure AddFieldV2 from Edit in Excel Filters codeunit.

We need to filter data for specific Sales Price List:

Field Price List Code is added as Edit in Excel Filter, so it shows only Sales Price List Lines for the Price List that you have opened and not for all Sales Price List Lines. This is most commonly used on Card Pages where “Edit in Excel” is implemented.

❌ Method AddField is obsolete from version 25.0. Use AddFieldV2 instead.

---

Excel exported File name

I would recommend to change file name of Excel which is being exported, otherwise it would be default:

In order to do that, define additional Label and add it as last parameter in EditPageInExcel procedure.

The result:

---

Visibility for SaaS

As already mentioned, on beginning of this blog post, this functionality is only available for SaaS. That’s why it’s also recommended to add visibility property to the action.

Declare variable for visibility property:

Check if Environment is SaaS on the trigger “OnOpenPage”. To do that use method GetIsSaasExcelAddinEnabled from Server Setting codeunit.

And of course, add Visible property to action parameters:

---

By the way, you don’t need to add “Edit in Excel” on Price List Lines page anymore, this is my contribution to Business Central Base Application and will be implemented in the next release:

https://github.com/microsoft/BusinessCentralApps/pull/1019

Additionally you can read more about how “Edit in Excel” is working from functionality side on YZhums blog: Dynamics 365 Business Central: How to use Edit in Excel in the browser/web (Excel Online) | Dynamics 365 Lab

Hope you find this useful and stay tuned for more! 🚀