XL: Save Method Saves Read-Only File to Current Directory

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel 98 Macintosh Edition

SYMPTOMS

When you run a Microsoft Visual Basic for Applications procedure to save a file using the Save method, if the file is opened as read-only, the file may be saved to the current directory or folder, instead of an error message appearing indicating that the file cannot be saved.

CAUSE

This behavior occurs when the file that you are saving is open, has a read- only protection status, and the current directory or folder is not the directory or folder that contains the file. In this case, the file is saved to the current directory or folder without displaying an error message explaining that the file is read-only.

This is incorrect behavior because the Save method normally saves the file to the folder or directory that contains the file. A read-only file should not be saved using the Save method because the Save method does not allow you to specify where to save a file.

For example, when you run the following procedure in Microsoft Excel for the Macintosh, if Macintosh HD:Temp is not the current folder, the file BOOK1.XLS is created in both the Macintosh HD:Temp folder and in the current folder:

   Sub SaveFile()
      Workbooks.Add
      ActiveWorkbook.SaveAs FileName:="Macintosh HD:Temp:Book1.xls"
      ActiveWorkbook.Close
      Workbooks.Open FileName:="Macintosh HD:Temp:Book1.xls", _
         ReadOnly:=True
      ActiveWorkbook.Save
   End Sub

   Replace existing '<filename>'?

   Run-time error '1004':

   Cannot save as that name. Document was opened as read-only.

RESOLUTION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/default.asp

Method 1: Use the SaveAs Method

Use the SaveAs method to save the file to another folder or directory as in the following examples:

Macintosh:

   ActiveWorkbook.SaveAs FileName:="Macintosh HD:Excel:Book1.xls"

   ActiveWorkbook.SaveAs FileName:="C:\Excel\Book1.xls"

Method 2: Close and Reopen the File

Close the file and open it again with read-write access in order to save the file to the same directory.