Is there a way to preserve my original file and never overwrite it?

cmazur71

Board Regular
Joined
Aug 7, 2003
Messages
61
I have a spreadsheet that runs a series of macros, that lets a user import data, clean and reformat the data, then SaveAs another file name. I don't want to ever overwrite the original file.
I am using this code to try to SaveAs another name, but sometimes the original file is still getting saved. Is there a good way to ensure that my original file never get overwritten once the macros have started?
VBA Code:
Sub SaveAsAnotherFile()
       
    Application.DisplayAlerts = False
    TodayDate = Format(Date, "yyyy-mm-dd")
   
    NewFileName = TodayDate & " Quotation " & Range("E1") & " - " & Range("B6") & " - " & Range("A11")
  
    FileSaveAsName = Application.GetSaveAsFilename(NewFileName, filefilter:="Excel Files (*.xlsm), *.xlsm")
   
    If FileSaveAsName = "False" Then
        Do
            MsgBox "Please save this as another file before continuing...", vbOKOnly
            FileName = Application.GetSaveAsFilename(NewFileName, filefilter:="Excel Files (*.xlsm), *.xlsm")
        Loop Until FileName <> "False"
    End If
   
    ActiveWorkbook.SaveAs FileName:=FileSaveAsName, FileFormat:=52
    Application.DisplayAlerts = True

   
End Sub
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,639
Office Version
  1. 365
Platform
  1. Windows
You can save it as read only.

To the best of my knowledge, you can't change this on an existing workbook, so here's the method that I've used previously (long winded, I've tried to include all steps).

Close the original file (all users if it is shared), then go to the file in the windows file browser and rename it (I usually just append 'old' to the existing name).

Open the renamed workbook, then go to File > Save As.

If you see the 'Save As' dialog box / popup window (older excel versions) then you're good for the next step, otherwise click on 'More options' (office 365, possibly excel 2019).

In the Save As dialog / popup, change the file name to whatever it should be (original name), then click the Tools button next to Save.

Select 'General options' then check the Read only recommended box, enter a password to modify (leave password to open empty).

Click OK, then Save.

Without the password, users can open the file as read only and edit anything, but they will not be able to save the changes to the original document, any attempt to save will warn the user and open the Save As dialog for them to save a copy of the file.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,752
Messages
5,638,162
Members
417,011
Latest member
Amaden95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top