?
Solved

VB.NET "Copy method of Sheets class failed"

Posted on 2010-08-16
15
Medium Priority
?
762 Views
Last Modified: 2013-11-27
When I try to copy a worksheet from one excel file to another, VB.NET is giving me the error... "Copy method of Sheets class failed". I have tried a number of supposed solutions that are available online, and each of them has failed. The full error code is...

System.Runtime.InteropServices.COMException was unhandled
  ErrorCode=-2146827284
  HelpLink="C:\Program Files\Microsoft Office\Office12\1033\XLMAIN11.CHM"
  Message="Copy method of Sheets class failed"
  Source="Microsoft Office Excel"
  StackTrace:
       at Microsoft.VisualBasic.CompilerServices.LateBinding.InternalLateCall(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack, Boolean IgnoreReturn)
       at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateCall(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean IgnoreReturn)
       at WindowsApplication3.Form1.Button1_Click(Object sender, EventArgs e) in C:\Documents and Settings\User\My Documents\Visual Studio 2008\Projects\WindowsApplication3\WindowsApplication3\Form1.vb:line 10
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(ApplicationContext context)
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
       at WindowsApplication3.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
       at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)
       at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly()
       at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)
       at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext)
       at System.Activator.CreateInstance(ActivationContext activationContext)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: 

Open in new window


I have simplified the code as much as possible, and so I have the following...

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim xl = CreateObject("Excel.Application")
        'specify the source workbook
        Dim owb = xl.Workbooks.Open("C:\test\File1.xlsx")
        'specify the target workbook
        Dim oTargetWb = xl.Workbooks.Open("C:\test\File2.xlsx")
        owb.Worksheets.Copy(oTargetWb.Worksheets, )
        oTargetWb.Save()
        xl.Quit()

    End Sub
End Class

Open in new window


The project consist of a simple windows form with a button in it named 'Button1'. There is a folder on my c: drive called test that contains 2 files 'File1.xlsx' and 'File2.xlsx'.

I am using Visual Studio 2008, I have office pro 2007 on the PC, and I have compiled the program and installed it on another PC and the same error happened.

Any help would be greatly appreciated, this one has been vexing me for some time.
0
Comment
Question by:philkeene
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
15 Comments
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33450524
hi
read this
there is a code for copying excel workbooks
http://support.microsoft.com/kb/210684/en-us
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33450542
Try to Change
owb.Worksheets.Copy(oTargetWb.Worksheets, ) with oBook.Worksheets.Copy After:=oTargetWb.Worksheets

common error
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33450649
do not forget to clean up after Copy

'CLEAN UP
    xlsApp1.Workbooks.Close()
    xlsBook1 = Nothing
    xlsApp1.Quit()
    xlsApp2.Workbooks.Close()
    xlsBook2 = Nothing
    xlsApp2.Quit()
    GC.Collect()

Open in new window

0
 
LVL 1

Expert Comment

by:Madian
ID: 33450823
I think you can use something like a template creation for the worksheet base and use this template to work whit it, as i understand you can do something like this, and avoid to copy a worksheet

xls.Workbooks.Add App.Path & "\Template\MyTemplate
you can change App.Path to your desired path with a file selection dialog to customize
MyTemplate is your template, and i think you can use this with a worksheet to


0
 
LVL 18

Accepted Solution

by:
John (Yiannis) Toutountzoglou earned 2000 total points
ID: 33454055
Try this ..seems it is working
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        
        Dim xlsApp As New Excel.Application

        Try
            System.Threading.Thread.CurrentThread.CurrentCulture = _
               New System.Globalization.CultureInfo("en-US")


            'specify the source workbook
            Dim owb = xlsApp.Workbooks.Open("YourPath\Book1.xlsx")
            xlsApp.Visible = False
            'specify the target workbook
            Dim oTargetWb = xlsApp.Workbooks.Open("YourPath\Book2.xlsx")

            owb.Sheets("Sheet1").Copy(After:=oTargetWb.Sheets(1))
            oTargetWb.Save()
            xlsApp.Quit()
            
            xlsApp.Workbooks.Close()
            owb = Nothing
            xlsApp.Quit()
            GC.Collect()
            System.Threading.Thread.CurrentThread.CurrentCulture = _
           New System.Globalization.CultureInfo("en-US")
        Finally
            'RELEASE ALLOACTED RESOURCES
            System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
            xlsApp = Nothing

        End Try
    End Sub

Open in new window

0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33454151
Also in namespace
Imports Excel = Microsoft.Office.Interop.Excel
Imports Office = Microsoft.Office.Core
0
 
LVL 4

Author Comment

by:philkeene
ID: 33456596
Hi,

Thanks so much for all your help, unfortunately it still doesn't seem to be working. I'm starting to wonder if there is something wrong with the installation of VS 2008 on this PC.

When I change the copy code to...

oBook.Worksheets.Copy After:=oTargetWb.Worksheets

It automatically puts brackets around the After:=oTargetWb.Worksheets

and then crashes at the same point in the same way.

Using your code jtoutou, the compiler won't even run. When I hover the mouse over the 'Imports' lines it says... "Namespace or type specified in the Imports 'Microsoft.Office.Interop.Excel' doesn't contain any public member or cannot be found. Make sure the namespace or the type is defined and contains at least on public member. Make sure the imported element name doesn't use any aliases."

Any ideas?

0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33456658
i tried also in another pc ....excel crashes but the it copies the sheet correctly after manually open the excel workbook...Strange ...I am still looking on it...
0
 
LVL 4

Author Comment

by:philkeene
ID: 33456720
I have done a quick search, and following the advice of others, I have gone to...

Project -> Add reference...

COM tab

And double clicke 'Microsoft Office 12.0 Object Library' ... this has got rid of the error over the following line...

'Imports Office = Microsoft.Office.Core'
0
 
LVL 4

Author Comment

by:philkeene
ID: 33456727
I have now added the 'Microsoft Excel 12.0 Object Library' and it seems to have got rid of all errors.
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33456747
does it work?
0
 
LVL 4

Author Comment

by:philkeene
ID: 33456771
This is fantastic... thanks jtoutou, I'm pretty sure we are almost there. I've run your code and it has errored with invalid index, so I'm going to have a play, and see if I can get it working from here.

You have more than earned your points on this one... I'll upload the final code if I get it working or I will link to the new question if I don't.

Thanks for all your help.
0
 
LVL 4

Author Comment

by:philkeene
ID: 33456820
Excellent your code was spot on... I didn't have a "sheet1" in the excel file, which is why it wasn't working. This has now worked. I am thoroughly embarassed to say how long this has taken me to solve, so thank you so much... I may be able to get some sleep tonight...

Glad I joined Experts Exchange...
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33456840
very welcome!!
0

Featured Post

PowerShell Core for Advanced Linux Administrators

Understand advanced principals around Powershell Core with a focus on the Linux Administrator.  This course covers how to administer numerous environments across multiple platforms including Linux, Azure, AWS, and Google Cloud from a single shell instance.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question