How to use Worbook.SaveAs
The Worbook SaveAs method is used to save changes to the workbook in a different file. In the below example the user selects the folder and gives the filename using the GetSaveAsFilename dialog.
Dim varFile As Variant
Dim strFile As String
varFile = Application.GetSaveAsFilename(InitialFileName:="c:\temp\test.xlsx", _
FileFilter:="Excel Files, *.xlsx")
If varFile <> False Then
strFile = varFile
Dim wb As Workbook: Set wb = ActiveWorkbook
wb.SaveAs Filename:=strFile, FileFormat:=xlOpenXMLWorkbook
End If
On this page we show the different options available when saving the workbook using the SaveAs arguments Filename FileFormat, Password, ReadOnlyRecommended, CreateBackup, AddToMru and Local.
The image on the right shows the Code VBA (download) builder that explains what the different options mean and allows you to select required values.
Filename
The name of the file to be saved.You can include a full path; if you don't Excel saves the file in the current folder.
| Note |
|---|
| The extension used with the filename should match the file format - see table below |
FileFormat
The file format to use when you save the file. For an existing file the default format is the last file format specified; for a new file the default is the format of the version of Excel being used. Most used values are
| xlWorkbookNormal (-4143) | xls | Excel 1997 - 2003 |
| xlOpenXMLWorkbook (=xlWorkbookDefault, 51) | xlsx | without macro's |
| xlOpenXMLWorkbookMacroEnabled (52) | xlsm | with or without macro's |
| xlExcel12 (50) | xlsb | Excel Binary Workbook with or without macro's |
| xlExcel8 (56) | xls | Excel 2007 or later |
| xlCSV (6) | csv | Local:=True |
For a complete list of valid choices see the XlFileFormatenumeration.
Password
A case-sensitive string (no more than 15 characters) that indicates the protection password to be given to the file.
WriteResPassword
A string that indicates the write-reservation password for this file. If a file is saved with the password and the password isn't supplied when the file is opened the file is opened as read-only.
ReadOnlyRecommended
True to display a message when the file is opened recommending that the file be opened as read-only (Default is False).
CreateBackup
True to create a backup file (Default is False).
AccessMode - Share or Exclusive
The access mode for the workbook: shared or exclusive.
| xlExclusive (3) | Workbook can only be edited by one person at a time |
| xlShared (2) | Workbook can be edited by multiple persons at the same time |
| xlNoChange (1) | Current value for workbook will be maintained |
ConflictResolution
Specifies the way conflicts are to be resolved whenever a shared workbook is updated.
| xlLocalSessionChanges (2) | The local user's changes are always accepted. |
| xlOtherSessionChanges (3) | The local user's changes are always rejected. |
| xlUserResolution (1) | A dialog box asks the user to resolve the conflict. |
If this argument is omitted, the conflict-resolution dialog box is displayed.
AddToMru
True to add this workbook to the list of Most Recently Used files.The default value is False.
Local
True saves files against the language of Microsoft Excel (including control panel settings). False (default) saves files against the language of Visual Basic for Applications (VBA) (which is typically US English unless the VBA project whereWorkbooks. Open is run from is an old internationalized XL5/95 VBA project).