Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Copy an Existing then Add Excel Sheet to Same Workbook

Status
Not open for further replies.
JeaShe

JeaShe

Programmer
Joined
Mar 9, 2004
Messages
89
Location
US
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!)
 
Sort by date Sort by votes




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 [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] [tongue]
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.

 
Upvote 0 Downvote
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!
 
Upvote 0 Downvote
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.

 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
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.

 
Upvote 0 Downvote
I get an object error 1004 in Excel on this line"
oSheetToCopy.Copy Before:=oSheetToCopy
 
Upvote 0 Downvote
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.

 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

247xavi
  • Locked
  • Question Question
Attachmate to BlueZone
Replies
0
Views
547
247xavi
247xavi
AlbertShenko
  • Locked
  • Question Question
Excel VBA Not Sending F keys to Attachmate
Replies
15
Views
2K
SkipVought
S
CADD6953
  • Locked
  • Question Question
Macro for Excel to Attachmate and Back Again
2
Replies
34
Views
3K
SkipVought
S
jeaneshe
  • Locked
  • Question Question
How do I select different Excel cells using a variable column loop?
Replies
10
Views
1K
SkipVought
S
d3sdinova
  • Locked
  • Question Question
Using VBA to work with two Attachmate sessions at the same time
Replies
9
Views
966
SkipVought
S

Part and Inventory Search

Sponsor

Back
Top