Exporting to Excel from Access using SaveAs

[mdw233psu]

I currently have a module that exports data to a pre created/formatted
Excel template. Every time a user runs this module certian cells are
filled (data is based on a parameter they pick off of a form). This
data is run for a whole year every time, so I want the data put into
the blank template every time, and I want the template to have default
valuse of 0 (the problem is that it will have the data for the last
paramater picked rather than the 0). I figured I would just do a
SaveAs rather than a save, and create a new file, leaving the template
as is. I can't get the SaveAs to work, so I have changed it back to
Save and have posted the code below. If anyone knows how to use SaveAs
in this situation, please give me a hand. Here is the code:

Note: This code currently works, but I want to use a SaveAs rather
than a Save in the 3rd to last line of code here!

Dim xlapp As Object
Dim xlsheet As Object
Dim xlworkbook As Object
Dim filename as String

Dim acquery As DAO.QueryDef
Dim prm As DAO.Parameter
Dim objrst As DAO.Recordset

filename = "C:\Documents and Settings\V3PM3W\My
Documents\Development Environment\CSR ReportCard Template 2006.xls"

Set xlapp = CreateObject("Excel.Application")
xlapp.Visible = False
Set xlworkbook = xlapp.workbooks.Open(filename)
Set xlsheet = xlworkbook.sheets(2)

'I have omitted the running of the query and filling of the
Cells

xlworkbook.Save
xlworkbook.Close
xlapp.Quit

Set xlsheet = Nothing
Set xlworkbook = Nothing
Set xlapp = Nothing


Any help is appreciated!

 

[Guest]

Yes, there is a SaveAs method forthe Workbook object. There is also a
GetSaveAsFileName which will present the common File Open/Save dialog so the
user can select a path and name.

Open the VBA editor and select the object browser. It is the icon on the
toolbar that looks like a little pot of flowers
There will be two combos at the top. Select Excel in the Library and SaveAs
in the search. Then highligt the line with the method on it and press F1 to
get help. I think this will get you through it.

 

[Matt]

Yes, there is a SaveAs method forthe Workbook object. There is also a
GetSaveAsFileName which will present the common File Open/Save dialog so the
user can select a path and name.

Open the VBA editor and select the object browser. It is the icon on the
toolbar that looks like a little pot of flowers
There will be two combos at the top. Select Excel in the Library and SaveAs
in the search. Then highligt the line with the method on it and press F1 to
get help. I think this will get you through it.

Worked Great!
THANKS