Solved

Copy worksheet into the same workbook

Posted on 2004-08-18
8
744 Views
Last Modified: 2010-04-17
I am writing code in VB6 to populate a workbook with data from a loop. I want the result of each loop to go onto a new worksheet. The first worksheet is a template for all the other worksheets so instead of formatting each worksheet to the same as the template, I want to copy the formatting and formulas on to the next worksheet and then update the values from the loop. When I try to copy the worksheet  I get error: Method 'Worksheets' of object '_Global' failed.

The code I am using

Dim xlApp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Worksheets(1).Copy After:=xlbook.Worksheets(1)


0
Comment
Question by:bmugabe
  • 6
  • 2
8 Comments
 
LVL 2

Expert Comment

by:zonaltech
ID: 11830170
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q178/5/10.asp&NoWebContent=1

Sometimes it's a pain when 'porting' macros over to VB from Office VBA; every single reference has to be qualified to reference the xlApp object, or else VB tries to access the local Worksheets object in the VB object library. A trick I use to help myself is start any automation code with a:

With xlApp

    .Worksheets(1).Copy After:=xlbook.Worksheets(1)

End With

That way you only need to use a "." instead of "xlApp." for every single object. ;) Let me know if this helps!
0
 
LVL 2

Expert Comment

by:zonaltech
ID: 11830218
Clarification: Every single object that isn't ALREADY linked to xlApp (I'm assuming you did a "Set xlBook = xlApp.ActiveWorkBook" or something similar) must be qualified. If you set xlBook in this manner, obviously you don't need to qualify it with xlApp, because it already links to it.

Native VBA has a certain spoil-factor, you get used to all the objects being 'there' already. In VB Automation, you have to tell VB what you're doing with what, every step of the way. I suppose that's it's same as if you tried to automate another application from within Access VBA, you'd have to define and reference all the variables pointing to the other application, or Access would get confused and try to use it's own library for the objects.
0
 

Author Comment

by:bmugabe
ID: 11831125
I tried that i.e. adding

with xlApp
.worksheets(1).copy After:=xlbook.worksheets(1)

End With

Now I get Copy method of worksheet class failed.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Expert Comment

by:zonaltech
ID: 11834591
is xlBook a valid object? where did you define it?
0
 
LVL 2

Expert Comment

by:zonaltech
ID: 11834968
I'm operating under the assumption that you've omitted code for the sake of shortness. However, if that truly is all the code you have in the procedure, there a quite a few steps to go through before xlBook even points to anything. If that's the case I can help you there too.
0
 
LVL 2

Expert Comment

by:zonaltech
ID: 11854405
Please post the full method if it isn't too large, we can go over opening an instance of Word and assigning it to xlApp object, opening a file from within it, setting that object to xlBook, etc.
0
 
LVL 2

Accepted Solution

by:
zonaltech earned 250 total points
ID: 11896163
*Just a ping, did you figure it out?*
0
 

Author Comment

by:bmugabe
ID: 12055618
Yes, I finally got it going. I tried the examples you gave and still got errors either method not supported or method failed. I finally had to split the action into two operations. Adding a new worksheet first then copying the original worksheet onto the added worksheet.

xlbook.Worksheets.Add After:=xlbook.Worksheets(n)

        xlsheet.Cells.Copy
            xlbook.ActiveSheet.Paste
           
     xlsheet.Activate

I will accept your answer though for the time and helping me to narrow down the problems.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …

763 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