To make an Excel file available for shared use you must save the file using the SaveAs with AccessMode = xlShared

To terminate sharing the method ExclusiveAccess is available.


Allows you to save the workbook in a different format or with different attributes.

ActiveWorkbook.SaveAs Filename:="C:\YourFolder\" & MyName, _ 
                      FileFormat:=xlFileFormat.xlNormal, _
                      Password:="", _
                      WriteResPassword:="", _
                      ReadOnlyRecommended:=False, _
                      CreateBackup:=False, _
                      AccessMode:=XlSaveAsAccessMode.xlNoChange, _
                      ConflictResolution:=XlSaveConflictResolution.xlLocalSessionChanges, _
                      AddToMru:=True, _
                      TextCodepage:=, _
                      TextVisualLayout:=, _

Filename - 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 - Variant. 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. What is xlNormal as it is not in the enumeration ???
Password - Variant. A case-sensitive string (no more than 15 characters) that indicates the protection password to be given to the file.
WriteResPassword - 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 - Variant. True to display a message when the file is opened, recommending that the file be opened as read-only.
CreateBackup - Variant. True to create a backup file.
AccessMode -
If this argument is omitted, the access mode isn't changed. This argument is ignored if you save a shared list without changing the file name. To change the access mode, use the ExclusiveAccess method.
ConflictResolution -
If this argument is omitted, the conflict-resolution dialog box is displayed.
AddToMru - Variant. True to add this workbook to the list of recently used files. The default value is False.
TextCodePage - Variant. Not used in U.S. English Microsoft Excel.
TextVisualLayout - Variant. Not used in U.S. English Microsoft Excel.
Local - 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).

Excel 2003

The following line saved to the default file format (xlFileFormat.xlWorkbookDefault)

ActiveWorkbook.SaveAs "C:\temp\file.xls" 

In 2007 you must specify the FileFormat you want to save in.
An Excel 2003 workbook is specified using (xlFileFormat.xlExcel8)

Close without a prompt

You can change the Saved property of the workbook to True and Excel will think that there are no changes to be saved.

ActiveWorkbook.Saved = True 

Saves the current workspace to the filename parameter.


© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext