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

Hi, Readers.
We have discussed How to allow Open in Excel but not Edit in Excel (Prohibit the use of Edit in Excel) before. Yesterday I received a question. They want to do some restrictions with Open in Excel, for example, disable Open In Excel feature, but not Edit in Excel feature. Is there a simple way? So in this post, I would like to briefly talk about this and share a solution that I personally feel is not the best.

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
EXPORT REPORT EXCEL: Export Report DataSet to Excel

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

The most key permission is as follows.

But if you exclude this permission, Edit in Excel won’t work either😑. We can only look for ways other than permission management.

If you don’t know how to start, I recommend using the standard Event Recorder feature. For example, below are all the events recorded after using the Open in Excel function on the Item List. More details: Discoverability of Events
The key event:

codeunit 2000000006 “System Action Triggers”:

But please note that this event will only be executed if the setting below is enabled in the OneDrive Setup. (Files will always open in Excel for the web……)
More details: Configure OneDrive integration (New assisted setup: Connect your files to the cloud)

If it is downloaded locally, this cannot be controlled.

Another limitation is that it is not possible to get the page name or file name in this process, so it is not possible to make it work only for certain pages. More details: How to disable “Edit in Excel” on a specific page

Okay, we can simply subscribe to this event.

Done.

If you want more flexible control, you can add some user-related settings.
For example,

Test video:

Source code:

codeunit 50112 MyCodeunit
{
    [EventSubscriber(ObjectType::Codeunit, Codeunit::"System Action Triggers", OnOpenInExcel, '', false, false)]
    local procedure OnOpenInExcel(Location: Text);
    var
        UserSetup: Record "User Setup";
    begin
        if UserSetup.Get(UserId) then
            if UserSetup."Allow Open in Excel" then
                exit;
        Error('You cannot use this feature, please contact the administrator.');
    end;
}

tableextension 50114 UserSetupExt extends "User Setup"
{
    fields
    {
        field(50100; "Allow Open in Excel"; Boolean)
        {
            Caption = 'Allow Open in Excel';
            DataClassification = CustomerContent;
        }
    }
}
pageextension 50114 UserSetupPageExt extends "User Setup"
{
    layout
    {
        addafter("Allow Posting To")
        {
            field("Allow Open in Excel"; Rec."Allow Open in Excel")
            {
                ApplicationArea = All;
            }
        }
    }
}

Although there are some limitations to this method, it is a solution and hopefully it will give you some hints, if you have a better method, feel free to get in touch.

END

Hope this will help.

Thanks for reading.

ZHU