Link to home
Start Free TrialLog in
Avatar of Member_2_6009151
Member_2_6009151Flag for United Kingdom of Great Britain and Northern Ireland

asked on

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
  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.
Avatar of John (Yiannis) Toutountzoglou
John (Yiannis) Toutountzoglou
Flag of Greece image

hi
read this
there is a code for copying excel workbooks
http://support.microsoft.com/kb/210684/en-us
Try to Change
owb.Worksheets.Copy(oTargetWb.Worksheets, ) with oBook.Worksheets.Copy After:=oTargetWb.Worksheets

common error
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

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


ASKER CERTIFIED SOLUTION
Avatar of John (Yiannis) Toutountzoglou
John (Yiannis) Toutountzoglou
Flag of Greece image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Also in namespace
Imports Excel = Microsoft.Office.Interop.Excel
Imports Office = Microsoft.Office.Core
Avatar of Member_2_6009151

ASKER

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?

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...
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'
I have now added the 'Microsoft Excel 12.0 Object Library' and it seems to have got rid of all errors.
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.
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...