{"record":{"id":"2e8ef2bafd4432b8","repo":"iOfficeAI/OfficeCLI","slug":"external-workbook-references-are-not-supported-in-2e8ef2","errorCode":null,"errorMessage":"External workbook references are not supported in pivot source. Use a local sheet name (e.g. Sheet1!A1:D10)","messagePattern":"External workbook references are not supported in pivot source\\. Use a local sheet name \\(e\\.g\\. Sheet1!A1:D10\\)","errorType":"validation","errorClass":"ArgumentException","httpStatus":null,"severity":"error","filePath":"src/officecli/Handlers/Excel/ExcelHandler.Add.Tables.cs","lineNumber":1683,"sourceCode":"            ?? properties.GetValueOrDefault(\"src\", \"\")\n            ?? throw new ArgumentException(\"pivottable requires 'source' property (e.g. source=Sheet1!A1:D100)\");\n        if (string.IsNullOrEmpty(sourceSpec))\n            throw new ArgumentException(\"pivottable requires 'source' property (e.g. source=Sheet1!A1:D100)\");\n\n        // R8-7: incidental whitespace around the source spec or its\n        // components (\" Sheet1 ! A1:D10 \") is a common paste-from-docs\n        // artefact. Trim the whole string and both sides of the '!'\n        // split so the downstream sheet/range lookup sees clean values.\n        sourceSpec = sourceSpec.Trim();\n\n        // R8-3: external workbook refs such as [other.xlsx]Sheet1!A1:D10\n        // used to fall through to FindWorksheet and surface as the\n        // misleading \"Source sheet not found: [other.xlsx]Sheet1\".\n        // Detect the '[' prefix up front and throw a clear error so\n        // users know the feature is not supported rather than blaming\n        // a missing sheet.\n        if (sourceSpec.StartsWith(\"[\"))\n            throw new ArgumentException(\n                \"External workbook references are not supported in pivot source. \"\n                + \"Use a local sheet name (e.g. Sheet1!A1:D10)\");\n\n        string sourceSheetName;\n        string sourceRef;\n\n        // B6 v2: try resolving structured-table refs (Table1[#All]) and\n        // workbook/sheet-scoped defined names (SalesData, Sheet1!SalesData)\n        // into an explicit (sheet, range) tuple BEFORE the literal-parse\n        // path. Falls through to the literal parser for explicit\n        // \"Sheet1!A1:C5\" specs and any form the resolver doesn't recognize.\n        // See PivotTableHelper.Cache.cs ResolvePivotSourceSpec for coverage.\n        var resolved = OfficeCli.Core.PivotTableHelper.ResolvePivotSourceSpec(\n            _doc.WorkbookPart!, sourceSpec, defaultSheet: ptSheetName);\n        if (resolved.HasValue)\n        {\n            sourceSheetName = resolved.Value.sheet;\n            sourceRef = resolved.Value.rangeRef;","sourceCodeStart":1665,"sourceCodeEnd":1701,"githubUrl":"https://github.com/iOfficeAI/OfficeCLI/blob/1ced45e900782c5083ed550ddf328ee974e425e7/src/officecli/Handlers/Excel/ExcelHandler.Add.Tables.cs#L1665-L1701","documentation":"Thrown by AddPivotTable's R8-3 guard when the source spec begins with '['. Bracket-prefixed specs like [other.xlsx]Sheet1!A1:D10 denote external workbook references, which OfficeCLI does not support for pivot sources. Without this guard the spec would fall through to FindWorksheet and surface as a misleading 'Source sheet not found: [other.xlsx]Sheet1' error.","triggerScenarios":"Calling Add('/Report/pivottable', ...) with source=[data.xlsx]Sheet1!A1:D10 or any spec starting with '['.","commonSituations":"Copying a reference from Excel's formula bar (which externalizes refs with brackets), or pointing the pivot at data in another workbook.","solutions":["Move or copy the source data into a local sheet in the same workbook and reference it directly, e.g. source=Sheet1!A1:D10.","If the external workbook is required, first import its data into the target workbook.","Confirm the spec uses a plain local sheet name with no leading bracket."],"exampleFix":"// before\nadd /Report/pivottable --prop source=[data.xlsx]Sheet1!A1:D10\n// after (data copied into same workbook)\nadd /Report/pivottable --prop source=Data!A1:D10","handlingStrategy":"validation","validationCode":"// Reject external (bracket-prefixed) pivot sources before Add.\nvar src = (props.GetValueOrDefault(\"source\") ?? props.GetValueOrDefault(\"src\") ?? \"\").Trim();\nif (src.StartsWith(\"[\"))\n    throw new InvalidOperationException(\"External workbook references are not supported in pivot source.\");","typeGuard":"static bool IsExternalRef(string src) => src.TrimStart().StartsWith(\"[\");","tryCatchPattern":"try { handler.Add(parentPath, \"pivottable\", null, props); }\ncatch (ArgumentException ex) when (ex.Message.Contains(\"External workbook references are not supported\"))\n{ /* import the external data into the workbook, then reference it locally */ }","preventionTips":["Never paste Excel formula-bar external refs (with brackets) into source.","Import external data into a local sheet first when cross-workbook pivots are needed.","Strip brackets in any source-value preprocessor and warn the user."],"tags":["excel","pivot-table","validation","external-reference","unsupported-feature"],"backgroundTag":null,"analyzedSha":"1ced45e900782c5083ed550ddf328ee974e425e7","analyzedAt":"2026-08-13T13:01:07.193Z","schemaVersion":2},"datasetVersion":"2026-08-13T14:17:21.547Z"}