Link to home
Start Free TrialLog in
Avatar of AJ Singh
AJ Singh

asked on

Excel file problems due to saving it back and forth in different versions

Hi,

We have this major problem with our excel files as some employees are saving the files (created in Excel 2010 and 2013) into 97-2003 format and it is taking away all the colors and formatting and also corrupting the data.

Is there a way to hardcode the file to be only saved in the version 2010 or 2013 and totally block saving in to an older version?

Please HELP! :)

Thanks.
Avatar of Haris Dulic
Haris Dulic
Flag of Austria image

Hi,

you can add this VBA code to your workbook and this will allow users only to select the xlsx format..

If SaveAsUI = True Then
wbName = Application.GetSaveAsFilename( _
fileFilter:="Excel Workbook (*.xlsx), *.xlsx")
Cancel = True
End If

Open in new window

Avatar of AJ Singh
AJ Singh

ASKER

Hi Haris,

Thanks for your reply.

When I used your script, it throws the error:

Compile Error: Invalid outside procedure

(And it highlghted SaveAsUI).
ASKER CERTIFIED SOLUTION
Avatar of Danny Child
Danny Child
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Add the following code in your workbook event as shown in the screenshot.

User generated image

 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim myfilename As String
'1. Check of Save As was used.
   If SaveAsUI = True Then
        Cancel = True
'2. Call up your own dialog box.  Cancel out if user Cancels in the dialog box.
       myfilename = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As XLSM file")
        If myfilename = "False" Then
            MsgBox "Action Cancelled", vbOKOnly
            Cancel = True
            Exit Sub
        End If
'3. Save the file.
       Application.EnableEvents = False
        ThisWorkbook.SaveAs Filename:=myfilename, FileFormat:=xlOpenXMLWorkbookMacroEnabled
        Application.EnableEvents = True
End If
End Sub

Open in new window



Also please find attached an example file.
Save-as-fileformat.xlsm
Thanks man! This solution was neat. I imported the OCT templates into my group policy and blocked save on the 97-2003 file.

Thank you everyone else for contributing as well. I have learnt a lot about Excel in past two days, thanks to you guys.