Copy an Existing then Add Excel Sheet to Same Workbook

[JeaShe]

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!)

 

[SkipVought]

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.

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

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[JeaShe]

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

 

[IknowMe]

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

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

 

[JeaShe]

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!

 

[IknowMe]

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?

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

 

[JeaShe]

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?

 

[IknowMe]

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.




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

 

[JeaShe]

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

 

[IknowMe]

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.

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

 

[JeaShe]

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