We help IT Professionals succeed at work.

Copy Worksheet from one Excel Workbook into a new Workbook

15,180 Views
Last Modified: 2010-05-18
I am using VB.NET in my web application and just need to open and exiting Excel file, copy content of the worksheet A, and paste it to a worksheet of another (new or existing file)

I found some code in an old answers in EE post Q_21474186
xlsApp1 = CreateObject("Excel.Application")
xlsBook1 = xlsApp1.Workbooks.Open(path1) 'path1 is set and known prior to this
xlsApp1.DisplayAlerts = False
xlsApp1.Visible = False 'False to perform operations without viewing excel.

xlsApp2 = CreateObject("Excel.Application")
xlsBook2 = xlsApp2.Workbooks.Open(path2) 'path2 is set and known prior to this
xlsApp2.DisplayAlerts = False
xlsApp2.Visible = False 'False to perform operations without viewing excel.

xlsApp1.ActiveWorkbook.Sheets(1).cells.copy()

xlsApp2.ActiveWorkbook.Sheets(3).paste()

xlsApp1.Workbooks.Close()
xlsApp1.Quit()
xlsApp2.Workbooks.Close()
xlsApp2.Quit()

When I run this I get follwing error:

System.Runtime.InteropServices.COMException: Paste method of Worksheet class failed

Please help me with this.

Thanks
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2015

Commented:
Well why dont u do this rather...

xlsApp1.ActiveWorkbook.Sheet1.copy after:=xlsApp2.ActiveWorkbook.Sheet2

Saurabh...
CERTIFIED EXPERT
Top Expert 2015

Commented:
Alternative method as well to do the same..

eFILE = "Your path" & "File name to open" &  ".xls"
Workbooks.Open Filename:=eFILE
y = ActiveWorkbook.Name
zFILE = "Your path" & "File name to open" &  ".xls"
Workbooks.Open Filename:=zFILE
z = ActiveWorkbook.Name

Workbooks(y).Activate
Sheets("Your Sheet Name").Copy after:=Workbooks(Z).Sheets("Sheet after which u want to move this sheet")
Windows(y).Close ([vbNo])
Windows(z).Close ([vbyes])

This will do what ur looking to do...

Author

Commented:
Thank you all for your time and help.
It is wierd how VB.NET is acting up on my code for Excel automation.

Saurabh726
If I use your first suggestion (to copy entire worksheet A from one Workbook to worksheet C in another workbook:

 xlsApp1.ActiveWorkbook.Worksheets("A").copy(after:=xlsApp2.ActiveWorkbook.Worksheets("C"))

I get this error:
Exception Details: System.Runtime.InteropServices.COMException: Copy method of Worksheet class failed

Error details:

[COMException (0x800a03ec): Copy method of Worksheet class failed]
   Microsoft.VisualBasic.CompilerServices.LateBinding.InternalLateCall(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack, Boolean IgnoreReturn) +798
   Microsoft.VisualBasic.CompilerServices.LateBinding.LateCall(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack) +23
   _10._0._0._144.BrokerRates.lkbtnRates_Click(Object sender, EventArgs e) in \\WEBTEST\c$\TestWebLock\BrokerRates.aspx.vb:220
   System.Web.UI.WebControls.LinkButton.OnClick(EventArgs e) +108
   System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +138
   System.Web.UI.Page.ProcessRequestMain() +1292

Any ideas how to fix it?
Thanks

Author

Commented:
Here is my entire code:

Dim xlsApp1 As Object
        Dim xlsBook1 As Object
        Dim xlsSheet1 As Object
        Dim oWS1 As Object
        Dim oRng1 As Object

        Dim xlsApp2 As Object
        Dim xlsBook2 As Object
        Dim xlsSheet2 As Object
        Dim oWS2 As Object
        Dim oRng2 As Object

        Dim sSampleFolder1 = "C:\TestWebLock\Rates\"
        xlsApp1 = CreateObject("Excel.Application")
        xlsApp1.Visible = False 'False to perform operations without viewing excel.
        'xlsBook1 = xlsApp1.Workbooks.Open(sSampleFolder1 & rateName & "_" & "T" & "_" & brok_code & ".xls")
        xlsBook1 = xlsApp1.Workbooks.Open("C:\TestWebLock\Rates\12_17_2007_01_T_899.xls")
        xlsApp1.DisplayAlerts = False
        xlsSheet1 = xlsBook1.Worksheets
        oWS1 = xlsSheet1.Item(1)
        oRng1 = oWS1.Range("C8")

       

        xlsApp2 = CreateObject("Excel.Application")
        'xlsBook2 = xlsApp2.Workbooks.Open(sSampleFolder1 & rateName & "_" & brok_code & ".xls")
        xlsBook2 = xlsApp2.Workbooks.Open("C:\TestWebLock\Rates\12_17_2007_01_899.xls")
        xlsApp2.DisplayAlerts = False
        xlsApp2.Visible = False 'False to perform operations without viewing excel.
        xlsSheet2 = xlsBook2.Worksheets
        oWS2 = xlsSheet2.Item(1)
        oRng2 = oWS2.Range("A1")

        'oRng1.Copy(oRng2)



        xlsApp1.ActiveWorkbook.Worksheets("A").copy(after:=xlsApp2.ActiveWorkbook.Worksheets("C"))
        'xlsApp1.ActiveWorkbook.Worksheets("A").copy(after:=xlsApp2.xlsBook1.Worksheets("B"))
        'xlsApp1.ActiveWorkbook.Worksheets("A").cells.copy()
        'xlsApp2.ActiveWorkbook.Worksheets("C").paste()
        'xlsSheet1 = xlsBook1.Worksheets(1)
        'xlsSheet1.Range("A1:k120").copy() 'cells.copy()
        'xlsApp1.Workbooks.Close()
        'xlsApp1.Quit()

        'xlsSheet2 = xlsBook2.Worksheets(1)
        'xlsSheet2.Range("A1:A1")
        'xlsSheet2.paste()

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

Author

Commented:
Cleaned version:
       
     Dim xlsApp1 As Object
        Dim xlsBook1 As Object
        Dim xlsSheet1 As Object
       

        Dim xlsApp2 As Object
        Dim xlsBook2 As Object
        Dim xlsSheet2 As Object
       

'Source Workbook        
       Dim sSampleFolder1 = "C:\TestWebLock\Rates\"
        xlsApp1 = CreateObject("Excel.Application")
        xlsApp1.Visible = False 'False to perform operations without viewing excel.
        xlsBook1 = xlsApp1.Workbooks.Open ("C:\TestWebLock\Rates\12_17_2007_01_T_899.xls")
     
'Destination Workbook
        xlsApp2 = CreateObject("Excel.Application")
        xlsBook2 = xlsApp2.Workbooks.Open("C:\TestWebLock\Rates\12_17_2007_01_899.xls")
        xlsApp2.DisplayAlerts = False
        xlsApp2.Visible = False 'False to perform operations without viewing excel.
       

    xlsApp1.ActiveWorkbook.Worksheets("A").copy (after:=xlsApp2.ActiveWorkbook.Worksheets("C"))
                   
      'tried this too but no luck
              'xlsApp1.ActiveWorkbook.Worksheets("A").cells.copy()
              'xlsApp2.ActiveWorkbook.Worksheets("C").paste()
       
'CLEAN UP
        xlsApp1.Workbooks.Close()
        xlsBook1 = Nothing
        xlsApp1.Quit()
        xlsApp2.Workbooks.Close()
        xlsBook2 = Nothing
        xlsApp2.Quit()
        GC.Collect()
CERTIFIED EXPERT
Top Expert 2015

Commented:
eFILE = "C:\TestWebLock\Rates\" & "12_17_2007_01_T_899" &  ".xls"
Workbooks.Open Filename:=eFILE
y = ActiveWorkbook.Name
zFILE = "C:\TestWebLock\Rates\" & "12_17_2007_01_899" &  ".xls"
Workbooks.Open Filename:=zFILE
z = ActiveWorkbook.Name

Workbooks(y).Activate
Sheets("A").Copy after:=Workbooks(Z).Sheets("C")
Windows(y).Close ([vbNo])
Windows(z).Close ([vbyes])

Try this code rather inplace of urs..it will do the trick for u..

Saurabh
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Jim,
thank you for your time and help. Yes, you are right - it looks that I should not start Excel twice.

When I modify my code per your suggestion, I don't have any more errors, but nothing is happening - i.e. after the process is done, nothing is copied and the Worksheet C is stilll blank.

Any ideas why?
Thanks

P.S. In my VS 2003, the editor adds sets of parenthesis after Copy statement such as
    xlsBook1.Worksheets("A").Copy (After:=xlsBook2.Worksheets("C"))
Doubt' this plays any role.
CERTIFIED EXPERT
Top Expert 2015

Commented:
Well coz this command is not to copy data from workbook-1 to workbook-2..this is moving the complete worksheet A from workbook-1 to workbook-2 after sheet c..

U want to copy the data or moving worksheet works for u..Its one and the same thing what ur trying to do..

Saurabh

Author

Commented:
Saurabh,

I am OK if my code performs MOVING the complete worksheet A from workbook 1 to workbook 2.

My destination workbook2 is completely empty. The worksheet C is the FIRST worksheet there and it is also blank. I named it C, but it may be called whatever, or it can stay as default name Sheet1

What command should I use to ensure that moved worksheet will be displayed in the FIRST worksheet of destination workbook?

Thanks

CERTIFIED EXPERT
Top Expert 2015
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Saurabh,
thanks a lot for all your help.

I tried using:
xlsBook1.Worksheets("A").Copy(Before:=xlsBook2.Worksheets("C"))
But still, nothing happens.

I also tried:
xlsBook1.Worksheets("A").UsedRange.Copy(xlsBook2.Worksheets("C").Range("A1"))

Still nothing.

Why this could be happening?
CERTIFIED EXPERT
Top Expert 2015

Commented:
Hey when u wrote the above code what u meant by nothing happens..as in the sheet does not get copy...??
well can u run this macro...it will do the trick for u..and its shorter as well..

Saurabh
Sub Copy()
 
Application.ScreenUpdating = False
 Application.DisplayAlerts = False
eFILE = "C:\TestWebLock\Rates\" & "12_17_2007_01_T_899" &  ".xls"
Workbooks.Open Filename:=eFILE
y = ActiveWorkbook.Name
zFILE = "C:\TestWebLock\Rates\" & "12_17_2007_01_899" &  ".xls"
Workbooks.Open Filename:=zFILE
z = ActiveWorkbook.Name
 
Workbooks(y).Activate
Sheets("A").Copy before:=Workbooks(Z).Sheets("C")
Windows(y).Close ([vbNo])
Windows(z).Close ([vbyes])
 
End Sub

Open in new window

Author

Commented:
Saurabh,

where to setup this macro? In source workbook?

How to execute macro from VB.NET?

Thank you very much for your persistency and help.
CERTIFIED EXPERT
Top Expert 2015

Commented:
Well u can set this macro...in any of the workbooks that u want to save it...for example...open a new workbook...Hit Alt+f11..then insert module...and then paste the code there and run it...it will do the trick what ur looking forward to...

Saurabh

CERTIFIED EXPERT

Commented:
Terrace,

>  ensure that moved worksheet will be displayed in the FIRST worksheet of destination workbook

Change this:

    xlsBook1.Worksheets("A").Copy After:=xlsBook2.Worksheets("C")

to this:

    xlsBook1.Worksheets("A").Copy Before:=xlsBook2.Worksheets(1)

Jim

Author

Commented:
Jim,
thank you for your help.
I actually got the answer for this through my other question.
https://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_23028859.html

So, now I am able to copy worksheets, but I am having problems with formatting.
Do you know how to use PasteSpecial via VB.NET

I need to keep size of the cells the same, but most importantly need to ensure that copied cells will be actual numbers, not the formulas (as in source worksheet)
 
Here is my question about that:
https://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_23031567.html

Thank you for your time and help

Commented:
If you add xlsBook2.Save() to jeverist solution it works perfectly.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.