Kill *.xls

[davidam]

Can anyone tell me why this fails, the folder (range E1) is empty, but I get a "file not found" error on the second line

If Range("E1").Value & "\*.xls" <> "" Then
Kill Range("E1").Value & "\*.xls"
End If

Thank you

 

[ChrisOswald]

if E1 is "", then you're testing if "\*.xls"<>"", which evaluates true...

Provided the folder exists, you can fix it by removing & "\*.xls" from the If statement

 

[davidam]

E1 is a folder that does exist...like C:\TempDL

 

[ChrisOswald]

Do you have any .xls files in that folder when the code is ran? So long as you have at least one, and all of the files are in a state that they can be deleted from, you shouldn't get an error.

 

[davidam]

Sometimes there are and sometimes not...it errored when there was no file in there...I thought it would bypass the Kill line if it found the folder empty?

 

[ChrisOswald]

No, it needs to try to kill something. you can do some error handling to trap the "No .xls files in folder error".

Sub TEstIt()
 
    On Error GoTo killerror
    Kill Range("E1").Value & "\*.xls"
    On Error GoTo 0
 
    Exit Sub
 
killerror:
    Select Case Err.Number
    Case 53
        Resume Next
    Case Else
        MsgBox Err.Description, vbCritical, "Error Number: " & Err.Number
        End
    End Select
 
End Sub

 

[davidam]

Yes, I think that is the ticket...Thank you!

 

[Ruddles]

As Chris said, Range("E1").Value & "\*.xls" will never equal "" because it's a string which contains, at the very least, the characters "\*.xls". It isn't testing whether those files actually exist.

Try this:-

If [B][COLOR=red]Dir([/COLOR][/B]Range("E1").Value & "\*.xls"[COLOR=red][B])[/B][/COLOR] <> "" Then
  Kill Range("E1").Value & "\*.xls"
End If

Try to avoid using the On Error directive when you can test for the condition directly as this produces tidier code.

 

[Norie]

You can test if there are any xls files in the folder using Dir.

If Dir(Range("E1").Value & "\*.xls") <> "" Then
       Kill Range("E1").Value & "\*.xls" 
End If

 

[ChrisOswald]

I forgot all about Dir()... Thanks.