×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Copy an Existing then Add Excel Sheet to Same Workbook

Copy an Existing then Add Excel Sheet to Same Workbook

Copy an Existing then Add Excel Sheet to Same Workbook

(OP)
Hi all,
I'm working on a macro in EB where I've opened a specific workbook in Excel. At some point I want to insert a copy of one of the worksheets into the same workbook. I'll include the code I have so far that makes a copy of the worksheet and adds it, but it adds it to a new workbook. I can't get the code Excel uses for this function to work in EB. I'll include it too.

This is what works so far:
    Dim activesheet as object, objExcel As Object
    Dim objWorkBook As Object

    'ADD NEW WORKSHEET
    objWorkbook.WorkSheets("Batch").Copy
    ObjWorkbook.Add    
    
The same command according to Excel VBA would be(long way)
:
    objWorkbook.WorkSheets("Batch").Select
    objWorkbook.Cells.Select
    objWorkbook.Selection.Copy
    objWorkbook.WorkSheets("Batch").Select
    objWorkbook.WorkSheets.Add

Or (cleanest method):
    objWorkbook.WorkSheets("Batch").Select
    objWorkbook.WorkSheets("Batch").Copy Before:=Sheets(1)

I think the last command with "Copy Before:=Sheets(1)" is the problem for me. I can't get that into a language EB will allow/execute.

Just sign me desparate (as usual!)

RE: Copy an Existing then Add Excel Sheet to Same Workbook





Hi,

From EB, have you used the CreateObject method to create an Excel Application object?  You need an instance of Excel before you can manipulate Excel objects.

CODE

dim xl as Excel.Application, wb as Excel.Wrokbook
set xl = CreteObject("Excel.Application")
set wb = xl.workbooks.open(yourworkbookpath)
'''''

Skip,

glasses To be safe on the FOURTH,
Don't take a FIFTH on the THIRD
Or you might not come FORTH on the FIFTH
FORTHWITH tongue

RE: Copy an Existing then Add Excel Sheet to Same Workbook

(OP)
So sorry! I forgot to add that part. I'll add it below. Then might you have an answer for me??

'=============================================================================================
' Start Excel or Get A Reference To An Open Instance Of Excel
On Error Resume Next

Set objExcel = GetObject(, "Excel.Application")
If objExcel is Nothing Then
    Set objExcel = CreateObject("Excel.Application")
    If objExcel is Nothing Then
        MsgBox ("Could not open Excel.")
        Stop
    End If
End If

'Path To The Workbook (*.xls File)
Excelpath = "C:\Documents and Settings\s4019\My Documents\batch sheet.xls"

Set objWorkBook = objExcel.Workbooks.Open (ExcelPath)

If objWorkBook Is Nothing Then
    MsgBox "Could not open : " & Excelpath
    Stop
End If    
If Not objExcel.Visible Then objExcel.Visible = true

RE: Copy an Existing then Add Excel Sheet to Same Workbook

CODE

Sub Main

    Dim oExcel as Object
    Dim oBook as Object
    Dim oSheetToCopy as Object

    Set oExcel = CreateObject("Excel.Application")
    Set oBook = oExcel.workbooks.open("c:\test.xls")
    Set oSheetToCopy = oBook.Worksheets("Sheet1")
    
    oExcel.visible = true    
    oSheetToCopy.Copy Before:= oSheetToCopy

End Sub

thumbsup2  Wow, I'm having amnesia and deja vu at the same time.
                         I think I've forgotten this before.


RE: Copy an Existing then Add Excel Sheet to Same Workbook

(OP)
I received the following message:

ExtraBasic Error
Microsoft Excel: Copy method of worksheet class failed

That's been the problem for me all along.

Still would love help!

RE: Copy an Existing then Add Excel Sheet to Same Workbook

Did you alter the code I posted?  It runs fine on my machine.  If so post your modified code.  If not did you save a 'c:\test.xls' prior to running the code?

thumbsup2  Wow, I'm having amnesia and deja vu at the same time.
                         I think I've forgotten this before.


RE: Copy an Existing then Add Excel Sheet to Same Workbook

(OP)
This is what I have in my Extra macro:
Sub Main

    Dim oExcel as Object
    Dim oBook as Object
    Dim oSheetToCopy as Object

    Set oExcel = CreateObject("Excel.Application")
    Set oBook = oExcel.workbooks.open("c:\test.xls")
    Set oSheetToCopy = oBook.Worksheets("Sheet1")
    
    oExcel.visible = true    
    oSheetToCopy.Copy Before:= oSheetToCopy
    
    end sub
Isn't this what you sent me? Maybe there is a library problem?

RE: Copy an Existing then Add Excel Sheet to Same Workbook

What happens if you paste the code into a module in Excel and run it?

I'm not sure why your getting the message your getting.  It works fine in Extra or VBA on my end.


thumbsup2  Wow, I'm having amnesia and deja vu at the same time.
                         I think I've forgotten this before.


RE: Copy an Existing then Add Excel Sheet to Same Workbook

(OP)
I get an object error 1004 in Excel on this line"
 oSheetToCopy.Copy Before:=oSheetToCopy

RE: Copy an Existing then Add Excel Sheet to Same Workbook

I am using Excel 2003 sp 2.  It seems your problem is within Excel not EB.  I'd check your Excel help files to see if the .copy method syntax regarding sheets is different for your Excel version.

thumbsup2  Wow, I'm having amnesia and deja vu at the same time.
                         I think I've forgotten this before.


RE: Copy an Existing then Add Excel Sheet to Same Workbook

(OP)
The previously provided code does not work for me in eBasic. I do believe it is the "=:" that stops it. It will accecpt the "=" but not the ":".

Can someone help me with simply referencing between multiple sheets in a workbook within the same sub or between subs in the same macro? Thanks

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close