|
||||||||||||||||||||
|
|||||||||||||||||||||
|
#1
08-26-2008, 02:18 AM
|
|||
|
|||
can I force xl2007 to save in .xls format
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. 
|
|
#2
08-28-2008, 07:18 PM
|
|||
|
|||
|
I came up with the following, hope someone finds it useful:
Code:
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
Last edited by urungus; 08-28-2008 at 07:33 PM.
|
|
#4
08-28-2008, 08: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?
|
|
#5
08-28-2008, 08: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.
|
|
#6
08-28-2008, 10:54 PM
|
|||
|
|||
|
Quote:
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.
|
|
#7
08-29-2008, 12:02 AM
|
||||
|
||||
|
Again,
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.
|
|
#8
08-29-2008, 02:40 AM
|
||||
|
||||
|
Quote:
Please post in the correct Forum, I am moving this to the 2007 Forum
__________________
Hope that helps. RoyUK -------- For Excel consulting, free examples and tutorials visit my site Check out the free Excel Toolbar New members please read & follow the Forum Rules Remember to mark your questions Solved and rate the answer(s) Where to copy the code to Code Tags: Make your code easier for us to read
|
|
#9
08-29-2008, 06:46 PM
|
||||
|
||||
|
Quote:
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
__________________
Office 2007 Any Feedback Welcomed If you receive a solution....Please let us know: 1) Click the EDIT button on your first post 2) Click the GO ADVANCED button 3) Select SOLVED from the Title dropdown (under the Reason for Editing box) 4) Click the SAVE CHANGES button Note: If it's more than 2 days after your first post, please ask a moderator to mark the thread SOLVED for you.
|
 |
| Bookmarks |
New topics in Excel 2007 Help
|
|
|
| Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Force file name format | Dude101 | Excel Programming | 1 | 07-16-2008 09:50 AM |
| Save As cell format problem | Jumpy | Excel Programming | 2 | 11-19-2007 03:33 AM |
| Force cell date format | Macdave_19 | Excel Programming | 2 | 09-20-2007 05:07 PM |
| .xla file saves itself as .xls file format | cmw001 | Excel Miscellaneous | 1 | 07-24-2007 10:09 AM |
| Save file, create .pdf, (Now delete .xls file) | daniels012 | Excel General | 4 | 03-30-2007 06:14 PM |
Linear Mode
