"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:

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

smakatura

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

Well-known Member
Joined
Apr 28, 2004
Messages
75,392
Office Version
365
Platform
Windows
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

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

Board Regular
Joined
Apr 23, 2011
Messages
79
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:

smakatura

Board Regular
Joined
May 27, 2011
Messages
141
You are correct about the "if-then" statement not being necessary with the "on error" command.

thanks for the advise.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,392
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,392
Office Version
365
Platform
Windows
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
 

Forum statistics

Threads
1,085,429
Messages
5,383,629
Members
401,843
Latest member
stevensmith1

Some videos you may like

This Week's Hot Topics

Top