We help IT Professionals succeed at work.

Access VBA SaveAs method for Excel file

Marilync1266
Marilync1266 used Ask the Experts™
on
Hi,

I'm using access vba to manipulate an Excel file.  When I save the file, it is in Excel 95, I want to save it as Excel 97-2003.  How do I specify this?

Set DB = CurrentDb
Set xlApp = New Excel.Application

Set xlwb_Book = xlApp.Workbooks.Open(strSubFolder & rstBkgsRpt!ReportName & " " & strFileDate & ".xls")

Set xlws_Book = xlwb_Book.Sheets(1)
xlwb_Book.Sheets(1).Name = "YTD Bookings"

xlwb_Book.Save
xlwb_Book.Close
Set xlwb_Book = Nothing
xlApp.Quit
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NorieAnalyst Assistant

Commented:
Use SaveAs and specify the fileformat.
xlwb_Book.SaveAs Filename = Filename:=xlwb_Book.FullName, FileFormat:=XlFileFormat.xlExcel12

Open in new window

xlwb_book.SaveAs Filename:="C:\Documents\Book1.xls",  FileFormat:=xlExcel8
NorieAnalyst Assistant

Commented:
Oops, wrong FileFormat, Peter is right it should be xlExcel8.

Author

Commented:
xlExcel8 is not an option in the drop down box.  I'm using Microsoft Access 2000
NorieAnalyst Assistant

Commented:
What dropdown box?

xlExcel8 is an Excel VBA constant and since you have a reference to the Excel Object library you should be able to use it.

Author

Commented:
Now, I receive a message if I want to replace the file since it already exists.  Is there a way to default this to yes without being prompted?
Analyst Assistant
Commented:
Try this.
xlApp Application.DisplayAlerts = False
xlwb_Book.SaveAs Filename = Filename:=xlwb_Book.FullName, FileFormat:=xlExcel8
xlApp Application.DisplayAlerts = True

Open in new window

Author

Commented:
I had to make a slight tweak - remove the word "Application".  It works!

Thanks,
NorieAnalyst Assistant

Commented:
Doh, of course - xlApp is the Excel application.:)