none
Excel Pivottable (new sheet) using Pivot Table Wizard RRS feed

  • Question

  • Hey guys,

    I'm trying to create a pivot table using the Pivot Table Wizard . I want the pivot table to be on an extra sheet. Not on the same one as the one containing the data. The data is arranged in a simple excel Data Table. The first line (A1 ... Something1) contains the Name of the columns.

    The following code is what I tried to use:

                var xlApplication = new Excel.Application();
                Excel.Workbooks wbooks = xlApplication.Workbooks;
                wbooks.Open(filePath);
                Excel.Workbook xlWbook = xlApplication.ActiveWorkbook;
                
                var xlWorksheets = xlApplication.Worksheets;
                
                Excel.Worksheet xlTargetWorksheet;
                xlTargetWorksheet = (Excel.Worksheet) xlWorksheets.Add();
                
                Excel.Worksheet xlDataWorksheet;
                xlDataWorksheet = (Excel.Worksheet)xlWorksheets[1];
                
                Excel.Range pivotData;            
                pivotData = (Excel.Range) xlDataWorksheet.Range["A2","B200"];
                
                Excel.Range pivotTarget;
                pivotTarget = xlTargetWorksheet.UsedRange;

                xlWbook.PivotTableWizard(
                    SourceType: Excel.XlPivotTableSourceType.xlDatabase,
                    SourceData: pivotData, 
                    TableDestination: pivotTarget,
                    TableName: "Bla",
                    RowGrand: true,
                    ColumnGrand: true,
                    SaveData: true,
                    HasAutoFormat: true,
                    BackgroundQuery: true);

    It seems that i did not get what the "Range" was supposed to do.

    BR

    Monday, August 10, 2015 4:59 PM

Answers

  • Hi thanks for your reply.

    I fixed my problem. Used a console app.

    The usedRange did not work for a reason. --> no idea why.

    I changed the code slightly:

                var xlApplication = new Excel.Application();
                var xlWbooks = xlApplication.Workbooks;
                xlWbooks.Open(Filename: filePath, AddToMru: true);
                var xlWbook = xlWbooks[1];

                Excel.Worksheet xlDataSheet = xlWbook.Sheets[1];
                Excel.Worksheet xlTargetSheet = xlWbook.Sheets.Add(After: xlApplication.ActiveSheet);
                xlTargetSheet.Name = "MySheet";

                Excel.Range pivotData = xlDataSheet.Cells.CurrentRegion;
                Excel.Range pivotTarget = xlTargetSheet.Cells.CurrentRegion;


                xlWbook.PivotTableWizard(
                    SourceType: Excel.XlPivotTableSourceType.xlDatabase,
                    SourceData: pivotData,
                    TableDestination: pivotTarget,
                    RowGrand: true,
                    ColumnGrand: true,
                    SaveData: true,
                    HasAutoFormat: true,
                    BackgroundQuery: true);

    BR

    • Marked as answer by Bonexy Tuesday, August 11, 2015 8:15 AM
    Tuesday, August 11, 2015 8:15 AM

All replies

  • Hi BR,

    What type application/project that you used? Excel add-in project, console project or others.

    Base on my test with console project, it works fine. Please save current workbook (xlWbook.Save()).

    If you are using the excel add-in project, please get excel application object by using this code: var xlApplication=this.Application.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, August 11, 2015 7:49 AM
    Moderator
  • Hi thanks for your reply.

    I fixed my problem. Used a console app.

    The usedRange did not work for a reason. --> no idea why.

    I changed the code slightly:

                var xlApplication = new Excel.Application();
                var xlWbooks = xlApplication.Workbooks;
                xlWbooks.Open(Filename: filePath, AddToMru: true);
                var xlWbook = xlWbooks[1];

                Excel.Worksheet xlDataSheet = xlWbook.Sheets[1];
                Excel.Worksheet xlTargetSheet = xlWbook.Sheets.Add(After: xlApplication.ActiveSheet);
                xlTargetSheet.Name = "MySheet";

                Excel.Range pivotData = xlDataSheet.Cells.CurrentRegion;
                Excel.Range pivotTarget = xlTargetSheet.Cells.CurrentRegion;


                xlWbook.PivotTableWizard(
                    SourceType: Excel.XlPivotTableSourceType.xlDatabase,
                    SourceData: pivotData,
                    TableDestination: pivotTarget,
                    RowGrand: true,
                    ColumnGrand: true,
                    SaveData: true,
                    HasAutoFormat: true,
                    BackgroundQuery: true);

    BR

    • Marked as answer by Bonexy Tuesday, August 11, 2015 8:15 AM
    Tuesday, August 11, 2015 8:15 AM