Live Cross-Workbook Formulas in Delphi with HotXLS

HotXLS can evaluate a formula such as =[Rates.xlsx]FX!B4*C10 against another workbook that is open in memory, not against a stale cached value. You register the second workbook by name with RegisterExternalWorkbook, and every recalculation reads the referenced cells straight from that object. Unregister it and evaluation falls back to the external-link cache the file already carries

This closes a specific and frustrating gap. Excel workbooks routinely reference each other: a rate table, a chart of accounts, a price list maintained by a different team. When such a file is opened by a library rather than by Excel, those references normally resolve to whatever values were cached the last time Excel recalculated, which can be months old, and no amount of recalculating fixes it because the data simply is not there

What does a cross-workbook reference look like inside the file?

An external reference is not stored as the text you see in the formula bar. The workbook keeps a table of supporting books, each formula token points at an index into that table, and a separate cached-values part holds the last known result for each referenced cell. That is why a file opened without its companions still shows numbers

Parsing the text form has its own subtleties. The lexer treats a bracketed book name as part of a single identifier, so [Book.xlsx]Sheet!A1 becomes one token rather than an unbalanced bracket followed by a sheet reference. Names are then normalised for lookup: quoting, path prefixes, the file extension and the numeric-index form all collapse to an upper-cased base name, so 'C:\Rates\[Rates.xlsx]FX'!B4 and [rates.xlsx]FX!B4 resolve to the same registered book

Registering a workbook and recalculating

Registration is by name, and the name is the one that appears in the formulas of the workbook doing the referencing:

uses
  lxHandleX;

var
  Main, Rates: TXLSXWorkbook;
begin
  Main := TXLSXWorkbook.Create;
  Rates := TXLSXWorkbook.Create;
  try
    if (Main.Open('valuation.xlsx') <> 1) or
       (Rates.Open('rates.xlsx') <> 1) then
      Exit;

    // Formulas in valuation.xlsx that say [Rates.xlsx]... now read
    // from the live object instead of the cached external values
    Main.RegisterExternalWorkbook('Rates.xlsx', Rates);
    Main.Recalculate;

    Writeln(VarToStr(Main.Sheets[0].Cells[10, 3].Value));

    // Change the source and recalculate again: the new value flows through
    Rates.Sheets[0].Cells[4, 2].Value := 1.0842;
    Main.Recalculate;
    Writeln(VarToStr(Main.Sheets[0].Cells[10, 3].Value));
  finally
    Main.UnregisterExternalWorkbook('Rates.xlsx');
    Rates.Free;
    Main.Free;
  end;
end;

The registration holds a weak reference. HotXLS does not take ownership of the registered workbook and will not free it, which means the lifetime is yours to manage and the ordering matters: unregister before freeing the source workbook, or at minimum do not recalculate after freeing it

What happens when a book is not registered?

Evaluation falls back to the external-link cache stored in the file, which is exactly the behaviour of a workbook opened without its companions. That fallback is the reason unregistering is safe rather than destructive

The behaviour after unregistration is worth stating precisely, because it surprises people. When a reference can be resolved neither from a registered book nor from the cache, the evaluation produces an error and that error is not written back into the cell. The cell keeps its last computed value. Re-register the source and recalculate, and the value updates again

This preserve-on-failure rule is deliberate. The alternative, writing an error value into every dependent cell the moment a source is unavailable, would turn a temporarily missing rate table into a workbook full of error markers that a later successful recalculation has to repair. Keeping the last good value means an interrupted pipeline leaves a document that still reads sensibly

Dirty propagation stops at the workbook boundary

Editing a cell in the registered source does not mark dependent cells in the main workbook dirty. Incremental recalculation tracks dependencies inside one workbook; it does not watch across the boundary. Excel behaves the same way, which is why its external references update on open or on an explicit refresh rather than continuously

The practical consequence is that changing a source value requires an explicit recalculation of the referencing workbook, as in the example above. If your application edits both books in a loop, recalculate once at the end rather than after each edit; the incremental engine described in incremental recalculation and the dependency graph then does the minimum work inside each workbook

Three shapes this feature fits

Consolidation is the obvious one. A parent workbook references twelve monthly files; register all twelve, recalculate once, and read the consolidated figures without ever opening Excel. The registration name for each must match what the parent's formulas actually say, which is worth verifying against the formula text rather than assuming it matches the file name

Scenario analysis is the second. Register a source, write a set of assumptions into it, recalculate the dependent model, record the outputs, then repeat. Because the source is a live object, no file is written between iterations, and a hundred scenarios cost a hundred recalculations rather than a hundred save-and-reload cycles

Validation before delivery is the third and the least obvious. Open a workbook, register its companions, recalculate, and compare the results against the values the file already carried. A mismatch means the cached values are stale, which is a defect worth catching before a client receives the file. That comparison is a natural companion to the workbook diffing described in diffing two Excel workbooks

Reference hygiene before you rely on it

Run a formula analysis pass first. It reports missing functions, external references and circular dependency components in one traversal, which tells you exactly which book names a document expects before you go looking for the files:

var
  Issues: TArray<TXLSXFormulaIssue>;
  I: Integer;
begin
  Issues := Main.AnalyzeFormulaIssues;
  for I := 0 to High(Issues) do
    if Issues[I].Kind = xlsxFormulaIssueExternalReference then
      Writeln(Format('sheet %d R%dC%d needs [%s] (%s)',
        [Issues[I].Sheet, Issues[I].Row, Issues[I].Col,
         Issues[I].ExternalWorkbook, Issues[I].SourceFileName]));
end;

Two other habits save time. Keep the registration name canonical — register under the plain file name and let the normalisation handle quoted and path-prefixed variants in the formulas. And treat a workbook that references files you cannot supply as a read-only artefact: recalculating it will preserve the cached values, but any edit that changes a dependent cell leaves the document in a state where some values are current and some are historic, with nothing in the file to say which is which. Copying formulas between workbooks is a different operation with its own rebinding rules, covered in cross-workbook copy and formula rebinding, and named ranges add another layer described in defined names and cross-sheet formulas

Live cross-workbook evaluation, incremental recalculation and formula analysis run on the same engine for Delphi and C++Builder; the complete feature list is on the HotXLS Delphi spreadsheet component page