I have never seen Business Central integrate with Excel like this!

Join me in a hack to explore a different type of integration between Business Central Excel. I spend a few days on a kind of “what if” exercise, check out the result in this video:

https://youtu.be/b0kgCX2usw8

In this video, Erik demonstrates a fascinating experiment: running AL code directly inside Excel cells that connects live to Business Central data. The concept enables Excel cells to become “living” connections to Business Central, capable of both reading and writing data — a fundamentally different approach from the traditional bulk data export that Microsoft has focused on.

The Problem with Current Excel Integration

Erik observes that Microsoft has invested heavily in getting data out of Business Central into Excel — through report layouts, edit-in-Excel experiences, and similar features. But the integration remains largely one-directional and bulk-oriented. The editing experience in Excel isn’t particularly smooth, and there’s no concept of a “live cell” that stays connected to a specific piece of Business Central data.

The idea that kept stirring in the back of Erik’s head: what if you could point to a random field inside Business Central, drag that value into an Excel cell, and have it become a living, two-way connection to the actual data?

The Demo: AL Code Running in Excel Cells

Erik demonstrates an Excel spreadsheet where typing a customer number (e.g., '10000 or '20000) into cell F8 triggers a cascade of activity. Related cells automatically populate with the customer’s name, address, and credit limit — all pulled live from Business Central.

But it goes further than just reading. Erik modifies the credit limit value directly in Excel, switches back to Business Central, refreshes, and the change is reflected. He also edits the customer name in Business Central and sees it update in Excel. This is genuinely two-way, live data integration.

How It Looks in the Cell

Each connected cell contains an Excel formula that looks like this:

=AL.RUN("var customer: Record Customer; begin if customer.Get(""&F8&""") then message(customer.Name); end;")

That’s right — there’s actual AL code embedded as a string inside an Excel formula. The AL.RUN function is a custom Excel function that sends the AL code to Business Central for execution and returns the result to the cell.

A particularly clever aspect is how Excel cell references are embedded within the AL code. By using double quotes to “exit” the AL string, you can insert Excel formula references like &F8& to inject cell values as parameters. So the customer number in cell F8 becomes the argument to customer.Get().

Writing Data Back

For the credit limit update cell, the AL code is more involved:

var customer: Record Customer;
begin
    if customer.Get("&F8&"") then begin
        customer.Validate("Credit Limit (LCY)", "&TEXT(F13,"0")&");
        customer.Modify();
        Message('Updated');
    end;
end;

This grabs the value from cell F13, validates it into the Credit Limit field on the customer record, modifies the record, and returns “Updated” to the cell. During the demo, Erik had accidentally left in a “multiply by seven” line, leading to an amusing moment where entering 12345 resulted in 86415 — a reminder that this is very much experimental code!

The Simplest Example

At its most basic, you can type a formula like this into any cell:

=AL.RUN("begin Message('Hello YouTube'); end;")

Hit enter, and the cell displays “Hello YouTube” — the output of the Message function becomes the cell’s return value.

How It’s Built

The solution consists of three components working together:

1. Office Add-in (Excel Side)

The Excel side is an Office Add-in, which consists of:

  • Custom Functions — The AL.RUN function that can be used in Excel formulas
  • Task Pane — A side panel with an editor, plus Load and Save buttons for easier editing of AL code in cells
  • Commands — Available but not used in this implementation

The custom function is defined in a functions.js file as a Node.js application. The function namespace is set to “AL” in the manifest file, which is how AL.RUN gets its name. The function takes the AL code as a parameter, Base64-encodes it (using Node.js Buffer.from since the standard atob/btoa functions don’t exist in Node), and sends it to a Business Central API endpoint.

2. The Task Pane Editor

Because editing AL code inside a formula bar is painful, Erik built a task pane with a proper code editor. The Load button grabs the formula from the selected cell, strips the AL.RUN wrapper, and displays the clean AL code. The Save button wraps the code back in AL.RUN and writes it back to the cell. Erik notes this is deliberately simple — it would break if AL.RUN were part of a larger formula — but it serves as a proof of concept.

3. Business Central API (AL Toolbox)

On the Business Central side, Erik uses the AL compiler and interpreter from his personal toolbox — a “Remote AL” capability exposed as a codeunit via OData. The endpoint receives the AL code as text, parses it into a syntax tree, runs it through an interpreter, and returns whatever was output to the console (in this case, Message output). If there are compilation errors, those are returned instead.

The API endpoint follows the standard OData pattern:

/OData/CodeunitName_FunctionName

Lessons Learned: Shared Runtime vs. Custom JS

Erik shares a hard-won lesson about Office Add-in development. When creating the project, you can choose between a shared runtime or a custom JavaScript runtime. Erik initially chose the custom runtime, thinking the shared one sounded overly complicated with rules about sharing.

This completely backfired. The custom runtime couldn’t make web service calls due to various restrictions and quirks. Erik found support forums full of developers with the same problem, all struggling — until a Microsoft response at the bottom recommended simply using the shared runtime instead. After starting over with the shared runtime, everything worked.

Future Possibilities

Erik envisions several extensions of this concept:

  • Listing groups of data — Formulas that could populate ranges of cells with query results
  • Mobile scenarios — Since Excel runs on handhelds, this could enable mobile data entry into Business Central
  • Excel Online — The approach should theoretically work in the web version of Excel as well
  • More sophisticated editing — The task pane editor could be enhanced to support AL code within larger formulas

Source Code: Related AL Examples

While the main demo focused on the Excel integration, Erik’s project also includes some related AL code for working with Business Central data. Here’s an example of a page and table definition from the project:

table 50108 StuffandDate
{
    Caption = 'StuffandDate';
    DataClassification = ToBeClassified;
    
    fields
    {
        field(1; StartDate; Date)
        {
            Caption = 'StartDate';
            DataClassification = ToBeClassified;
        }
    }
    keys
    {
        key(PK; StartDate)
        {
            Clustered = true;
        }
    }
}
page 50108 "My Dates"
{
    ApplicationArea = All;
    Caption = 'My Dates';
    PageType = List;
    SourceTable = StuffandDate;
    UsageCategory = Administration;

    layout
    {
        area(content)
        {
            repeater(General)
            {
                field(StartDate; Rec.StartDate)
                {
                    ToolTip = 'Specifies the value of the StartDate field.';
                }
            }
        }
    }
    actions
    {
        area(Promoted)
        {
            actionref(testpromoted; test)
            { }
        }
        area(Processing)
        {
            action(Test)
            {
                Caption = 'Test';
                trigger OnAction()
                var
                    Recur: Codeunit "Recurrence Schedule";
                    ID: Guid;
                    LastDate: DateTime;
                    i: Integer;
                begin
                    Rec.DeleteAll();
                    ID := Recur.CreateWeekly(0T, today(), 20231231D, 1, 
                        true, false, false, true, false, false, false);
                    Message('This is: %1', Recur.RecurrenceDisplayText(ID));

                    for i := 1 to 10 do begin
                        LastDate := Recur.CalculateNextOccurrence(ID, LastDate);
                        Rec.StartDate := DT2Date(LastDate);
                        Rec.Insert();
                    end;
                end;
            }
        }
    }
}

Conclusion

This experiment demonstrates a fundamentally different approach to Excel–Business Central integration. Instead of bulk data exports or clunky edit-in-Excel experiences, imagine individual cells that are live, bidirectional connections to Business Central data — powered by AL code running directly in Excel formulas. While Erik acknowledges this is a proof of concept rather than a production-ready solution, the core idea is compelling: a smarter, more seamless, cell-level integration between Excel and Business Central. The question Erik poses to the community is the right one — this feels like something Microsoft should explore building natively into the platform.

Picture of hougaard

hougaard