• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1209
  • Last Modified:

How do I save a .xlsm file as a .xls

I have the following code in "Workbook-BeforeSave" in my Excel 2010 .xlsm file:


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

ActiveWorkbook.SaveCopyAs "c:\conversion.xls"

End Sub


It works well, except the saved file really isn't in an .xls format.  How can I save it as an .xls file?  I understand I will loose the macro's etc., I only want the data to remain in the newly created file.
0
fselliott
Asked:
fselliott
1 Solution
 
krishnakrkcCommented:
To remove macros, first saveas .xlsx and then .xls.

Kris
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

you can save as an Excel 97 - 2003 file, which will give it an .xls extension AND keep the macros intact. There is no need to save as an .xlsx first. In Excel 2003 and earlier, there was no different file extension for macro-enabled workbooks, so saving with the .xls extension will not destroy the macros. Saving as .xlsx first WILL destroy the macros.

cheers, teylyn
0
 
krishnakrkcCommented:
you can save as an Excel 97 - 2003 file

that's what the OP's code currently does.

What I proposed is, saving the file without macros.

Kris
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ExcelGuideConsultantCommented:
Use this code instead:
ActiveWorkbook.SaveCopyAs "c:\conversion.xls", fileformat:=56
0
 
fselliottAuthor Commented:
I have tried using the following, as suggested:

          Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

          ActiveWorkbook.SaveCopyAs "c:\conversion.xls", fileformat:=56

          End Sub

But, when I save the document I am first prompted as to whether or not I would like to replace the existing document, then I am prompted with the compatability checker, and then after I have said to continue, Excel crashes.

I am using Excel 2010 on a Windows 7 computer.  My goal, is only to save the macro-enabled Excel file as a flat .xls file. I do not want the user who is saving the file to be prompted for any reason, I want this to be able to run in the background each time the .xlsm file is saved. Thank you for your continued help.
0
 
fselliottAuthor Commented:
I have solved the issue by using the following code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs "c:\ProjSched.xls", _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommend:=False, _
CreateBackup:=False

End Sub
0
 
fselliottAuthor Commented:
In Excel 2010, on a Windows 7 workstation, this solution is most effective.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now