New sheets and renaming sheets

[patt]

I would like to know how to add a sheet in a workbook.

I would also like to rename a sheet in a workbook.

Both of these i wish to do from Access using automation to Excel.

Are they possible?

[patt]

[quote name='patt' post='766343' date='20-Mar-2009 13:38']I would like to know how to add a sheet in a workbook.

I would also like to rename a sheet in a workbook.

Both of these i wish to do from Access using automation to Excel.

Are they possible?[/quote]
As far as I know i can do the following:

To add a sheet i just use:
.sheets.add this will create a sheet called sheet1 as long as sheet1 does not exist.

To rename a sheet i just:
.sheets("sheet1").name="new sheet name"

[StuartR]

[quote name='patt' post='766346' date='20-Mar-2009 05:21']As far as I know i can do the following:

To add a sheet i just use:
.sheets.add this will create a sheet called sheet1 as long as sheet1 does not exist.

To rename a sheet i just:
.sheets("sheet1").name="new sheet name"[/quote]
Rather than assuming that the new sheet will be called sheet1, you could use
sht = .sheets.add(type:=xlWorksheet, count:=1, after:=.sheets(1))
sht.name = "new sheet name"

[patt]

[quote name='StuartR' post='766349' date='20-Mar-2009 19:26']Rather than assuming that the new sheet will be called sheet1, you could use
sht = .sheets.add(type:=xlWorksheet, count:=1, after:=.sheets(1))
sht.name = "new sheet name"[/quote]


Good point, thanks stuart.

[rory]

Don't forget Set:

Set sht = .sheets.add(type:=xlWorksheet, count:=1, after:=.sheets(1))