Avatar of Andreas Hermle
Andreas Hermle
Flag for Germany 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
Microsoft Excel

Avatar of undefined
Last Comment
Andreas Hermle

8/22/2022 - Mon
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

Your help has saved me hundreds of hours of internet surfing.
fblack61
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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.