asked on

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

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

remmett70

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.

Anthony Berenguel

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

ScriptAddict

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

Andreas Hermle

ASKER

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

Anthony Berenguel

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

ScriptAddict

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 Hermle

ASKER

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

ScriptAddict

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

-SA

ASKER CERTIFIED SOLUTION

ScriptAddict

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andreas Hermle

ASKER

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

ScriptAddict

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

Andreas Hermle

ASKER

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 Hermle

ASKER

Thank you very much for your professional help.

Regards, Andreas