{"record":{"id":"1d3a12a25aa9a5bf","repo":"iOfficeAI/OfficeCLI","slug":"external-workbook-references-are-not-supported-in","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/Core/PivotTableHelper.Readback.cs","lineNumber":375,"sourceCode":"    }\n\n    /// <summary>\n    /// R10-1: refresh a pivot's cache definition + records from a new source\n    /// range spec (\"Sheet1!A1:C4\" or \"A1:C4\" — same sheet as the existing\n    /// CacheSource). Replaces CacheFields, updates WorksheetSource.Reference\n    /// (and Sheet if changed), rewrites the PivotTableCacheRecordsPart, and\n    /// resizes pivotDef.PivotFields to match the new column count. Existing\n    /// PivotField Axis/DataField assignments are reset because indices may no\n    /// longer line up — RebuildFieldAreas reapplies them after this returns.\n    /// </summary>\n    private static void RefreshPivotCacheFromSource(PivotTablePart pivotPart, string newSourceSpec,\n        Dictionary<string, string>? pendingFieldAreaProps = null)\n    {\n        if (string.IsNullOrWhiteSpace(newSourceSpec))\n            throw new ArgumentException(\"source must not be empty\");\n        newSourceSpec = newSourceSpec.Trim();\n        if (newSourceSpec.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        var cachePart = pivotPart.GetPartsOfType<PivotTableCacheDefinitionPart>().FirstOrDefault()\n            ?? throw new InvalidOperationException(\"Pivot table has no cache definition part\");\n        var cacheDef = cachePart.PivotCacheDefinition\n            ?? throw new InvalidOperationException(\"Pivot cache definition is missing\");\n        var existingWsSource = cacheDef.CacheSource?.WorksheetSource\n            ?? throw new InvalidOperationException(\"Pivot cache source is not a worksheet source\");\n\n        // Parse the new source spec.\n        string newSheetName;\n        string newRef;\n        if (newSourceSpec.Contains('!'))\n        {\n            var parts = newSourceSpec.Split('!', 2);\n            newSheetName = parts[0].Trim().Trim('\\'', '\"').Trim();\n            newRef = parts[1].Trim();","sourceCodeStart":357,"sourceCodeEnd":393,"githubUrl":"https://github.com/iOfficeAI/OfficeCLI/blob/1ced45e900782c5083ed550ddf328ee974e425e7/src/officecli/Core/PivotTableHelper.Readback.cs#L357-L393","documentation":"RefreshPivotCacheFromSource detects an external workbook reference by the leading '[' (Excel's external-link syntax, e.g. [Book1.xlsx]Sheet1!A1:D10) and rejects it up front. The helper only knows how to read source data from a worksheet inside the same package; following an external link would require resolving another file, which is not supported. The message points the user at the local-sheet form.","triggerScenarios":"source=[Book1.xlsx]Sheet1!A1:D10; source=[1]Sheet1!A1:D10 (indexed external reference); copy-paste of a formula from Excel that referenced another workbook.","commonSituations":"User expects the tool to follow cross-workbook links; migrating a pivot whose original source was in a different file; exported range strings that include the source filename.","solutions":["Move the source data into a local sheet of the current workbook and reference it: source=Sheet1!A1:D10","If the data must stay external, import it into the workbook first, then refresh the pivot against the local copy","Strip any leading [book] prefix from the spec before submitting"],"exampleFix":"// before\nsource=\"[Book1.xlsx]Sheet1!A1:D10\"\n// after (data imported into current workbook first)\nsource=\"Sheet1!A1:D10\"","handlingStrategy":"validation","validationCode":"if (sourceSpec.TrimStart().StartsWith(\"[\"))\n    throw new InvalidOperationException(\"External workbook sources are not supported; import the data locally first\");","typeGuard":"static bool IsLocalSourceSpec(string s) => !s.TrimStart().StartsWith(\"[\");","tryCatchPattern":"try { RefreshPivotCacheFromSource(pivotPart, sourceSpec); }\ncatch (ArgumentException ex) when (ex.Message.Contains(\"External workbook references\"))\n{ /* import the data locally, then retry with a local sheet reference */ }","preventionTips":["Import external data into the workbook before refreshing the pivot","Strip leading [book] prefixes from copied range strings","Prefer local sheet references of the form Sheet1!A1:D10"],"tags":["pivottable","cache-refresh","external-reference","argument-validation"],"backgroundTag":null,"analyzedSha":"1ced45e900782c5083ed550ddf328ee974e425e7","analyzedAt":"2026-08-13T13:01:07.193Z","schemaVersion":2},"datasetVersion":"2026-08-13T14:17:21.547Z"}