insert an excel sheet

[jnewl]

i am opening an excel application in access and copying an existing excel
workbook. saving the workbook with another name. how do i insert a new
worksheet into the new workbook. tried various approaches with no success

code:

Set xlapp = CreateObject("excel.application")
Set xlsheet = xlapp.workbooks.Open("g:\fee sched test\2009 calendar.xls")

xlapp.activeworkbook.Saveas ("g:\fee sched test\calendar copy.xls")

xlapp.Quit
Set xlapp = Nothing
Set xlsheet = Nothing
Set xlapp = CreateObject("excel.application")
Set xlsheet = xlapp.workbooks.Open("g:\fee sched test\calendar copy.xls")

xlsheet.sheets("sheet1").Add


xlapp.activeworkbook.Save
xlapp.activeworkbook.Close ("g:\fee sched test\calendar copy.xls")
xlapp.Quit
Set xlapp = Nothing
Set xlsheet = Nothing


thanks

 

[Tom van Stiphout]

On Mon, 13 Apr 2009 13:24:13 -0700, jnewl

If you don't want to study the object model itself, here is a great
shortcut: Open Excel, turn on the Macro recorder, and insert a
worksheet. Stop the recorder. Check the VBA that was created.

-Tom.
Microsoft Access MVP

 

[jnewl]

hi,
i did that but what i would like to do is take the code that excel generates
and write it in access vb. i can' t find the structure of translating the
excel syntax to access vb syntax.

thanks

 

[Tom van Stiphout]

On Tue, 14 Apr 2009 05:02:07 -0700, jnewl

I don't understand. In Excel:
Workbooks.Open("g:\fee sched test\2009 calendar.xls")

In Access:
xlapp.workbooks.Open("g:\fee sched test\2009 calendar.xls")

-Tom.
Microsoft Access MVP

 

[jnewl]

i want to insert a new sheet in the new excel workbook before i save the new
workbook...

what is the syntax that allows me to do that in access vb?

i ran an excel macro that shows me the code in excel. but i don't know the
syntax for that same routine in access

thanks

 

[Alex Dybenko]

Hi,
try:
xlsheet.sheets.Add

where xlsheet is workbook object

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[jnewl]

hi,

this is the code i am using..
'Save routine
Set xlapp = CreateObject("excel.application")
Set xlsheet = xlapp.workbooks.Open("g:\fee sched test\2009 calendar.xls")


xlapp.activeworkbook.Saveas ("g:\fee sched test\calendar copy.xls")

xlapp.Quit
Set xlapp = Nothing
Set xlsheet = Nothing
Set xlapp = CreateObject("excel.application")
Set xlsheet = xlapp.workbooks.Open("g:\fee sched test\calendar copy.xls")
xlsheet.sheets("sheet1").Add
xlsheet.sheets("physician2").Add
xlsheet.sheets("ancillary2").Add
xlsheet.sheets("facility2").Add
xlapp.activeworkbook.Save
xlapp.activeworkbook.Close ("g:\fee sched test\calendar copy.xls")
' xlapp.activeworkbook.Close ("g:\fee sched test\2009 calendar.xls")
xlapp.Quit
Set xlapp = Nothing
Set xlsheet = Nothing


'end save routine

when i add the sheets, i get an out of range subscript

 

[Alex Dybenko]

Hi,
xlsheet.sheets("sheet1").Add is not correct syntax

xlsheet.sheets.Add - is correct one

See help on add method for additional parameters you can specify there

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com