Backup Copy of Source File

[Bob Zimski]

I would like to make a make a backup copy and dump it into one directory
deeper called 'Archive' before manipulating the file in the current
directory. If the filename already exists in the Archive directory, then I
would want to add a '-01' or '-02' etc.. to the next revision level. I know
about the FileCopy command, but have no idea how to determine if the file
already exists inthe archive directory and therefore add the next available
revision number.

Thanks

 

[Bernie Deitrick]

Bob,

Sub BackUpWithIncrementedName()
Dim i As Integer
Dim myFN As String
i = 0

FindAName:
i = i + 1
myFN = ThisWorkbook.Path & "\Archive\" & _
Replace(ThisWorkbook.Name, ".xls", Format(i, "-00") & ".xls")
If Dir(myFN) = "" Then
GoTo DoTheSave
Else
GoTo FindAName
End If

DoTheSave:

ThisWorkbook.SaveCopyAs myFN
MsgBox "I just saved a copy as " & myFN

End Sub

HTH,
Bernie
MS Excel MVP

 

[Nigel]

I use the following code to test the presence of a file, if it exists then
it loops until it finds the next serial number....you maybe able to adapt?

If these exist already
File_Rep.xls
File_Rep(1).xls
File_Rep(2).xls

then the file is named File_Rep(3)


Dim myPath As String, myFile As String, myExt As String, mySerial As String

mySerial = ""
myPath = "C:\Data\"
myFile = "File_Rep"
myExt = ".xls"

' create output using sequence 1 to n if report already exists
If Len(Dir(myPath & myFile & mySerial & myExt)) > 0 Then

Do While Len(Dir(myPath & myFile & mySerial & myExt)) > 0
mySerial = "(" & Val(Mid(mySerial, 2)) + 1 & ")"
Loop

End If

ThisWorkBook.SaveAs Filename:=myPath & myFile & mySerial & myExt

 

[Bernie Deitrick]

I should have noticed that this will fail and go into an infinite loop if you have 99 copies. Add
in this to prevent that:

FindAName:
i = i + 1
If i > 99 Then
MsgBox "You have waaaaay too many backup files...."
Exit Sub
End If
.....


HTH,
Bernie
MS Excel MVP

 

[Tim Zych]

Here's a couple of reusable functions which are a variation of Bernie's
excellent approach.

Sub tester()
MsgBox GetNextIncrementedFullFilename("D:\Test", ".xls")
End Sub

Function GetNextIncrementedFullFilename(RootFilename As String, Optional
ByVal FileExt As String = ".xls") As String
Dim szFullFilename As String, i As Long
szFullFilename = RootFilename & FileExt
Do
If FileExists(szFullFilename) Then
' Found file, so increment it by one
i = i + 1
szFullFilename = RootFilename & "_" & Format(CStr(i), "00") &
FileExt
Else
' No file with this name.
Exit Do
End If
Loop
GetNextIncrementedFullFilename = szFullFilename
End Function

Public Function FileExists(ByVal strFullFileName As String) As Boolean
On Error GoTo ErrHandler
If Dir(strFullFileName) <> "" And Len(strFullFileName) > 0 Then
FileExists = True
Else
FileExists = False
End If
Exit Function
ErrHandler:
FileExists = False
End Function

 

[Tim Zych]

I think that part is OK:

In the immediate window:
?format(9999,"-00")
-9999

 

[Bernie Deitrick]

Tim,

Very true, but I was thinking of sorting based on names... 10 vs 100, 101,
etc...

I guess I didn't explain myself (at all).

Bernie