Excel 2007 : can I force xl2007 to save in .xls format

**urungus** · 08-26-2008, 01:18 AM

The macros I have embedded in my excel files are being stripped out when my coworkers modify them using excel 2007 and then save the files in the default 2007 file format.

Is there a way to force the sheet to be saved in Excel 2003 format, thus preserving my macros (maybe using a Workbook_BeforeSave subroutine)?

Thanks.

**urungus** · 08-28-2008, 06:18 PM

I came up with the following, hope someone finds it useful:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim fileSaveName As String
    
    Application.EnableEvents = False
        Do
            fileSaveName = Application.GetSaveAsFilename(fileFilter:="Excel 2003 Files (*.xls), *.xls")
            If fileSaveName <> "False" And Right(fileSaveName, 4) <> ".xls" Then
                MsgBox ("FILE NOT SAVED - please save in .xls format")
            End If
        Loop Until fileSaveName = "False" Or Right(fileSaveName, 4) = ".xls"
        
        If fileSaveName <> "False" Then
            ThisWorkbook.SaveAs Filename:=fileSaveName
        End If
        
        Cancel = True
    Application.EnableEvents = True
End Sub

**shg** · 08-28-2008, 06:38 PM

That's a little hostile; it doesn't allow the user to save the workbook in 2007 format at all.

You might instead do the save the user want to do, and then do SaveCopyAs.

**urungus** · 08-28-2008, 07:14 PM

I don't want them to be able to save in Excel 2007 format because it strips out the macros and then the next person working on the file doesn't have the required functionality.

However there is a bug in the above code if the user makes changes to the file and attempts to close the file without saving first. The expected "do you want to save the changes you have made" dialog box pops up, but if the user chooses "yes" and saves the file, the same dialog box will pop up again (and again) until the user chooses to cancel.

I believe this is happening because I set Cancel to true at the end. But I need to do so for a normal save, because otherwise the file will be saved a second time after the routine is finished. Any advice?

**shg** · 08-28-2008, 07:25 PM

I don't know what you're trying to do.

If you want to ensure that there is always a post-review copy in xls format by the original file name, then store the original workbook name and path (FullName property) in a public variable in the Open event, and save by that name in addition to whatever is the user is doing in the Save event. You could also save as an xlsm in every save as well.

I just wouldn't keep the user from doing what they want to do.

You can also test SaveAsUI to see if the event was triggered by the user doing a File > Save As ...

But I'd just ask the user to do Save rather than SaveAs. Excel opens 2003 files in compatibility mode, and they have to do something willful to save as a 2007 workbook.

**urungus** · 08-28-2008, 09:54 PM

I don't know what you're trying to do.

I'm trying to force the file to be saved in .xls format.

We have several people collaborating on the file, some of whom use Excel 2007 and some who use 2003. Sometimes, despite telling them not to, and despite the file being opened in compatibility mode, the people updating the file save it in Excel 2007 format.

This causes several headaches:

1) The spreadsheet relies heavily on behind-the-scenes macros. They are stripped out when saved in Excel 2007 format, rendering the updated file practically useless.

2) The converters that allow Excel 2007 files to be read into Excel 2003 do not work reliably for us.

Thus, any time a save is attempted, whether via menu command save/save as, or because an unsaved file is being closed, I want to make sure the file is being saved in its original Excel 2003 format (.xls).

I've got the save/save as working but there is still the quirk described above when closing an unsaved file.

**royUK** · 08-29-2008, 01:40 AM

Originally Posted by urungus

The macros I have embedded in my excel files are being stripped out when my coworkers modify them using excel 2007 and then save the files in the default 2007 file format.

Is there a way to force the sheet to be saved in Excel 2003 format, thus preserving my macros (maybe using a Workbook_BeforeSave subroutine)?

Thanks.

That cannot be happening with a save, especially if they save as *.xlsm .

Please post in the correct Forum, I am moving this to the 2007 Forum

**ratcat** · 08-29-2008, 05:46 PM

Originally Posted by urungus

The macros I have embedded in my excel files are being stripped out when my coworkers modify them using excel 2007 and then save the files in the default 2007 file format.

Is there a way to force the sheet to be saved in Excel 2003 format, thus preserving my macros (maybe using a Workbook_BeforeSave subroutine)?

Thanks.

G'day

Suggestion.

Go to the machines that have 2007 change the following settings.

The setting will default the 2007 to save in 2003 mode without adjusting any setting when saving.

Go to the Office button > Excel Options > Save > then change the setting 'Save files in this format:' to Excel 97-2003 Workbook.

Then click on 'ok'.

Hope that helps