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.
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.
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).
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Add the following code in your workbook event as shown in the screenshot.
Also please find attached an example file.
Save-as-fileformat.xlsm
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
Also please find attached an example file.
Save-as-fileformat.xlsm
ASKER
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.
Thank you everyone else for contributing as well. I have learnt a lot about Excel in past two days, thanks to you guys.
you can add this VBA code to your workbook and this will allow users only to select the xlsx format..
Open in new window