'After your service updates the monthly workbook, our macros are gone.' That support ticket typically has nothing to do with macro code at all. Somewhere in the pipeline a macro-enabled workbook was saved under a plain .xlsx name, and Excel — following the ECMA-376 content-type rules — refused to load a VBA project from a package whose content type says it has none. No API call deleted anything; the file name did.
UK teams should align this hotxls vba external links preservation workflow with local governance, audit, and data quality requirements before production release
For UK environments, align the hotxls vba external links preservation implementation with local quality gates that include governance approval, versioned fixture baselines, and evidence retention for every publication candidate. Keep an explicit review log for accessibility, redaction policy, and data residency checks so your deployment audit is repeatable without changing output binaries
UK technical governance addendum
Macro projects and external workbook links are the two assets most often destroyed by well-meaning automation, because both live outside the cell grid that editing code actually touches. HotXLS, a native Delphi and C++Builder library for reading and writing XLS and XLSX files without Excel, treats both as first-class payloads: it can carry them through a rewrite, inject them, extract them, and tell you they exist. This article maps exactly what survives, what does not, and where the honest limits are.
Two payloads, two distinct ways to lose them
A VBA project is a single opaque binary — vbaProject.bin within an OOXML package, an OLE storage within a BIFF file. It is lost in one of two ways: the writer fails to copy it into the output, or the output is saved under a file type that disallows it. An external link is the opposite kind of structure: not one blob but a web of relationships — a target path or URL, the list of sheet names in the remote workbook, and optionally a cache of last-seen cell values that lets Excel display something when the target is unreachable.
Editing code that thinks only in terms of cells will preserve neither by accident. The preservation guarantees have to come from the library's file writer, which explains why it pays to know precisely what HotXLS writes back for each.
Carrying a VBA project through an XLSX rewrite
On the XLSX side, TXLSXWorkbook stores the macro payload verbatim: the VbaProject property holds the raw vbaProject.bin bytes in an AnsiString container, an empty string meaning no macros. HasVbaProject tests for presence, ClearVbaProject strips it deliberately, and LoadVbaProjectFromFile injects a project extracted from a template — a clean way to give generated workbooks a standard set of macros without keeping a full template file around:
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Data');
Sheet.Cells[1, 1].Value := 'Refreshed ' + DateTimeToStr(Now);
Book.LoadVbaProjectFromFile('macros\vbaProject.bin');
if not Book.HasVbaProject then
raise Exception.Create('VBA payload failed to load');
// The .xlsm extension is not cosmetic: it selects the
// macro-enabled content type inside the package.
Book.SaveAs('monthly-report.xlsm');
finally
Book.Free;
end;
end;The save line carries the rule that produces the support ticket from the opening paragraph: a workbook holding a VBA project must be saved with macro-enabled semantics, which HotXLS applies when the target is .xlsm. Save the same content as .xlsx and Excel will refuse to load the macros even though the bytes could physically fit in the package. When you need to look within the payload instead of just carry it — listing module names for an audit report, for instance — the ParsedVBAProject property exposes the parsed module model, whilst VbaProject itself remains the untouched original bytes.
Reusing macros from legacy XLS workbooks
The BIFF facade has the symmetric tool set: HasVBAProject probes a loaded file, SaveVBAProjectToFile extracts the project storage to disk, and LoadVBAProjectFromFile injects it into another workbook. That enables a common modernization chore — harvesting the macros from a 2003-era model file and planting them into freshly generated XLS output:
var
Src, Dst: IXLSWorkbook; // interface references: no manual Free
begin
Src := TXLSWorkbook.Create;
if Src.Open('legacy-model.xls') <= 0 then
raise Exception.Create('Cannot open legacy model');
if Src.HasVBAProject then
Src.SaveVBAProjectToFile('extracted-vba.bin');
Dst := TXLSWorkbook.Create;
Dst.Sheets.Add.Name := 'Report2026';
Dst.LoadVBAProjectFromFile('extracted-vba.bin');
Dst.SaveAs('report-with-macros.xls');
end;Note the memory model: TXLSWorkbook is held through the reference-counted IXLSWorkbook interface and must not be freed manually — the opposite of the XLSX class. Mixing the two conventions in one unit is a reliable source of double-free crashes. Keep extraction and injection within the same file format; the BIFF project storage and the OOXML vbaProject.bin are related but not interchangeable containers, and a pipeline that needs macros in both formats should maintain one macro template per format.
External links: the map survives, the cached values do not
For XLSX workbooks, HotXLS models external links through the ExternalLinks collection. Each TXLSXExternalLink carries the Target — a path or URL to the remote workbook — and the SheetNames list describing its sheets. Both round-trip faithfully through open and save, and links can be created programmatically:
var
Link: TXLSXExternalLink;
begin
Link := Book.ExternalLinks.Add('\\fileserver\finance\fx-rates-2026.xlsx');
Link.SheetNames.Add('FX');
if Book.ExternalLinks.Count > 0 then
Writeln(Format('%d external link(s): delivery requires reachable targets',
[Book.ExternalLinks.Count]));
end;Here is the boundary your pipeline design must respect: HotXLS round-trips the link map — target and sheet names — but it neither parses nor writes the cached cell values that OOXML stores in the link's sheetDataSet element. The practical consequence appears on the recipient's machine, not yours. When Excel opens a workbook whose link cache is absent and whose target is unreachable — a laptop off the VPN, a renamed share — formulas that depend on the link degrade to #REF! errors or stall behind an update prompt. Never promise that a generated workbook will display externally linked values offline, and treat a nonzero ExternalLinks.Count as a delivery precondition: the targets must be reachable from wherever the file is opened.
What the XLS reader preserves byte-for-byte
The BIFF side takes a different strategy for structures HotXLS does not model. Pivot caches and pivot views (the SX* record family), QueryTable definitions, external data connections, custom views, header pictures, and theme records are all carried through an open-and-save cycle as raw record blocks — unparsed, unmodified, intact. External references themselves round-trip through the underlying EXTERNSHEET and SupBook records, although there is no typed creation API on this side.
Raw preservation is a real guarantee, but it cuts both ways. Your edits cannot corrupt a pivot cache, because nothing touches it; equally, nothing updates it. Insert rows through a region a preserved structure points at and the structure keeps its original coordinates whilst the data moves — the file stays valid, the semantics silently rot. The safe layout convention is to confine generated edits to sheets that contain no preserved structures, which is also the discipline recommended for protected and print-configured sheets in our article on worksheet protection and page setup.
Acceptance checks before a governed workbook ships
Because both failure modes are silent, the only trustworthy verification is reopening the file you actually wrote. Three checks catch nearly everything. Reopen the output and confirm HasVbaProject still answers true when macros were expected — this catches both a dropped payload and a wrong extension in one test. Compare ExternalLinks.Count before and after the rewrite. And open the file once in Excel with macros disabled: Excel's own content-type validation is stricter than any library's, and it is the arbiter your customers will use.
For high-volume intake, you do not need a full parse just to triage which files contain governed content — the lightweight probing techniques in our article on sheet listing and lightweight workbook inspection let you route macro-bearing and linked workbooks to a stricter pipeline before any rewrite happens.
Frequently asked questions
Does HotXLS ever execute the macros it preserves?
No. The library has no VBA runtime; it stores, copies, extracts, and injects the project as data. That is a security feature on servers: a malicious macro passing through a HotXLS pipeline is inert bytes until a desktop copy of Excel opens the file and the user enables content.
Can I convert an .xlsm to .xlsx and keep the macros?
No — and that is the file format's rule, not the library's. The .xlsx content type declares a macro-free workbook, so the only honest conversions are keeping .xlsm or calling ClearVbaProject and shipping a genuinely macro-free file. Choose explicitly; the default behaviour of renaming is the worst of both.
Why do linked cells show #REF! after my service rewrites the workbook?
The rewritten file carries the link target but no cached values, so Excel must resolve the target at open time. If the path was relative to the original author's environment, or the share is unreachable, resolution fails. Either guarantee target reachability or remove the dependency by writing computed values into the cells before delivery.
Preserving what you do not understand is most of the job when editing other people's workbooks. The VBA and link round-trip facilities described here ship with the HotXLS Component, alongside the audit properties used to detect governed content at intake.