Preventing File Format Change When Saving Workbook

S

skidooo

New Member
Joined
Sep 16, 2010
Messages
3
I have a workbook that is used in a mixed Excel 2002, 2003, 2007 and probably 2010 environment. About a month ago, all the VBA code 'disappeared' from the workbook. I presume that one of the users with 2007 did a 'save as' and accepted the default .xlsx format and then later on a 2003 user saved it back to .xls format since that was the extension when I was called about the problem.

I need a way to force the workbook to be saved only as .xls (format 56). I have some questions about this:

I would like to allow all users to be able to save, not 'save as' the file as they work on it in case some disaster happens. If a 2007 or 2010 user opens an .xls file (format 56), and saves it, is the file saved with it's original file format (.xls), or as a file of the default file format of 2007/2010 ? I think that the answer is .xls.

I would like to allow certain users password protected access to 'save as' across all platforms. What is the best way to accomplish this ? I was thinking that I could put password prompting/checking code in Workbook.BeforeSave and check that SaveAsUI is true and be done but I am not sure that this would be sufficient. Any ideas about this ?

Lastly, I am not sure about how to use Application.DefaultSaveFormat. I have played around with this by putting this in the beginning of my code, but when I go to 'save as' the suggested default format is not the one that I specified with Application.DefaultSaveFormat. Does Application.DefaultSaveFormat only apply to 'save' and not 'save as' since the user can always override the suggested format in 'save as' ?

I am not proud. If anyone knows of code that will accomplish what I want, i.e force the saving of a workbook with a certain format, please point me in that direction.

Thanks
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
[RESOLVED] - Preventing File Format Change When Saving Workbook

I thought about this for a little while and come up with a solution.

For anyone curious:

In Workbook_BeforeSave I put the following:

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

    Call SaveAsExcel2003Format(SaveAsUI)          'Call the Save / Save As replacement logic
    Cancel = True                                 'Cancel saving again since we already did it

End Sub

This intercepts any 'save' or 'save as' attempts.

The code for SaveAsExcel2003Format is as follows:

Code: 
Option Explicit

Sub SaveAsExcel2003Format(bolSaveAsUI As Boolean)

    Const FILEEXTENTION = ".xls"               'The file extention for a Excel 2003 file
    Const XLS2003FORMATNUM = -4143             'The constant number for xlnormal in Excel 2003
    Const XLS2007FORMATNUM = 56                'The constant number for xlnormal in Excel 2007 and above
    
    Dim strFileName As String
  
    If bolSaveAsUI Then                           'Got here because of 'Save As' clicked
        'Get file name from the Save As dialog
        strFileName = Application.GetSaveAsFilename(ThisWorkbook.Name, "Microsoft Excel Workbook (*.xls),*.xls")
        If UCase(strFileName) = "FALSE" Then      'Returns False if Cancel is clicked
            Exit Sub
        End If
        strFileName = Left(strFileName, InStr(1, strFileName, ".") - 1) & FILEEXTENTION  'Force .xls extention
    Else                                        'User clicked Save 
        strFileName = Left(ThisWorkbook.Name, InStr(1, ThisWorkbook.Name, ".") - 1) & FILEEXTENTION
    End If

    Application.DisplayAlerts = False                   'Kill the prompt for overwriting the file
    Application.EnableEvents = False                    'Stop firing the Before_Save event
    
    
    'Determine which version of Excel we are running
    If Val(Application.Version) < 12 Then
        'We are running Excel 2003 and below
        ActiveWorkbook.SaveAs Filename:=strFileName, FileFormat:=XLS2003FORMATNUM
    Else
        'We are running Excel 2007 and above
        ActiveWorkbook.SaveAs Filename:=strFileName, FileFormat:=XLS2007FORMATNUM
    End If
    
    Application.EnableEvents = True                     'Allow events again
    Application.DisplayAlerts = False                   'Turn prompting back on
    
End Sub

I decided that I did not need a password after all.

Hope that this helps someone. :)
 
Upvote 0
I posted a question last week which is mostly answered by this post. However, Skiddoo's code allows files to be overwritten as the standard "this file already exists. do you want to replace it?" message is turned off by Application.DisplayAlerts = False
I need to prevent accidental overwriting of files so I tried commenting this out but there is then a problem if I answer "no" to the message - I get Run-time error 1004 "Method SaveAs of Object_Worrkbook failed"

if anyone can suggest a bit of code to hanlde a "no" answer to the saveas message I'd most grateful
 
Upvote 0
Hi Skidooo,
I've just cribbed your entire code from your "Resolved" post above! It does almost exactly what I need, the only problem being that I need to warn users that they're about to overwrite an existing file so need to display the alert and handle the error if the user says "no".
Thanks,
Keith
 
Upvote 0
The code works perfectly when I save (or save as). However, when I try to CLOSE the workbook I get stuck in a loop whereby I am repeated asked if I want to save the changes; when I choose "Yes" it loops. I must select "No" and then the file closes. Can you suggest some "workbook before close" code that would allow a "No" answer to "save changes?" but also allow a "Yes" and still save the workbook as ".xls" but avoid the present loop?
Thanks,
Bill Freund
 
Upvote 0
You must log in or register to reply here.

Similar threads

JoshLyman
  • Solved
Save specific sheet as PDF
2
Replies
11
Views
2K
tahmed5835
T
Y
  • Question
Modify Macro to Select 1 .TXT File at a time, do changes on it (tab delimeter+column to text) to convert to .XLS file+save the file then select other
Replies
0
Views
373
yferrer
Y
B
  • Solved
Convert all excel files in a folder to have password to modify option
Replies
6
Views
549
PeteWright
PeteWright
Enthusiastic Amateur
  • Question
Using VBA to Name a file based on a string & a cell value
Replies
1
Views
545
rlv01
R
R
  • Question
Saving to Desktop....
2
Replies
10
Views
906
RandyD123
R

Forum statistics

Threads
1,215,045
Messages
6,122,840
Members
449,096
Latest member
Erald

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top