How to copy and rename MS Excel sheet from MS Access VBA (MS Office 2000)

[Alek Luchnikov]

Hello, All!
I want to copy and rename excel sheet from ÁÓÓÅss vba.

I try this but result is error:

Sht.ActiveWorkbook.WorkSheets("Sheet2").Copy
Before:=Sheet.ActiveWorkbook.WorkSheets(1)
or
Sht.WorkSheets("Sheet1").Copy Before:=Sheet.WorkSheets(1)

I know how to add sheet, insert data from access into sheet & else.
My searching in internet return nothing.
Is anybody know how do this ????
Thank you in advance.

With best regards, Alek Luchnikov.
E-mail: (e-mail address removed) REMOVE ---FORSPAMERS---

 

[RoyVidar]

Hello, All!
I want to copy and rename excel sheet from ÁÓÓÅss vba.

I try this but result is error:

Sht.ActiveWorkbook.WorkSheets("Sheet2").Copy
Before:=Sheet.ActiveWorkbook.WorkSheets(1)
or
Sht.WorkSheets("Sheet1").Copy Before:=Sheet.WorkSheets(1)

I know how to add sheet, insert data from access into sheet & else.
My searching in internet return nothing.
Is anybody know how do this ????
Thank you in advance.

With best regards, Alek Luchnikov.
E-mail: (e-mail address removed) REMOVE ---FORSPAMERS---

I think we would need more code, to be able to assist - and the error
number/message.

I would usually have one Excel object (xl), one (or more) Workbook
object(s) (wr) and one (or more) sheet object(s), and ensure they are
"anchored" correctly. Here's a small untested sample without any
errorchecking.

set xl=createobject("excel.application")
set wr = xl.workbooks.open("c:\mywrk.xls")
set sh = wr.sheets(3)

sh.copy before:=wr.sheets(1)
sh.name = "MyNewName"

set sh = nothing
set wr = nothing
set xl = nothing

 

[RoyVidar]

I think we would need more code, to be able to assist - and the error
number/message.

I would usually have one Excel object (xl), one (or more) Workbook
object(s) (wr) and one (or more) sheet object(s), and ensure they are
"anchored" correctly. Here's a small untested sample without any
errorchecking.

set xl=createobject("excel.application")
set wr = xl.workbooks.open("c:\mywrk.xls")
set sh = wr.sheets(3)

sh.copy before:=wr.sheets(1)
sh.name = "MyNewName"

set sh = nothing
set wr = nothing
set xl = nothing

Ah, the naming would probably require to select the copied sheet,
sorry.

sh.copy before:=wr.sheets(1)
set sh = wr.sheets(1)
sh.name = "MyNewName"