Solved Overwrite Write reserved file

Mstest / Awrdacpi
January 2, 2013 at 18:54:15
Specs: Microsoft Windows XP Professional, 2 GHz / 958 MB
Hi,

Please help digging into this code..
My file is a write-protected file which is readonly recommended.
The first attempt in saving the file is okay but if I save it again the second time it doesn't overwrite the file. A run time error appears

Run-time error '1004':
         Operation failed. 'C:\tracking files\Consolidated.xls' is write reserved.

Is there a way that I can overwrite the file?
The thing is that I will save my existing file to its current filename and add some date into it then saved it again to the original file name.

Sub Copy_Transfer()
    Dim savepath As Variant
    Dim PathAndFile As Variant
    Dim orig As Variant
    Dim SaveOrig As Variant

    ymd = Application.Text(Date, "YYYYMMDD")
    docname = "Consolidated"
    orig = "Consolidated.xls"
    fname = docname & " - " & ymd & ".xls"
    savepath = "C:\tracking files\"
    PathAndFile = savepath & fname
    SaveOrig = savepath & orig


   Set wb = ActiveWorkbook
    
    wb.ReadOnlyRecommended = False
    wb.WritePassword = ""
    ActiveWorkbook.Save
    wb.SaveAs Filename:=PathAndFile, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    wb.WritePassword = "a"
    wb.ReadOnlyRecommended = True
    wb.Save
    
    wb.ReadOnlyRecommended = False
    wb.WritePassword = ""
    wb.SaveAs Filename:=SaveOrig, FileFormat:=xlNormal, Password:="",  WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    wb.WritePassword = "a"
    wb.ReadOnlyRecommended = True
    wb.Save

End Sub


See More: Overwrite Write reserved file

Report •


#1
January 3, 2013 at 06:10:37
I'm not sure what you're attempting to do here (Why are you saving the workbook 5 times?), but you might want to look up Workbook.SaveCopyAs.

How To Ask Questions The Smart Way


Report •

#2
January 3, 2013 at 22:41:33
no.. I'm not saving the file 5 times..what I need is that I want to save my current file
"Consolidated.xls"
with the same filename but will add a date on it.
"Consolidated - 20130104.xls"
and then save it again with the original file name which is the
"Consolidated.xls"

it's like backing up the file but will still be working on the original file. I write the code on that way because my file is a write protected ffile.

I already changed it to SaveCopyAs as shown

Sub Copy_Transfer()
    Dim savepath As Variant
    Dim PathAndFile As Variant
    Dim orig As Variant
    Dim SaveOrig As Variant

    ymd = Application.Text(Date, "YYYYMMDD")
    docname = "Consolidated"
    orig = "Consolidated.xls"
    fname = docname & " - " & ymd & ".xls"
    savepath = "C:\tracking files\"
    PathAndFile = savepath & fname
    SaveOrig = savepath & orig


   Set wb = ActiveWorkbook
    
    wb.ReadOnlyRecommended = False
    wb.WritePassword = ""
    ActiveWorkbook.Save
    wb.SaveCopyAs Filename:=PathAndFile
    wb.WritePassword = "a"
    wb.ReadOnlyRecommended = True
    wb.Save
    
    wb.ReadOnlyRecommended = False
    wb.WritePassword = ""
    wb.SaveAs Filename:=SaveOrig
    wb.WritePassword = "a"
    wb.ReadOnlyRecommended = True
    wb.Save

End Sub

but my problem is when a message
A file named 'C:\....\Consolidated.xls' already exists in this location. Do you want to replace it?

and I clicked on NO it will have a run time error that method failed.


All I want is to save a file using macro that when it is already existing a message box/dialog box will be prompted if user would like to exist the file.
If YES then file will be overwritten.
If NO then user will have the option to input a name they would like to use.
If CANCEL then it will exit sub


Report •

#3
January 4, 2013 at 00:24:17
✔ Best Answer
Hi

I count 4 saves of the original workbook. I've highlighted the problem save which you can get rid of.

Also, were you meaning to put a password on the backup file? If so, there's another fix that's needed.


Sub Copy_Transfer()
    Dim savepath As Variant
    Dim PathAndFile As Variant
    Dim orig As Variant
    Dim SaveOrig As Variant

    ymd = Application.Text(Date, "YYYYMMDD")
    docname = "Consolidated"
    orig = "Consolidated.xls"
    fname = docname & " - " & ymd & ".xls"
    savepath = "C:\tracking files\"
    PathAndFile = savepath & fname
    SaveOrig = savepath & orig


   Set wb = ActiveWorkbook
    
    wb.ReadOnlyRecommended = False
    wb.WritePassword = ""
    ActiveWorkbook.Save ---------------------------------------- #1
    wb.SaveCopyAs Filename:=PathAndFile
    wb.WritePassword = "a"
    wb.ReadOnlyRecommended = True
    wb.Save --------------------------------------------------------------#2
    
    wb.ReadOnlyRecommended = False
    wb.WritePassword = ""
    wb.SaveAs Filename:=SaveOrig -----------------------------#3 - the problem save
    wb.WritePassword = "a"
    wb.ReadOnlyRecommended = True
    wb.Save ---------------------------------------------------------------#4

End Sub

See if this works ... It will save the backup with a password. You will get the error message if you save the backup twice in a day. I recommend adding the time to the backup name

Sub Copy_Transfer()
    Dim savepath As Variant
    Dim PathAndFile As Variant
    Dim orig As Variant
    Dim SaveOrig As Variant

    ymd = Application.Text(Date, "YYYYMMDD") & "-" & Application.Text(Time, "hhmm")

    fname = docname & " - " & ymd & ".xls"
    savepath = "C:\tracking files\"
    PathAndFile = savepath & fname

   Set wb = ActiveWorkbook
    
    wb.ReadOnlyRecommended = False
    wb.WritePassword = ""
    ActiveWorkbook.Save   ' ---------------------------------------- Save original
    wb.WritePassword = "a"   ' -------------------------------------Set password 
    wb.ReadOnlyRecommended = True
    wb.SaveCopyAs Filename:=PathAndFile '-----------------Save Backup with Password

End Sub



Report •

Related Solutions

#4
January 7, 2013 at 18:00:11
Thank you :)

Report •


Ask Question