Why Excel.WorkSheet.Copy throws exception of A first chance exception of type 'System.Runtime.InteropServices.COMException' with HRESULT: 0x800A03EC

0
votes

I am playing around with Excel Interop in .NET where I am encountering COMException over "Excel._WorkSheet.Copy(Type.Missing,Type.Missing)".

The behavior is very strange : I am copying worksheets from a template based .xls and generating a new workbook with specified worksheets( probably selected by user from GUI ). As soon as it encounters Excel._Worksheet WorkSheet.Copy(Type.Missing,Type.Missing), it throws following COMException.

"A first chance exception of type 'System.Runtime.InteropServices.COMException' occurred.

Additional information: Exception from HRESULT: 0x800A03EC"

After experimenting with different way, I copied manually specified worksheet to another newly created instance of Excel and given this workbook as source to the same code. Here, it worked fine! It created an exact copy by opening a new excel instance and I had no exception at all.

I don't know why this behavior is occuring? Is it related to the contents inside that worksheet, If so, same and exact worksheet is being copied to another newly created blank worksheet and everything worked fine.

I have to produce reports for the specified worksheets given by the user, So I have to open the templated workbook from where, I need to copy those specified worksheets and later those worksheets would be filled in with the specified data.

I am using the following code : 

        Excel._Worksheet WorkSheet = null;

        Excel.Application excel = new Excel.Application();
        excel.Visible = true;
        excel.DisplayAlerts = false;

       _ExcelWorkbook = excel.Workbooks.Open(a_WorkbookName, Type.Missing,         Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,true);

        WorkSheet = (Excel.Worksheet)_ExcelWorkbook.Sheets[1];
        WorkSheet.Copy(Type.Missing, Type.Missing); // throws COM Exception of 0x800A03EC.

Regards Usman

1
c#excelcomexcel-interop
Google "excel error 1004 worksheet copy" to find hits. – Hans Passant

1 Answers

2
votes

I believe you need to declare a source and then a destination. It appears you are only declaring a source.

Excel.Worksheet wksh1 = ((Excel.Worksheet)Application.ActiveWorkbook.Worksheets[1]);
Excel.Worksheet wksh2 = ((Excel.Worksheet)Application.ActiveWorkbook.Worksheets[2]);
wksh1.Copy(wksh2);

EDIT:

Application.ActiveWorkbook is only an example of a workbook object that can be used in this code pattern. It is essentially a 'this' pointer for Excel in that it is the workbook in focus, but 'ThisWorkbook' references the workbook that is currently running VBA code so 'ActiveWorkbook' is the syntax. See spec here and here for more on the Workbook interface.

To target other workbooks, you can declare the specific workbook you want.

Excel.Application excel = new Excel.Application();
excel.xlWorkbook = excel.Workbooks.Add(1);
Workbook newWorkbook = excel.Workbooks[1];
Workbook oldWorkbook = excel.Workbooks.Open(fileLocation);
Excel.Worksheet newWorksheet = ((Excel.Worksheet)newWorkbook.Worksheets[1]);
Excel.Worksheet oldWorksheet = ((Excel.Worksheet)oldWorkbook.Worksheets[1]);
oldWorksheet.Copy(newWorksheet);