Copy Worksheet from one workbook to another
Copy Worksheet from one workbook to another
Copy Worksheet from one workbook to another
Copy Worksheet from one workbook to another
Copy Worksheet from one workbook to another
Copy Worksheet from one workbook to another Copy Worksheet from one workbook to another Copy Worksheet from one workbook to another Copy Worksheet from one workbook to another Copy Worksheet from one workbook to another Copy Worksheet from one workbook to another Copy Worksheet from one workbook to another Copy Worksheet from one workbook to another
Copy Worksheet from one workbook to another Copy Worksheet from one workbook to another
Copy Worksheet from one workbook to another
Go Back  Xtreme Visual Basic Talk > > > Copy Worksheet from one workbook to another


Reply
 
Thread Tools Display Modes
  #1  
Old 12-17-2007, 02:00 PM
pips69 pips69 is offline
Newcomer
 
Join Date: Dec 2007
Posts: 1
Default Copy Worksheet from one workbook to another


I tought this will be easy and smooth, but keep facing issues.
Below is my latest code attempting to copy a worksheet A from one workbook to another workbook (blank excel file). I created this new blank file and named its first worksheet C.
This destination file may be even created on a fly (does not need to exist prior to copying)

My code is below.
Currenlty I am getting error:
Copy method of Worksheet class failed

I am not sure what is the reason. Maybe Worksheet A goes into Read Only mode and it is not accesible?
I thought this would be an easy task, but it became my nightmare.

Code:

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

xlsApp1 = CreateObject("Excel.Application")
xlsApp1.Visible = False
xlsApp1.DisplayAlerts = False
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

'COPY
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()

Please help me troubleshoo this issue.
Thank you in advance.
Reply With Quote
  #2  
Old 12-17-2007, 06:59 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hi pips,

Welcome to the forum.

Your issue here is that you were trying to use two application instances simultaneously. Generally you can't pass a workbook, worksheet, or range (or any object) from one application to another in this fashion.

The good news, however, is that you don't need to. In fact, in this case, it looks like you have only one workbook, yes? So, basically, I think you had a right… But I think he should be able to simply use one application. Something like this:

Code:
Dim xlsApp1 As Object Dim xlsBook1 As Object Dim xlsSheet1 As Object xlsApp1 = CreateObject("Excel.Application") xlsApp1.Visible = False xlsApp1.DisplayAlerts = False xlsBook1 = xlsApp1.Workbooks.Open("C:\TestWebLock\Rates\12_17_2007_01_T_899.xls") xlsBook1.Worksheets("A").copy(after:=xlsBook1.Worksheets("C"))

The above is untested (and is basically your code!), But I think it should work.

Let us know how it goes…

Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #3  
Old 01-03-2008, 03:33 PM
FuzzyRichie FuzzyRichie is offline
Newcomer
 
Join Date: Jan 2008
Posts: 13
Default Close enough

Quote:
Originally Posted by Mike_R View Post
Hi pips,

Welcome to the forum. :)

Your issue here is that you were trying to use two application instances simultaneously. Generally you can't pass a workbook, worksheet, or range (or any object) from one application to another in this fashion.

The good news, however, is that you don't need to. In fact, in this case, it looks like you have only one workbook, yes? So, basically, I think you had a right… But I think he should be able to simply use one application. Something like this:

Code:
Dim xlsApp1 As Object Dim xlsBook1 As Object Dim xlsSheet1 As Object xlsApp1 = CreateObject("Excel.Application") xlsApp1.Visible = False xlsApp1.DisplayAlerts = False xlsBook1 = xlsApp1.Workbooks.Open("C:\TestWebLock\Rates\12_17_2007_01_T_899.xls") xlsBook1.Worksheets("A").copy(after:=xlsBook1.Worksheets("C"))

The above is untested (and is basically your code!), But I think it should work. :)

Let us know how it goes…

Mike
I think you have the right idea Mike, however in his code you notice that:
Quote:
xlsApp1.DisplayAlerts = False
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")
so wouldn't it be possible, instead of copying an area, to copy each cell individually using a For Each loop? I don't know the exact coding, as I'm no guru in Excel, but it's a thought, right? ^.^
__________________
FuzzyRichie
------------
1st year University of Ottawa student
Software Engineering with Security Option /Génie Informatique avec option Sécurité
Reply With Quote
  #4  
Old 01-03-2008, 04:14 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Wow, good eye! I missed the "_T" in there...

But, no, best to avoid the looping if possible, for it would be very, very slow if the worksheet is large. The key to Pips' troubles is that he's trying to use two separate Excel.Application instances -- that aren's necessary, and cannot interact.

But as you picked up, we do in fact need two different workbooks here, so corrected, the code should look something like this:

Code:
Dim xlsApp1 As Object Dim xlsBook1 As Object Dim xlsBook2 As Object Dim xlsSheet1 As Object xlsApp1 = CreateObject("Excel.Application") xlsApp1.Visible = False xlsApp1.DisplayAlerts = False xlsBook1 = xlsApp1.Workbooks.Open("C:\TestWebLock\Rates\12_17_2007_01_T_899.xls") xlsBook2 = xlsApp1.Workbooks.Open("C:\TestWebLock\Rates\12_17_2007_01_899.xls") xlsBook1.Worksheets("A").copy(after:=xlsBook2.Worksheets("C"))

Pips, is this working for you?

Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Copy Worksheet from one workbook to another
Copy Worksheet from one workbook to another
Copy Worksheet from one workbook to another Copy Worksheet from one workbook to another
Copy Worksheet from one workbook to another
Copy Worksheet from one workbook to another
Copy Worksheet from one workbook to another Copy Worksheet from one workbook to another Copy Worksheet from one workbook to another Copy Worksheet from one workbook to another Copy Worksheet from one workbook to another Copy Worksheet from one workbook to another Copy Worksheet from one workbook to another
Copy Worksheet from one workbook to another
Copy Worksheet from one workbook to another
 
Copy Worksheet from one workbook to another
Copy Worksheet from one workbook to another
 
-->