-
August 8th, 2006, 06:58 PM
#1
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)
-
August 8th, 2006, 10:46 PM
#2
Re: Copy method failed
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...
Last edited by gigemboy; August 8th, 2006 at 10:53 PM.
-
August 9th, 2006, 11:53 AM
#3
Re: Copy method failed
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.
-
August 9th, 2006, 02:02 PM
#4
Re: Copy method failed
I haven't worked a lot with datasets. But shouldn't the statement
currentListObject.Disconnect()
be after you have copied into excel?
-
August 9th, 2006, 04:47 PM
#5
Re: Copy method failed
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.
-
August 9th, 2006, 10:05 PM
#6
Re: Copy method failed
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...
-
August 10th, 2006, 07:44 AM
#7
Re: Copy method failed
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.
-
August 10th, 2006, 04:47 PM
#8
Re: Copy method failed
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?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|