ActiveWorkbook.SaveAs - dealing with file already exists

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.

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.
>
>

Hi


Here is some compilation from 2 of my projects

....
' Asking for file name
Const fTitle = "Select File!"
Const FilterList = "Templates (*.XLS), *.XLS"
fFullName = Application.GetOpenFilename(Title:=fTitle,
FileFilter:=FilterList)
If fFullName = "False" Then
MsgBox ("No file to open!")
ActiveWindow.Close
Exit Sub
End If
' Storing file name and path, and sheet name
varFile = fFullName
Do While InStr(fName, "\") > 0
fName = Mid(fName, CLng(InStr(fName, "\") + 1))
Loop
varPath=Left(fFullName,Len(varFile))

varSheet = "MySheet"
....

' Checking, that file exist, and opening it or creating a new one
Set fs = Application.FileSearch
With fs
.LookIn = varPath
.Filename = varFile
If .Execute(SortBy:=msoSortByFileName,
SortOrder:=msoSortOrderAscending) > 0 Then
' If found, then open
Workbooks.Open (varPath & varFile)
Else
' If not found, then create a new workbook with sheet MySheet in
it, and save it
Workbooks.Add
ActiveWorkbook.Sheets("Sheet1").Name = varSheet
ActiveWorkbook.SaveAs (varPath & varFile)

End If
Workbooks(varFile).Activate
End With
....


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )



"Adrian" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

Thank you, Patrick. The "dir" function achieves what I wanted. I knew
that there should be such a function but I could not find it.

Also, to Arvi, I will study Application.FileSearch and the rest of the
code that you posted.

the DIR( ) function works well for determining if ANY file or path exists
well too.

I needed a way to know where the FTP.EXE program supplied with the windows
install resided. Different operating systems keep it in different places...

CheckWindows = Dir("C:\Windows\System32\FTP.EXE")
CheckWinNT = Dir("C:\WinNT\System32\FTP.EXE")

allowed me to alter the way I call the FTP so my spreadsheet can now work
deployed on any of the current operating systems with out having to make
changes.


--
Regards,
John


"Adrian" wrote:

> Thank you, Patrick. The "dir" function achieves what I wanted. I knew
> that there should be such a function but I could not find it.
>
> Also, to Arvi, I will study Application.FileSearch and the rest of the
> code that you posted.
>
>