Test for file existence

  • Thread starter Thread starter Joseph
  • Start date Start date
J

Joseph

Hi,

I have a worksheet that saves as a csv. What I need to do is enabl
people to save more than once without losing the file but withou
giving them dialogues. The only way I can think to do this is to chec
to see if the file exists and if it does, kill it and replace it wit
the new one. This is my code but it isn't working at all!

On Error Resume Next

Kill ("P:\clevedon staff\activities\" & Application.UserName & "\"
Application.UserName & " " & Format(Now, "DDMMYY") & " Activit
Log.csv")

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:= _
"P:\clevedon staff\activities\" & Application.UserName & "\"
Application.UserName & " " & Format(Now, "DDMMYY") & " Activit
Log.csv", FileFormat:=xlCSV _
, Password:="", WriteResPassword:=""
ReadOnlyRecommended:=False, _
CreateBackup:=False
Application.Quit

I'm assuming that the On Error Resume Next is picking up two errors
one that the file I'm trying to kill doesn't exist, and two that i
can't save over a file that DOES exist. Help!

thank
 
sName = "P:\clevedon staff\activities\" & _
Application.UserName & "\" &Application.UserName _
& " " & Format(Now, "DDMMYY") & " ActivityLog.csv")

If dir(sName) <> "" then
kill sName
End if
ActiveWorkbook.SaveAs FileName:= _
sName, FileFormat:=xlCSV _
, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, _
CreateBackup:=False
Application.Quit
 
Back
Top