none
Excel 2007 Interop crashes when renaming and then copying a sheet with some combination of Pivot Chart/Pivot Table/Slicers in Excel 2010 RRS feed

  • Question

  • The Excel 12.0 version of Interop crashes when renaming and then copying a sheet with some combination of Pivot Chart/Pivot Table/Slicers. I have attached a quick C# example and sample Excel file that replicates this crash, which occurs only when run against Excel 2010, not Excel 2013. We must use the Excel 12 interop, as we need to support Excel 2007 and higher in our application.

    The attached example doesn’t use the interop in an unusual way – it just mimics the steps that a user would make performing the same action (which does not crash Excel or generate any errors).

    Is there a hotfix or some other resolution for this issue?

    Sample Workbook: https://www.dropbox.com/s/4hsfubo4m4x2ggs/Sample%20Pivot%20Chart.xlsx?dl=0

        public static void TriggerExcel2010Bug()
        {
            string fileLocation = AppDomain.CurrentDomain.BaseDirectory + @"Sample Pivot Chart.xlsx";
    
            // Open the interop
            Application xlApplication = new Application();
    
            // Open our example workbook, and select our example worksheet
            Workbook workbook = xlApplication.Workbooks.Open(fileLocation);
            Worksheet worksheetWithCharts = workbook.Worksheets["Sales Analysis"];
            xlApplication.Visible = true;
    
            // Rename the sheet to a new name
            worksheetWithCharts.Name = "a new name";
    
            // Copy. Code will fail here in Excel 2010.
            worksheetWithCharts.Copy(workbook.Worksheets[1], Type.Missing);
    
            // Clean up
            workbook.Close(false, Type.Missing, Type.Missing);
            xlApplication.Quit();
        }



    Monday, August 24, 2015 7:57 PM

Answers

  • >>>The Excel 12.0 version of Interop crashes when renaming and then copying a sheet with some combination of Pivot Chart/Pivot Table/Slicers.

    According to your description, I have reproduced your issue with you provided sample codes and get this exception "Unable to get the Copy property of the Worksheet class c#".
    I find out that this issue is caused by that you have renamed worksheetWithCharts's name before you copied worksheet in the Excel 12.0 version of Interop.
    So I suggest that you could change your code like below:

    string fileLocation = AppDomain.CurrentDomain.BaseDirectory + @"Sample Pivot Chart.xlsx";
    
    // Open the interop
    Application xlApplication = new Application();
    xlApplication.Visible = false;
    
    // Open our example workbook, and select our example worksheet
    Workbook workbook = xlApplication.Workbooks.Open(fileLocation);
    Worksheet worksheetWithCharts = workbook.Worksheets["Sales Analysis"];
    xlApplication.Visible = true;
    
    // Copy. Code will fail here in Excel 2010.
    worksheetWithCharts.Copy(workbook.Worksheets[1], Type.Missing);
    
    worksheetWithCharts.Name = "a new name";
    

    or add Microsoft.Office.Interop.Excel version 14.0.0.0 to references

    1.In Solution Explorer, right-click your project's name and then click Add Reference. The Add Reference dialog box appears.
    2.On the Assemblies tab, select Microsoft.Office.Interop.Excel, version 14.0.0.0 (version 14.0.0.0 for Excel 2010,version 12.0.0.0 for Excel 2007)

    Thursday, August 27, 2015 6:52 AM