File saving

[Avner mediouni]

Hi All,
I would to write a macro that saves automatically an open workbook with the
name that is on cell B2, but if the file already exist the macro add a
"running number" to the file name (i.e. FileName1, FileName2,etc.,)

Can anyOne help?

Thanks'
Avner Mediouni
R&D Physicist

 

[Bob Phillips]

Sub testit()
Dim sBase As String
Dim sFile As String
Dim i As Long
Dim rtn

sBase = Range("B2").Value
If Right(sBase, 4) = ".xls" Then
sBase = Left(sBase, Len(sBase) - 4)
End If
sFile = sBase
Do
rtn = FileExists(sFile)
If rtn Then
i = i + 1
sFile = sBase & " " & i
End If
Loop Until Not rtn
ActiveWorkbook.SaveAs sFile
End Sub



'-----------------------------------------------------------------
Function FileExists(File) As Boolean
'-----------------------------------------------------------------
Dim sFile As String
On Error Resume Next
sFile = Dir(File & ".xls")
If sFile <> "" Then
FileExists = True
End If
End Function