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" <(E-Mail Removed)> wrote in message
news:EE7EB497-9556-4B03-9C4B-(E-Mail Removed)...
>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
(E-Mail Removed)



"Bob Zimski" <(E-Mail Removed)> wrote in message
news:EE7EB497-9556-4B03-9C4B-(E-Mail Removed)...
>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
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:EE7EB497-9556-4B03-9C4B-(E-Mail Removed)...
>>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" <(E-Mail Removed)> wrote in message
news:EE7EB497-9556-4B03-9C4B-(E-Mail Removed)...
>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
news:(E-Mail Removed)...
>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
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:EE7EB497-9556-4B03-9C4B-(E-Mail Removed)...
>>>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
news:%23ZGNK%(E-Mail Removed)...
>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
> news:(E-Mail Removed)...
>>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
>> news:(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:EE7EB497-9556-4B03-9C4B-(E-Mail Removed)...
>>>>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
>>>
>>>
>>
>>
>
>