S Oberlander
Board Regular
- Joined
- Nov 25, 2020
- Messages
- 147
- Office Version
- 365
- Platform
- Windows
I have the following sub to save my excel file as a binary workbook. It works great, I just want to add that it should check if my excel file is actually saved already, and if yes it should just save the current file and not save a copy.
I am looking to replace the default save button on the quickaccess toolbar, I find the method used there annoying and long winding...
I am looking to replace the default save button on the quickaccess toolbar, I find the method used there annoying and long winding...
VBA Code:
Sub SaveBinary()
Dim fname As Variant
Dim FileFormatValue As Long
fname = Application.GetSaveAsFilename(InitialFileName:=ActiveCell, filefilter:= _
" Excel Macro Free Workbook (*.xlsx), *.xlsx," & _
" Excel Macro Enabled Workbook (*.xlsm), *.xlsm," & _
" Excel 2000-2003 Workbook (*.xls), *.xls," & _
" Excel Binary Workbook (*.xlsb), *.xlsb", _
FilterIndex:=4, Title:="Save as xlsb")
'Find the correct FileFormat that match the choice in the "Save as type" list
Select Case LCase(Right(fname, Len(fname) - InStrRev(fname, ".", , 1)))
Case "xls": FileFormatValue = 56
Case "xlsx": FileFormatValue = 51
Case "xlsm": FileFormatValue = 52
Case "xlsb": FileFormatValue = 50
Case Else: FileFormatValue = 0
End Select
If fname = False Then
Exit Sub
End If
ActiveWorkbook.SaveAs fname, FileFormat:=FileFormatValue, CreateBackup:=False
ActiveWorkbook.Save
End Sub