Solved

Copy worksheet into the same workbook

Posted on 2004-08-18
8
745 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This is an explanation of a simple data model to help parse a JSON feed
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.

710 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