Solved

Copy worksheet into the same workbook

Posted on 2004-08-18
8
741 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

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’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

786 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