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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alek Luchnikov - 31 May 2007 07:51 GMT

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: alekluch---FORSPAMERS---1983@mail.ru REMOVE ---FORSPAMERS---

Reply to this Message

RoyVidar - 31 May 2007 09:06 GMT

"Alek Luchnikov" <alekluch---FORSPAMERS---1983@mail.ru> wrote in
message <e8nvYA1oHHA.3512@TK2MSFTNGP06.phx.gbl>:

> Hello, All!
> I want to copy and rename excel sheet from ÁÓÓÅss vba.
[quoted text clipped - 13 lines]
> With best regards, Alek Luchnikov.
> E-mail: alekluch---FORSPAMERS---1983@mail.ru 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

Signature

Roy-Vidar

Reply to this Message

RoyVidar - 31 May 2007 09:12 GMT

"RoyVidar" <roy_vidarNOSPAM@yahoo.no> wrote in message
<mn.fa5e7d755a9e0234.59509@yahoo.no>:

> "Alek Luchnikov" <alekluch---FORSPAMERS---1983@mail.ru> wrote in
> message <e8nvYA1oHHA.3512@TK2MSFTNGP06.phx.gbl>:
[quoted text clipped - 35 lines]
> 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"

Signature

Roy-Vidar

Reply to this Message