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.

workbook saveas code builder

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.

workbook saveas password

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).ribbon set share

 

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).