"on error" command not exiting if loop

[smakatura]

I want to look and see if a file exists (based on a date entered into an input box). If the file exists, I want to delete the file. If the file does not exist, I want the macro to move onto the next line without an error message.

The code below works when the file exists but gets stuck on the " Kill ("C:\Users\smakatura\Documents\call report\" & tmpdate & " tmp.csv")" when the file does not exist.

What would be the proper coding to accomplish my goal?

Dim tmpdate As String
    tmpdate = InputBox("Enter TMP Date")
 
   '
   If Len("C:\Users\smakatura\Documents\call report\" & tmpdate & " tmp.csv") <> 0 Then
       Kill ("C:\Users\smakatura\Documents\call report\" & tmpdate & " tmp.csv")
    End If
 
   On Error Resume Next
   Workbooks.Open filename:= _
        "C:\Users\smakatura\Documents\call report\book2.xlsx"

 

[mathsbeauty]

You may try putting "on error resume next" just before if loop.

 

[Richard Schollar]

Hi

You need to wrap the filename in Dir():

If Len(Dir("C:\Users\smakatura\Documents\call report\" & tmpdate & " tmp.csv")) > 0 Then
       Kill "C:\Users\smakatura\Documents\call report\" & tmpdate & " tmp.csv"
    End If

 

[smakatura]

kill command did have the file in (). for some reason it did not show up here but I did have it that way. but good suggestion because that would have caused an error.

putting the "on error resume next" before the loop worked. Thanks for the help.

 

[Norie]

smakatura

But you weren't using Dir, which was Richard's point I think.

Without Dir then all you would be doing is basically checking if the lenghth of the string 'C:\Users\smakatura\Documents\call report\" & tmpdate & " tmp.csv' was 0.

Which it's obviously not.

By using On Error... you might have hidden the error but not actually fixed the problem.

 

[smakatura]

I see what you were saying about using dir() with the kill command.

actually kill ("filename") works just fine. so the dir() is not required. but I will keep that in mind if I run into the issue with other commands that require a file name.

thanks

 

[mathsbeauty]

I think there is no need to use "if then " for the purpose. Just have the kill statement. If file is there it deletes otherwise it proceeds to the next statement. Of course you should keep "on error resume next" before kill statement.

 

[smakatura]

You are correct about the "if-then" statement not being necessary with the "on error" command.

thanks for the advise.

 

[Norie]

Who said anything about using Dir with Kill?

If your if statement is meant to check for the existence of a file that's where you should be using it.

 

[Norie]

Part of my message got cut off.

This is what your code should probably look like, with an added variable for fun.

Dim strFileName As String
Dim strPath As String
Dim tmpdate As String
 
    strPath = "C:\Users\smakatura\Documents\call report\"
    tmpdate = InputBox("Enter TMP Date")
    strFileName = tmpdate & " tmp.csv"
 
     ' check if file exists using Dir
    If Dir(strPath & strFileName) <> "" Then
 
        ' file exists so delete it
        Kill strPath & strFileName
 
    End If