Test for file existence
 

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

--
Josep

-----------------------------------------------------------------------
Joseph's Profile: http://www.excelforum.com/member.php...nfo&userid=563
View this thread: http://www.excelforum.com/showthread.php?threadid=27773

Test for file existence
 

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

--
Regards,
Tom Ogilvy


"Joseph" wrote in message
...

Hi,

I have a worksheet that saves as a csv. What I need to do is enable
people to save more than once without losing the file but without
giving them dialogues. The only way I can think to do this is to check
to see if the file exists and if it does, kill it and replace it with
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") & " Activity
Log.csv")

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:= _
"P:\clevedon staff\activities\" & Application.UserName & "\" &
Application.UserName & " " & Format(Now, "DDMMYY") & " Activity
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 it
can't save over a file that DOES exist. Help!

thanks


--
Joseph


------------------------------------------------------------------------
Joseph's Profile:
http://www.excelforum.com/member.php...fo&userid=5637
View this thread: http://www.excelforum.com/showthread...hreadid=277733