Can't Kill xls files

Question

I have been using every version of Kill routines to delete xls files in the current directory with no success. Here is my code. I want to delete every xls file except wbCntl. Stepping through this code shows that everything works with the exception of the Kill swb command. (swb is public defined as a string)

    'Close all files except wbCntl
        swb1 = wbCntl.Name
        For Each wb In Application.Workbooks
            swb = wb.Name
            If swb <> swb1 Then
                Workbooks(swb).Close SaveChanges:=False
                On Error Resume Next
                Kill swb
                Err.Clear
            End If
        Next wb

Can anyone help me with this

Answer 1

I don't think that Application.Workbooks is what you want. The Excel 2003 VBA help gives the following meaning to this collection:

A collection of all the Workbook objects that are currently open in the Microsoft Excel application.

However, you want to iterate through Excel files in the current directory, which is a very different thing from that.

I am by no means an Excel VBA expert, so don't just take my word for this without checking, but: I could well imagine that you cannot just delete files at will from VBA, because including a macro that does such things in a spreadsheet document would be considered by many as dangerous / a potential security threat / a virus.

Answer 2

It's not working because the Name property is just the file name. The Kill method requires the full path. Try using the FullName property instead.

Also you can run into weirdness when you delete an item out of a collection you are iterating through using "For Each". Try this:

Public Sub Example()
    Dim lngIndx As Long
    Dim strFilePath As String
    For lngIndx = Excel.Workbooks.Count To 1& Step -1
        strFilePath = Excel.Workbooks(lngIndx).FullName
        If strFilePath <> ThisWorkbook.FullName Then
            Excel.Workbooks(lngIndx).Close False
            SafeKill strFilePath
        End If
    Next
End Sub

Private Sub SafeKill(ByVal path As String)
    On Error Resume Next
    Kill path
End Sub

Answer 3

Thanks to both stakx and especially Oorang. Your code worked perfectly. Sorry that It's taken me so long to work on this. Also, I'm not clear if this the way to respond to your good solutions. I'm new at this site. Would like to give thumbs up to you both. Grant