iOfficeAI/OfficeCLI · error · ArgumentException

External workbook references are not supported in pivot sour

Error message

External workbook references are not supported in pivot source. Use a local sheet name (e.g. Sheet1!A1:D10)

What it means

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.

Source

Thrown at src/officecli/Core/PivotTableHelper.Readback.cs:375

    }

    /// <summary>
    /// R10-1: refresh a pivot's cache definition + records from a new source
    /// range spec ("Sheet1!A1:C4" or "A1:C4" — same sheet as the existing
    /// CacheSource). Replaces CacheFields, updates WorksheetSource.Reference
    /// (and Sheet if changed), rewrites the PivotTableCacheRecordsPart, and
    /// resizes pivotDef.PivotFields to match the new column count. Existing
    /// PivotField Axis/DataField assignments are reset because indices may no
    /// longer line up — RebuildFieldAreas reapplies them after this returns.
    /// </summary>
    private static void RefreshPivotCacheFromSource(PivotTablePart pivotPart, string newSourceSpec,
        Dictionary<string, string>? pendingFieldAreaProps = null)
    {
        if (string.IsNullOrWhiteSpace(newSourceSpec))
            throw new ArgumentException("source must not be empty");
        newSourceSpec = newSourceSpec.Trim();
        if (newSourceSpec.StartsWith("["))
            throw new ArgumentException(
                "External workbook references are not supported in pivot source. "
                + "Use a local sheet name (e.g. Sheet1!A1:D10)");

        var cachePart = pivotPart.GetPartsOfType<PivotTableCacheDefinitionPart>().FirstOrDefault()
            ?? throw new InvalidOperationException("Pivot table has no cache definition part");
        var cacheDef = cachePart.PivotCacheDefinition
            ?? throw new InvalidOperationException("Pivot cache definition is missing");
        var existingWsSource = cacheDef.CacheSource?.WorksheetSource
            ?? throw new InvalidOperationException("Pivot cache source is not a worksheet source");

        // Parse the new source spec.
        string newSheetName;
        string newRef;
        if (newSourceSpec.Contains('!'))
        {
            var parts = newSourceSpec.Split('!', 2);
            newSheetName = parts[0].Trim().Trim('\'', '"').Trim();
            newRef = parts[1].Trim();

View on GitHub (pinned to 1ced45e900)

Solutions

1. Move the source data into a local sheet of the current workbook and reference it: source=Sheet1!A1:D10
2. If the data must stay external, import it into the workbook first, then refresh the pivot against the local copy
3. Strip any leading [book] prefix from the spec before submitting

Example fix

// before
source="[Book1.xlsx]Sheet1!A1:D10"
// after (data imported into current workbook first)
source="Sheet1!A1:D10"

if (sourceSpec.TrimStart().StartsWith("["))
    throw new InvalidOperationException("External workbook sources are not supported; import the data locally first");

static bool IsLocalSourceSpec(string s) => !s.TrimStart().StartsWith("[");

try { RefreshPivotCacheFromSource(pivotPart, sourceSpec); }
catch (ArgumentException ex) when (ex.Message.Contains("External workbook references"))
{ /* import the data locally, then retry with a local sheet reference */ }

When it happens

Trigger: 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.

Common situations: 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.

Related errors

• source must not be empty
• calculatedField requires a non-empty name
• calculatedField '{name}' requires a non-empty formula
• calculatedField '{name}' collides with an existing field nam
• 'calculatedFields' must be a JSON array

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