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

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


"Bob Zimski" wrote in message
...
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

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


--

Regards,
Nigel




"Bob Zimski" wrote in message
...
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

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


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


"Bob Zimski" wrote in message
...
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

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
http://www.higherdata.com
Workbook Compare - Excel data comparison
Free & Pro versions


"Bob Zimski" wrote in message
...
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

I think that part is OK:

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

--
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison
Free & Pro versions


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


"Bob Zimski" wrote in message
...
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

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


"Tim Zych" <feedback at higherdata dt com wrote in message
...
I think that part is OK:

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

--
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison
Free & Pro versions


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


"Bob Zimski" wrote in message
...
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