VB.NET "Copy method of Sheets class failed"

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
  HelpLink="C:\Program Files\Microsoft Office\Office12\1033\XLMAIN11.CHM"
  Message="Copy method of Sheets class failed"
  Source="Microsoft Office Excel"
       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()

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

    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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John (Yiannis) ToutountzoglouInstructor Multiengine PilotCommented:
read this
there is a code for copying excel workbooks
John (Yiannis) ToutountzoglouInstructor Multiengine PilotCommented:
Try to Change
owb.Worksheets.Copy(oTargetWb.Worksheets, ) with oBook.Worksheets.Copy After:=oTargetWb.Worksheets

common error
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

John (Yiannis) ToutountzoglouInstructor Multiengine PilotCommented:
do not forget to clean up after Copy

    xlsBook1 = Nothing
    xlsBook2 = Nothing

Open in new window

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

John (Yiannis) ToutountzoglouInstructor Multiengine PilotCommented:
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

            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 = Nothing
            System.Threading.Thread.CurrentThread.CurrentCulture = _
           New System.Globalization.CultureInfo("en-US")
            System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
            xlsApp = Nothing

        End Try
    End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John (Yiannis) ToutountzoglouInstructor Multiengine PilotCommented:
Also in namespace
Imports Excel = Microsoft.Office.Interop.Excel
Imports Office = Microsoft.Office.Core
philkeeneAuthor Commented:

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?

John (Yiannis) ToutountzoglouInstructor Multiengine PilotCommented:
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...
philkeeneAuthor Commented:
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'
philkeeneAuthor Commented:
I have now added the 'Microsoft Excel 12.0 Object Library' and it seems to have got rid of all errors.
John (Yiannis) ToutountzoglouInstructor Multiengine PilotCommented:
does it work?
philkeeneAuthor Commented:
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.
philkeeneAuthor Commented:
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...
John (Yiannis) ToutountzoglouInstructor Multiengine PilotCommented:
very welcome!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.