One of the most common tasks for VBA developers is to write code which modifies another Excel file, perhaps to update a product list or to merge two data sets.
But a disadvantage in using VBA is that, unlike regular Excel operations there is no undo command.
And while it's nice to think that we test our programs thoroughly it makes sense to have a backup plan.
This article will show you how to save a copy of the file you're modifying, before any code makes unforeseen changes to the file that can't be undone.
Making A Copy Of The File With VBA You can make a copy of a file without accessing the File System Object(FSO) simply by defining the path and file name, plus the path and file name of the copied file.
In our scenario, we'll assume you're working on a file called "test.
xls" which is in a folder called "files" under the active workbook.

Dim myFile, myPath myFile = "test.
xls" myPath = ActiveWorkbook.
Path & "\files\" myPath = myPath & myFile FileCopy myPath, Replace(myPath, myFile, "COPY" & myFile) The code is fairly easy to follow with the replace command renaming the file by placing "COPY" before the original file name.
Putting A Date Stamp On The Copied File It might be a good idea to include the date and time as part of the copied file's new name, so at a glance it's obvious when the file was last saved.
Although you can access date and time data through the File System Object there's nothing like visually scanning the contents of a folder to see whether it's time to make a copy.
Instead of renaming the file "COPY" & File, you can use the current date which returns a file name in this format: "20-12-12FileName.
xls".

myDate = Format(Date, "dd-mm-yy") FileCopy myPath, Replace(myPath, myFile, myDate & myFile) Don't be tempted to use the date function on its own as that will return a format of dd/mm/yy and will mean the file can't be found because of the extra / symbols.
NOTE, you can't save a copy of the current workbook this way, you'll get an access denied message.
Summary Saving a copy of any file you are attempting to modify is good programming practice.
And although this VBA code is a simple one, it's a few lines of code that could save you frustration and stress and is a definite candidate to add to your code library.