CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2006
    Posts
    27

    Question 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)

  2. #2
    Join Date
    Jan 2006
    Posts
    293

    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.

  3. #3
    Join Date
    Jan 2006
    Posts
    27

    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.

  4. #4
    Join Date
    Dec 2002
    Posts
    305

    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?

  5. #5
    Join Date
    Jan 2006
    Posts
    27

    Question 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.

  6. #6
    Join Date
    Jan 2006
    Posts
    293

    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...

  7. #7
    Join Date
    Dec 2002
    Posts
    305

    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.

  8. #8
    Join Date
    Jan 2006
    Posts
    27

    Smile 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
  •  





Click Here to Expand Forum to Full Width

Featured