ActiveWorkbook.SaveAs - dealing with file already exists
check if the file exists, if not then use SaveAs, if it does, add a number,
and retest, incrementing the number until there's no existing file
Sub tester()
SaveFileAs "MyFile", "C:\Test\"
End Sub
Function SaveFileAs(sFilename As String, sPAth As String) As Boolean
Dim fn As String
Dim check As String
Dim ok As Boolean
Dim index As Long
fn = sPAth & sFilename & ".xls"
check = Dir(fn)
ok = (check = "")
Do Until ok
index = index + 1
fn = sPAth & sFilename & index & ".xls"
check = Dir(fn)
ok = (check = "")
Loop
ThisWorkbook.SaveAs fn
End Function
"Adrian" wrote:
Using Excel 97 SR-1.
I am using ActiveWorkbook.SaveAs to save an ".xls" file and I cannot
find any documentation on how to handle file-already-exists errors.
My intent is to try and save the file but if the filename is already
used then to alter the filename and save again, repeating with
different filenames until either the SaveAs works or the code chooses
to give up. Currently, Excel pops up a message asking whether I want to
replace the file, after clicking "no" I get another popup asking
whether I want to debug the code.
I do not want any pop ups. I just want to handle the
file-already-exists case in the code.
|