Force users to only save as '.xls'-file

Andreas Hermle
Andreas Hermle used Ask the Experts™
on
Dear Experts:

I would like to prevent users from saving my 'Sample.xls' file as 'Sample.xlsm' or 'Sample.xlsx' file by accident.

This is in case they do not use just the 'Save'-button but accidentally the 'Office-Button - Save As' functionality.

Hence, in summary, I would like to force users to save the 'Sample.xlsm' file (in case they accidentally use the menu 'Office Button - Save As') ...

... only save as 'xls-file and ...
... only with the designated filename 'Sample.xls' ... and
... only in the directory "C:\temp\".

i.e. they just can overwrite the existing file once it has been opened. Of course they should be able to abort the whole action and close the file without any saving any possible changes.

I hope this is not asking too much.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
remmett70Network Manager

Commented:
You can set the default save preferences for Excel to .xls.  New documents will be created in that format by default and any existing .xls files will stay defaulted for save and save as.

You can do it individually or Group Policy.
http://technet.microsoft.com/en-us/library/cc178949(v=office.12).aspx

I don't think you can prevent them from saving in other formats though.  but setting the default preference will make it so users have to change the format to save in something else so it doesn't just happen accidentally.
try this... put this code in ThisWorkbook of your workbook.

Dim saveProcessStarted As Boolean 

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim result As Integer
    Dim saveAsThisFilename As String

    
    If saveProcessStarted = False Then
        result = MsgBox("would you like to save?", vbYesNo)
    Else
        result = 6
    End If
    
    If result = vbYes Then
        saveProcessStarted = True
        'create filename
        saveAsThisFilename = "c:\temp\Sample.xls"
        'save as xls
        ActiveWorkbook.SaveAs Filename:=saveAsThisFilename, FileFormat:=xlExcel8, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
        'reset saveProcessStarted
        saveProcessStarted = False
    Else
        MsgBox "Save action cancelled"
    End If
End Sub

Open in new window

There is a VBA event hook beforesave

This ugly code will force it to default to .xls

I've tested it and it works, to get it properly installed open excel vba (press alt+F11) and double click onthe workbook item to the left then just paste the code.

-SA

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.EnableEvents = False

  Dim vFile
   
   If SaveAsUI Then
vFile = Application.GetSaveAsFilename("", "Excel files (*.xls),*.xls")
   If TypeName(vFile) = "Boolean" Then
    Cancel = True
    Application.EnableEvents = True
    
       Exit Sub  ' user cancelled
End If
   ActiveWorkbook.SaveAs vFile, FileFormat:=56
   Application.EnableEvents = True
   cancel = true
     Exit Sub
   End If
Application.EnableEvents = True
 End Sub

Open in new window

Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Andreas HermleTeam leader

Author

Commented:
Hi ScriptAddict:

ok, great, this really forces the the file to save as .xls.

I am afraid  to tell  you that this is 'only' one of the requirements I am looking for.

I.E. I the code should also force the user to just save it ...
... under a designated filename (the original one) and ....
....in a certain folder (C:\temp)

Help is much appreciated.

Thank you very much in advance.

Regards, Andreas
Andreas,

ActiveWorkbook.SaveAs vFile, FileFormat:=56

you can change  the vFile variable to any string. For example, you could replace it entirely with "C:\temp\Sample.xls". In this example it would look like

ActiveWorkbook.SaveAs "C:\temp\Sample.xls", FileFormat:=56
This is , as correctly noted above, much simpler.  This code below should be all you need.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.EnableEvents = False
 ActiveWorkbook.SaveAs "C:\temp\Sample.xls", FileFormat:=56
Application.EnableEvents = True
   cancel = true
 End Sub

Open in new window

Andreas HermleTeam leader

Author

Commented:
Hi aebea,

thank you very much for  your swift help.

your code works great if you 'just' use the 'Save' button. But if the user happens to use the SaveAs action it regrettably does not work properly, throwing error messages.

Regards, Andreas
Does the temp directory already exist on the computer?  Or does it need to be created?

-SA
Updated to default to requested location:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.EnableEvents = False

  Dim vFile
  Dim strDirectoryPath As String
    
   If SaveAsUI Then
vFile = Application.GetSaveAsFilename("C:\temp\Sample.xls", "Excel files (*.xls),*.xls")
   If TypeName(vFile) = "Boolean" Then
    Cancel = True
    Application.EnableEvents = True

       Exit Sub  ' user cancelled
End If
   strDirectoryPath = "C:\temp\"
   If Dir(strDirectoryPath, vbDirectory) = "" Then MkDir strDirectoryPath

   ActiveWorkbook.SaveAs vFile, FileFormat:=56
   Application.EnableEvents = True
   saveProcessStarted = False
Cancel = True
     Exit Sub
   End If
Application.EnableEvents = True
 End Sub

Open in new window

Andreas HermleTeam leader

Author

Commented:
Hi Script Addict:

the temp  directory already exists on the computer.

It appears that the re-written code does not produce any results.

I may be wrong and will do further testing tomorrow.

Thank you very much.

Regards, Andreas
Ok,  I'll check back tommorrow.  I know on my system it worked fine.  But you can still change the default.
Andreas HermleTeam leader

Author

Commented:
Hi ScriptAddict:

this seems to work just fine. I am gonna give it a couple of more trials and then let you know.

Regards, Andreas
Andreas HermleTeam leader

Author

Commented:
Thank you very much for your professional help.

Regards, Andreas

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial