Saving as .xlsx from VBA MACRO

[Donhuff]

I am attempting to save files through a MACRO as .xlsx files using
getclosefilename function. I have Excell 2007 set to save all files as .xls
files. When I save the files, it places the .xlsx extension, but saves in
..xls format. I cannot open the files as they are, but can after changing the
extension to .xls through Explorer they open just fine.

How do I set Excel 2007to save files as .xlsx through the MACR VBA?

 

[Ron de Bruin]

Hi Don

See
http://www.rondebruin.nl/saveas.htm

 

[Barb Reinhardt]

I've had to use this to save as an xlsm

ThisWorkbook.SaveAs Filename:=filesavename, _
FileFormat:=52

This should help you figure out what you need.

http://www.rondebruin.nl/saveas.htm

 

[Donhuff]

Actually, the VBA is part of an Excel add-in under XLSTART, and the files I'm
saving contain no code. The problem is I have Excel set to save files as
..xlsx, but Excel saves the files in Excel 97-03 format with the .xlsx
extension. I cannot open the resulting .xlsx files, but can open the files
when I manually edit the filename throgh Explorer to .xls.

I am using the following MACRO code to save the files (again, as an add-in,
not .xlsm file):

Filt = "Excel Files (*.xlsx),&*.xlsx,"

SaveFile = Application.GetSaveAsFilename(InitialFileName:=Nme, _
FileFilter:=Filt, FilterIndex:=1, Title:="Browse to Location for
File")
ActiveWorkbook.SaveAs Filename:=SaveFile _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

The code is not part of the Excel file. When it saves with the .xlsx
extension, I cannot open the file. When I manually change the extension to
..xls through Explorer, I can open the file as an earlier Excel version file.

The question is how do I get the MACRO to save the file as a Excel 2007
format.

 

[Pada]

Donhuff, Barb Reinhardt already gave the answer you were looking for. See http://www.rondebruin.nl/saveas.htm (which he gave).

You would want to use "xlOpenXMLWorkbook" to save the Excel files as .xlsx

Barb, thanks again for that link, I was looking for the same as Donhuff.