iOfficeAI/OfficeCLI · error · ArgumentException

Cross-workbook references like '{refVal}' require an externa

Error message

Cross-workbook references like '{refVal}' require an externalLinks part which officecli doesn't expose; use raw-set for this case

What it means

Thrown when the resolved ref/refersTo/formula value starts with an external (cross-workbook) reference, detected by the regex `^\[(\d+|[^\]]*\.xls[xbm]?)\]` after stripping leading whitespace/quote. Forms like `[Other.xlsx]Sheet1!$A$1` or `[1]Sheet1!A1` (and the single-quoted variant) require an externalLinks part that officecli does not expose. Writing such a ref without the part produces a silently broken defined name (formulas show #REF!), so it is refused. Structured refs like `Table1[Price]` are deliberately NOT matched.

Source

Thrown at src/officecli/Handlers/Excel/ExcelHandler.Add.Tables.cs:103

        // R27-1: cross-workbook references like "[Other.xlsx]Sheet1!$A$1"
        // or "[1]Sheet1!$A$1" need an externalReferences part to resolve.
        // Without one, Excel opens the file but formulas referencing the
        // name show #REF!. Reject up-front rather than write a silently
        // broken defined name.
        // CONSISTENCY(xref-detect): bt-5/fuzz-NR01 — also catch the
        // single-quoted form `'[Book.xlsx]Sheet'!A1` (Excel's standard
        // quoting for sheet names with spaces) which previously slipped
        // through and produced a silently broken defined name.
        // CONSISTENCY(cross-workbook-vs-structured-ref): mirror Helpers.cs
        // RejectCrossWorkbookFormula. Tight match against `[<digits>]` or
        // `[<name>.xls(x|m|b)?]` so structured-ref namedrange values like
        // `Table1[@Col]` or `Table1[Price]` aren't falsely rejected.
        var refValProbe = refVal.TrimStart(' ', '\t').TrimStart('\'');
        if (System.Text.RegularExpressions.Regex.IsMatch(refValProbe,
                @"^\[(\d+|[^\]]*\.xls[xbm]?)\]",
                System.Text.RegularExpressions.RegexOptions.IgnoreCase))
            throw new ArgumentException(
                $"Cross-workbook references like '{refVal}' require an externalLinks part which officecli doesn't expose; use raw-set for this case");

        // Sheet-qualified refs must name an existing sheet with a plausible
        // range — garbage like "乱码!!!" written verbatim made real Excel
        // refuse the file while schema validation stayed green.
        // A bare A1-style range with no sheet qualifier is also INVALID in a
        // defined-name body — Excel refuses the whole file (0x800A03EC).
        // When the parent path names a sheet (add /Sheet1 --type namedrange),
        // qualify with it; otherwise the ref is ambiguous and rejected.
        if (!refVal.Contains('!')
            && System.Text.RegularExpressions.Regex.IsMatch(refVal.Replace("$", ""),
                @"^[A-Za-z]{1,3}\d+(:[A-Za-z]{1,3}\d+)?$"))
        {
            var nrParentSheet = parentPath.TrimStart('/').Split('/', 2)[0];
            if (!string.IsNullOrEmpty(nrParentSheet)
                && !nrParentSheet.StartsWith("namedrange", StringComparison.OrdinalIgnoreCase)
                && !nrParentSheet.Equals("workbook", StringComparison.OrdinalIgnoreCase)
                && FindWorksheet(nrParentSheet) != null)

View on GitHub (pinned to 1ced45e900)

Solutions

1. Inline the external data into the current workbook first, then reference it locally (e.g. `ref=Sheet1!A1`).
2. If you truly need a cross-workbook link, use the raw-set escape hatch to author the externalLinks part yourself (as the message suggests).
3. Convert the external ref to a structured/table reference if the data is already local.

Example fix

// before
add ./book.xlsx /namedrange --type namedrange --prop name=X --prop ref=[Other.xlsx]Sheet1!A1
// after
add ./book.xlsx /namedrange --type namedrange --prop name=X --prop ref=Sheet1!A1

// Detect external/cross-workbook refs the same way the handler does.
var probe = refVal.TrimStart(' ','\t').TrimStart('\'');
if (Regex.IsMatch(probe, @"^\[(\d+|[^\]]*\.xls[xbm]?)\]", RegexOptions.IgnoreCase))
    throw new InvalidOperationException(
        $"Cross-workbook ref '{refVal}' needs an externalLinks part; inline the data or use raw-set");

static bool IsCrossWorkbookRef(string? s)
{
    if (string.IsNullOrEmpty(s)) return false;
    var probe = s.TrimStart(' ','\t').TrimStart('\'');
    return System.Text.RegularExpressions.Regex.IsMatch(
        probe, @"^\[(\d+|[^\]]*\.xls[xbm]?)\]", RegexOptions.IgnoreCase);
}

try { handler.AddNamedRange(...); }
catch (ArgumentException ex) when (ex.Message.Contains("Cross-workbook"))
{ /* route the user to raw-set or inline the data */ }

When it happens

Trigger: Passing `--prop ref=[Other.xlsx]Sheet1!A1`, `--prop ref=[1]Sheet1!A1`, or the quoted form `--prop ref='[Book.xlsx]Sheet'!A1`. Using `Table1[Price]` does NOT trigger this (it is a valid structured reference).

Common situations: Copying a defined name from a workbook that linked to another file; consolidating workbooks but keeping external links; paste from Excel's formula bar where external refs are common.

Related errors

• 'name' property is required for namedrange
• Invalid defined-name '{nrName}': must start with a letter/un
• Invalid defined-name '{nrName}': length {nrName.Length} exce
• Invalid defined-name '{nrName}': name parses as a cell refer
• Invalid defined-name '{nrName}': single letter 'R' / 'C' is

AI-assisted analysis of iOfficeAI/OfficeCLI@1ced45e900 (2026-08-13). Data as JSON: /api/errors/addb1341c5a22823. Report an issue: GitHub.