Dynamics 365 Business Central: How to add Edit in Excel to Card and Document Pages (codeunit 1481 “Edit in Excel”)

Hi, Readers.
Today I would like to talk about how to add Edit in Excel feature to Card and Document Pages in Business Central.
As you might know, 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. More details: Viewing and Editing in Excel From Business Central

But this feature does not exist in any Card or Document pages other than List Page.

PS: Starting with update 18.3, you can also view Edit in Excel in page parts, like the lines in a sales order.

So is there a way to move this feature to the card or document pages?

Let’s first look at the standard code of Business Central.
If we go to the standard code of Item List page, you will not find the Edit in Excel action.

And when you create a new page, the Edit in Excel button will be automatically added according to the page type.
For example, a new Item List page.

Although I don’t have any definitions in the code, the Edit in Excel is added automatically.

So where do we start?
This time we can use codeunit 1481 “Edit in Excel”.

This codeunit provides an interface to running Edit in Excel for a specific page.

There is a EditPageInExcel() method.

Creates web service for the specified page, and uses the web service to prepare and download an Excel file for the Edit in Excel functionality.

Let’s see an example. Try adding Edit in Excel on Item Card.

Test Video:

There are two points to note here.

1. Page Caption and PageID: You can use Page.ObjectId([Boolean]) Method to get page caption and page id. More details: How to get the opening Page ID and Page name

2. Filter: The Filter here is not the general filters on the page, it is the form used by WebService and APIs, for example, $filter=customerNumber eq ‘10000’. So the Record.GetFilters() Method cannot be used here. More details: filter in APIs.

And click on a cell in Excel, you can see the Name of the field. You can pass various filters to the ODataFilter.
For Example,
No. field: No

Last Date Modified field: Last_Date_Modified

Source Code:

pageextension 50113 ItemCard extends "Item Card"
{
    actions
    {
        addbefore(Attributes)
        {
            action(ZYEditInExcel)
            {
                Caption = 'ZY Edit in Excel';
                Image = Excel;
                Promoted = true;
                PromotedCategory = Category4;
                PromotedIsBig = true;
                ToolTip = 'Send the data to an Excel file for analysis or editing.';
                ApplicationArea = All;

                trigger OnAction()
                var
                    EditInExcel: Codeunit "Edit in Excel";
                    ODataFilter: Text;
                    Filters: Label 'No eq ''%1''';
                begin
                    ODataFilter := StrSubstNo(Filters, Rec."No.");
                    EditInExcel.EditPageInExcel(CurrPage.ObjectId(true), CurrPage.ObjectId(false), ODataFilter);
                end;
            }
        }

    }
}

Another example for Document Pages (Sales Order).

Test Video:

pageextension 50114 ZYSalesOrderExt extends "Sales Order"
{
    actions
    {
        addfirst(processing)
        {
            action(ZYEditInExcel)
            {
                Caption = 'ZY Edit in Excel';
                Image = Excel;
                Promoted = true;
                PromotedCategory = Process;
                PromotedIsBig = true;
                ToolTip = 'Send the data to an Excel file for analysis or editing.';
                ApplicationArea = All;

                trigger OnAction()
                var
                    EditInExcel: Codeunit "Edit in Excel";
                    ODataFilter: Text;
                    Filters: Label 'No eq ''%1'' and Document_Type eq ''%2''';
                begin
                    ODataFilter := StrSubstNo(Filters, Rec."No.", Rec."Document Type"::Order);
                    EditInExcel.EditPageInExcel(CurrPage.ObjectId(true), CurrPage.ObjectId(false), ODataFilter);
                end;
            }
        }

    }
}

PS:
If you want to add the Edit in Excel button to the Share Icon, you can refer to the link below.
How to add new button (action) to Share group (In Share Icon)

END

Hope this will help.

Thanks for reading.

ZHU