Copy method failed

I have create a excel project using VSTO in .net 2005. It retrieves data from a web service.



I have a project template sheet, which has a list object on it. As I pull a dataset from the web service, it places the data in the list object. Then I take the project template sheet copy it and then rename it to the current project’s dataset.

I get a copy method failed after I have loaded approximately 100 sheets in the workbook. I have set up a button that auto loads the projects from list box.

This the error:

“System.Runtime.InterlopServices.COMException(0x800A03EC):Copy Method of worksheet class failed”



I have been searching and searching and I haven’t found the answer...

Here is a sample of my code:





currentListObject = Globals.ProjectTemplate.List1

currentListObject.AutoSetDataBoundColumnHeaders = False

currentListObject.SetDataBinding(dsData.Tables(0))

currentListObject.ShowTotals = False

currentListObject.Disconnect()







With Globals.ProjectTemplate

.Visible = Excel.XlSheetVisibility.xlSheetVisible

.Copy(, Globals.ThisWorkbook.Worksheets(1))

'.Copy(Globals.ProjectTemplate.InnerObject, ) ß I tried the method on MSDN and it failed(http://msdn2.microsoft.com/en-us/lib...heet.copy.aspx)

.Visible = Excel.XlSheetVisibility.xlSheetHidden

End With



Dim newSheet As Excel.Worksheet = DirectCast(Globals.ThisWorkbook.Sheets(2), Excel.Worksheet)

Are you sure that you aren't just running out of memory at the time the project errors out? I was looking and it seems that theres conflicting reports of the maximum number of worksheets in a workbook. Some say 255, others say it is limited by the amount of memory that is available. Maybe its a memory problem? I don't see why it would copy over 100 fine and then just error out if it was anything else. Even more so when you have the .NET IDE running while debugging, as that would tend to consume more memory than usual...

the copy method begins to fail before i hit any memory issues arise. it puts the new dataset return in the list box with out evey coping the worksheet and renaming it.

I haven't worked a lot with datasets. But shouldn't the statement

currentListObject.Disconnect()

be after you have copied into excel?

I forgot to copy the piece of code, I do disconnect the dataset.

But I did create a sample project without the dataset, but I had it copy a excel worksheet with "junk" data in the list box and copy method failed after about 200 sheets being added.

Now if I don’t have list box on the sheet, it will put in over 500 sheets into the workbook. So I know I am not running out of sheet in the workbook.

Well are these listboxes filled with data? Sounds even more like a memory problem to me, since the less items you have in the sheets, the more sheets you can fill...

The clipboard could be storing your previous copies, thus increasing memory size with each copy. See if setting clipboard contents to "nothing" after completion of each copy command or before each copy command helps - just a shot in the dark.

i have modified the code and cleared the clipboard after each copy method and it looks like it has helped... no cpy method failed but now i run out of memeory after about 116 sheets.... that is an issue the actaully excel limitations?