VBA - Delete all workbooks but the active, then close (and do not save) the active one

Question

i need to find a way to delete all the excel workbooks but the active one after some condition is fulfilled. I am new to VBA, so it is possible that I got here some very basic problem (but I couldn't find similar question here on SO). Here's my code:

Sub kill()

Dim wb              As Workbook
Dim A               As String

A = 2
If A = 1 Then
MsgBox "Everything is fine"
'The if condition is working just fine
Else
    Application.DisplayAlerts = False
    For Each wb In Application.Workbooks
        If Not (wb Is Application.ActiveWorkbook) Then
                Application.DisplayAlerts = False
                If wb.Path <> vbNullString Then
                    wb.ChangeFileAccess vbNormal
                    kill (wb.FullName)
                End If
                ThisWorkbook.Close SaveChanges:=False
        End If
    Next
End If
End Sub

The if condition is working well, but VBA seems to have a difficulties with the kill command, which confuses me since the "killing" part is working perfectly when not put inside of the If Not condition.

Thank you very much for any suggestions you could provide.

Best regards, Maritn

Kill command is used to delete file. You have to use Close command, for example: `wb.Close SaveChanges:=False` — Maciej Los, Nov 06 '17 at 07:36
You are certainly confusing things by telling VBA to call your subroutine with a parameter (`kill (wb.FullName)`) when you haven't specified that your subroutine requires a parameter (`Sub kill()`). (Don't use VBA functions / keywords as names for your own variables and/or procedures.) — YowE3K, Nov 06 '17 at 07:36
kill will not work on a open file. you have to close the workbook before deleting it. — h2so4, Nov 06 '17 at 07:40
it is not a good practice to define a user function/sub with the same name as a language statement. — Vincent G, Nov 06 '17 at 08:29
@YowE3K Yes, that was pretty much the main problem...Very dull mistake. Thank you very much! — M.Drsata, Nov 06 '17 at 08:40

score 1 · Answer 1 · edited Nov 06 '17 at 13:18

If you want just to close all workbooks but active one, you could use the code I paste below:

Dim wb As Workbook

For Each wb In Application.Workbooks
    If wb.Name <> ActiveWorkbook.Name Then
        wb.Save
        wb.Close
    End If
Next wb

If you want rather to close all workbooks except of the one that have this macro inside, replace ActiveWorkbook with ThisWorkbook. Other thing is to delete all files. This is obviously risky operation, so I would suggest to restrict it to the specific folder. It happened that I have one subroutine of this kind, see:

Sub DeleteFilesFromFolder()

Dim myPath



myFolder = Sheets("Main").Range("B4").Value

Set Fso = CreateObject("Scripting.FileSystemObject") 
For Each Filename In fldr.Files
    Filename.Delete True ' delete all files
Next 
End Sub

The path of the folder to clean is the specific cell, you can put it elsewhere, as you wish. It won't delete open workbook, if you want to avoid error messages, just use On Error Resume Next. So, my suggestion is first to close all workbooks, then delete closed.

VBA - Delete all workbooks but the active, then close (and do not save) the active one

1 Answers1