Solved

VB.NET "Copy method of Sheets class failed"

Posted on 2010-08-16
15
738 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
  • 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
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33450575
0
 
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 500 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

759 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now