Excel VBA Workbook.SaveAs Method

This Excel VBA tutorial explains how to use Workbook.SaveAs Method.

You may also want to read:

Excel VBA convert CSV to Excel

Excel VBA Workbook.SaveAs Method

VBA Workbook.SaveAs Method is same as the action Save As

Excel add password to workbook 01

 

The options under Tools are also available in VBA Workbook.SaveAs Method.

Excel add password to workbook 02

 

For example, we can add password to workbook.

Excel add password to workbook 03

To understand what Workbook.SaveAs Method is capable of, let’s see what arguments it has.

Syntax – Excel VBA Workbook.SaveAs Method

expression .SaveAs(FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local)
Name Required/Optional Data Type Description
Filename Optional Variant A string that indicates the name of the file to be saved. You can include a full path; if you don’t, Microsoft Excel saves the file in the current folder.
FileFormat Optional Variant The file format to use when you save the file. For a list of valid choices, see the XlFileFormat enumeration. 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.

Name Value Description
xlAddIn 18 Microsoft Excel 97-2003 Add-In
xlAddIn8 18 Microsoft Excel 97-2003 Add-In
xlCSV 6 CSV
xlCSVMac 22 Macintosh CSV
xlCSVMSDOS 24 MSDOS CSV
xlCSVWindows 23 Windows CSV
xlCurrentPlatformText -4158 Current Platform Text
xlDBF2 7 DBF2
xlDBF3 8 DBF3
xlDBF4 11 DBF4
xlDIF 9 DIF
xlExcel12 50 Excel12
xlExcel2 16 Excel2
xlExcel2FarEast 27 Excel2 FarEast
xlExcel3 29 Excel3
xlExcel4 33 Excel4
xlExcel4Workbook 35 Excel4 Workbook
xlExcel5 39 Excel5
xlExcel7 39 Excel7
xlExcel8 56 Excel8
xlExcel9795 43 Excel9795
xlHtml 44 HTML format
xlIntlAddIn 26 International Add-In
xlIntlMacro 25 International Macro
xlOpenDocumentSpreadsheet 60 OpenDocument Spreadsheet
xlOpenXMLAddIn 55 Open XML Add-In
xlOpenXMLStrictWorkbook 61 (&H3D) Strict Open XML file
xlOpenXMLTemplate 54 Open XML Template
xlOpenXMLTemplateMacroEnabled 53 Open XML Template Macro Enabled
xlOpenXMLWorkbook 51 Open XML Workbook
xlOpenXMLWorkbookMacroEnabled 52 Open XML Workbook Macro Enabled
xlSYLK 2 SYLK
xlTemplate 17 Template
xlTemplate8 17 Template 8
xlTextMac 19 Macintosh Text
xlTextMSDOS 21 MSDOS Text
xlTextPrinter 36 Printer Text
xlTextWindows 20 Windows Text
xlUnicodeText 42 Unicode Text
xlWebArchive 45 Web Archive
xlWJ2WD1 14 WJ2WD1
xlWJ3 40 WJ3
xlWJ3FJ3 41 WJ3FJ3
xlWK1 5 WK1
xlWK1ALL 31 WK1ALL
xlWK1FMT 30 WK1FMT
xlWK3 15 WK3
xlWK3FM3 32 WK3FM3
xlWK4 38 WK4
xlWKS 4 Worksheet
xlWorkbookDefault 51 Workbook default
xlWorkbookNormal -4143 Workbook normal
xlWorks2FarEast 28 Works2 FarEast
xlWQ1 34 WQ1
xlXMLSpreadsheet 46 XML Spreadsheet
Password Optional Variant A case-sensitive string (no more than 15 characters) that indicates the protection password to be given to the file.
WriteResPassword Optional Variant 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 Optional Variant True to display a message when the file is opened, recommending that the file be opened as read-only.
CreateBackup Optional Variant True to create a backup file.
AccessMode Optional XlSaveAsAccessMode The access mode for the workbook.

Name Value Description
xlExclusive 3 Exclusive mode
xlNoChange 1 Default (does not change the access mode)
xlShared 2 Share list
ConflictResolution Optional XlSaveConflictResolution An XlSaveConflictResolution value that determines how the method resolves a conflict while saving the workbook. If set to xlUserResolution, the conflict-resolution dialog box is displayed. If set to xlLocalSessionChanges, the local user’s changes are automatically accepted. If set to xlOtherSessionChanges, the changes from other sessions are automatically accepted instead of the local user’s changes. If this argument is omitted, the conflict-resolution dialog box is displayed.

Name Value Description
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.
AddToMru Optional Variant True to add this workbook to the list of recently used files. The default value is False.
TextCodepage Optional Variant Ignored for all languages in Microsoft Excel.

When Excel saves a workbook to one of the CSV or text formats, which are specified by using the FileFormat parameter, it uses the code page that corresponds to the language for the system locale in use on the current computer. This system setting is available in the Control Panel, by clicking Region and Language, clicking the Location tab, under Current location.

TextVisualLayout Optional Variant Ignored for all languages in Microsoft Excel.

When Excel saves a workbook to one of the CSV or text formats, which are specified by using the FileFormat parameter, it saves these formats in logical layout. If left-to-right (LTR) text is embedded within right-to-left (RTL) text in the file, or vice versa, logical layout saves the contents of the file in the correct reading order for all languages in the file without regard to direction. When an application opens the file, each run of LTR or RTL characters are rendered in the correct direction according to the character value ranges within the code page. (Unless an application that is designed to display the exact memory layout of the file, such as a debugger or editor, is used to open the file.)

Local Optional Variant 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 where Workbooks.Open is run from is an old internationalized XL5/95 VBA project).

Example – Excel VBA Workbook.SaveAs Method

When you save as a workbook in a location where the workbook name already exists, you will receive the following prompt box.

Excel protect workbook 10

In order for the Macro to continue to run without prompting the box, the first thing to do is to temporarily turn the alert box off using Application.DisplayAlerts = False, and all answers will become Yes.

The second thing is the Filname argument. Although the documentation says if you omit the Filename, the current folder will be used. I tried with Excel 2013 but it saves to My Document folder, so I use Application.ActiveWorkbook.FullName to make sure the file really saves to the current folder.

The third thing is FileFormat. I choose xlOpenXMLWorkbookMacroEnabled (xlsm) to ensure the Macro is saved successfully.

The below procedure uses SaveAs Method to add password (yourpassword) and then overwrite the existing xlsm workbook.

Sub add_password()
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=Application.ActiveWorkbook.FullName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="yourpassword"
'OR using specific path
    'ActiveWorkbook.SaveAs "C:\folderA\test.xlsx", Password:="yourpassword"
    Application.DisplayAlerts = True End Sub

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff841185.aspx

 

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *