Dynamics 365 Business Central: How to allow Open in Excel but not Edit in Excel (Prohibit the use of Edit in Excel)

Hi, Readers.
I saw an interesting question in the Business Central forum this morning, they wanted to only prohibit Edit in Excel feature and allow users to use Open in Excel feature. But didn’t find an easy way to do it. More details: How to allow “Open in Excel” but not “Edit in Excel”.

In fact, this is not very difficult. So in this post I would like to briefly share two ways, one is by permission management and the other is by customization.

PS: Viewing and Editing in Excel From Business Central
1. With the Open in Excel action, you can make changes to the records in Excel, but you can’t publish the changes back to Business Central. You can only save the changes to Excel file, without affecting data in Business Central.

2. The Edit in Excel action is available on most lists, but not all. With the Edit in Excel action, you make changes to records in Excel and then publish the changes back to Business Central. When Excel opens, you’ll see the Excel Add-in pane on the right.

In general, users assigned to the EXCEL EXPORT ACTION permission set can use the Open in Excel and Edit in Excel features.

EXCEL EXPORT ACTION: D365 Excel Export Action

The following two permission sets are included.
EDIT IN EXCEL – VIEW: Edit in Excel – View (Key permission)
EXPORT REPORT EXCEL: Export Report DataSet to Excel

Without this permission set, the user would not be able to see these two actions.

So what permissions are there that are only relevant to Edit in Excel?

Codeunit 1488 Edit in Excel Workbook

Let’s see more details. I create a new permission set (Test) which excludes the Codeunit 1488 Edit in Excel Workbook permission.

When the user choose Edit in Excel, the following error will be prompted.

Sorry, the current permissions prevented the action. (CodeUnit Edit in Excel Workbook Execute: System Application)

Test video: looks good.

Okay, let’s move on to the second way. This method we mentioned when discussing How to disable “Edit in Excel” on a specific page.
Using OnEditInExcelWithFilters event in codeunit 1481 “Edit in Excel”. (OnEditInExcel event was used before BC22)

Source code:

codeunit 50112 MyCodeunit
{
    [EventSubscriber(ObjectType::Codeunit, Codeunit::"Edit in Excel", OnEditInExcelWithFilters, '', false, false)]
    local procedure OnEditInExcel();
    begin
        Error('You can not use Edit in Excel!!!');
    end;
}

You can not use Edit in Excel!!!

This way is more flexible than permission management, and you can add some settings, or other conditions to control whether users can use the Edit in Excel feature.

Test video:

END

Hope this will help.

Thanks for reading.

ZHU