Geek asks and answers

C # Excel VSTO - Is it possible to move a variable?

I am trying to move a pivot table to VSTO and not succeed at all. My logic was to find the range of the pivot table, cut it out and paste it into a new range, where I am sure that there is no data on the sheet.

    public static void MovePivotTable(string sheetName, PivotTable pivotTable, int newX, int newY, int width, int height)
    {
        try
        {
            Worksheet worksheet = GetOrCreateWorksheet(sheetName);

            Range topLeft = (Range)worksheet.Cells[newX, newY];
            Range bottomRight = (Range)worksheet.Cells[newX + width, newY + height];
            Range newRange = worksheet.get_Range(topLeft, bottomRight);

            pivotTable.TableRange1.Cut(Missing.Value);

            newRange.PasteSpecial(XlPasteType.xlPasteAll, XlPasteSpecialOperation.xlPasteSpecialOperationNone,
                Missing.Value, Missing.Value);

            return;
        }
        catch (Exception)
        {
        }
        finally
        {
        }
    }

However, I always get an exception. Or: - PasteSpecial error. - Something along the lines that it is impossible to change the pivot table.

Has anyone ever done this? Can they confirm that this is really possible or not? Any sample code?

Thanks a lot sean

+2
source share
1 answer

Two things:

TableRange1 does not include the Pivot Table header, so you get a "Can not Modify" error. Use TableRange2 to select the entire pivot table.

, PasteSpecial Cut, Destination Cut. VB:

pivotTable.TableRange2.Cut Destination:=NewRange
+2

More articles:


All Articles