"on error" command not exiting if loop

smakatura

Board Regular
Joined
May 27, 2011
Messages
141
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?


HTML:
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"
 
Last edited:

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Hi

You need to wrap the filename in Dir():

Code:
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
 
Upvote 0
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.
 
Upvote 0
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.:)
 
Upvote 0
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
 
Upvote 0
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.
 
Last edited:
Upvote 0
You are correct about the "if-then" statement not being necessary with the "on error" command.

thanks for the advise.
 
Upvote 0
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.
 
Upvote 0
Part of my message got cut off.

This is what your code should probably look like, with an added variable for fun.
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top