How to kill workbooks

Question

The VBA code below is supposed to do the following : If a workbook file with the SavedSourcePath name exists then Open it then Close it then suppress it. It opens it correctly, but do not close it and I get an error "index is outside the (?)list (tableau in french)

Note that at beginning I don't know if the file exists and if so if it is open or close. I want in every case it to disappear.

SavedSourcePath = "/Users/Shared/TS1/optim.xlsm"••••ˇˇˇˇ

If Dir(SavedSourcePath) <> "" Then
    Workbooks.Open (SavedSourcePath)
    Workbooks(SavedSourcePath).Close saveChanges:=False
    Kill Workbooks(SavedSourcePath)
End If

Alternatively is there a way to save a workbook with a name which may exists and crash the previous one if it exists ? Witout having to answer a "do you want to…"

`Kill` deletes files, not workbook objects. You get the error because `Workbooks` should be indexed with the file name, not the full path. You don't need to index it at all because `Workbooks.Open` returns a workbook which you should save in a variable and then close. — GSerg, May 12 '19 at 19:23
Possible duplicate of [How to use workbook.saveas with automatic Overwrite](https://stackoverflow.com/q/14634453/11683) — GSerg, May 12 '19 at 19:30
GSerg I guess you are right on both counts, but I am still lost. Not very expert. I had found the other post and tried : Application.DisplayAlerts = False ActiveWorkbook.SaveAs FileName:="/Users/Shared/TS1/optim.xlsm", FileFormat:=52, CreateBackup:=False but I get an error "impossible to save under the name of an opened workbook" — Studix, May 12 '19 at 19:45
Do not open the existing workbook. Just save the current one. — GSerg, May 12 '19 at 19:51
GSerg thank you for your comments, but I am still in the dark. The file may exist or not and may be open or not. I would like to delete it or alternatively overwrite it by a new file without a "do you want…". Would you be as king as to give me an example ? — Studix, May 12 '19 at 19:58
It does not matter if the file exists after you set `DisplayAlerts = False`. It *does* matter that it is opened, it needs to be [closed first](https://stackoverflow.com/q/28163552/11683). The problem here is that you don't know if the opened workbook is in fact the one you want to close (if you have `folder1\1.xlsx` and `folder2\1.xlsx`, their workbooks will have the same name, `1.xslx`, and you won't know from looking at just `1.xlsx` whether it's the one you want to close, so loop over the opened workbooks and check their `.FullName`). — GSerg, May 12 '19 at 20:05
(Keep in mind that even that [won't work](https://devblogs.microsoft.com/oldnewthing/20110720-00/?p=10103) if you are using hard/sym links). — GSerg, May 12 '19 at 20:07
But if I use : SavedSourcePath = "/Users/Shared/TS1/optim.xlsm" Set wk = Workbooks.Open(SavedSourcePath) wk.Close SaveChanges:=False then the file is well identified but I still cannot close it; It seems the line "wk.Close SaveChanges:=False" simply does nothing — Studix, May 12 '19 at 20:11
Like I said [before](https://stackoverflow.com/questions/56102675/how-to-kill-workbooks?noredirect=1#comment98840279_56102675), you don't need to open the existing workbook. You only need to save the one you want to save. — GSerg, May 12 '19 at 20:13
If it is already opened before you run your code, you need to close it first, which you do by looping over the `Workbooks` collection and closing the ones that have `.FullPath` that you want to save under, like I [said](https://stackoverflow.com/questions/56102675/how-to-kill-workbooks?noredirect=1#comment98840470_56102675) above. You do not try to open anything in order to do that. — GSerg, May 12 '19 at 20:20

Ho Wai Yan Arthur · Answer 1 · 2022-07-14T10:02:27.147

Sub Whatever ()

Dim FolderPath as String

Dim NewWB as String

Dim FLName as String

FolderPath = "C:\Users\Shared\TS1"

NewWB = optim.xlsm

FLName = Dir(FolderPath)

Do While FLName <> ""

if Instr(1, FLName, NewWB) <> 0 Then

Workbooks.Open FolderPath & NewWB
Workbooks(NewWB).Close SaveChanges:=False
Kill FolderPath & NewWB

End If

FLName = Dir()

Loop

End Sub

#Suppress Any Warning by: Application.DisplayAlerts = False

score -2 · Answer 2 · answered May 13 '19 at 02:19

This may help:

Sub openFile()

'First make sure the files are closed
On Error Resume Next
Dim xlApp As Object
Set xlApp = GetObject(, "Excel.Application")
xlApp.DisplayAlerts = False
Call xlApp.Workbooks("originalFile.txt").Close
Call xlApp.Workbooks("newFile.csv").Close
Set xlApp = Nothing

'Delete the final file if it exist 
'the file can have the same name just need to be stored in different location
'or you will overwrite the existing file
Dim aFile As String
aFile = "C:\newlocation\newFile.csv"    
If Len(Dir$(aFile)) > 0 Then
     Kill aFile
End If

'Open the originalFile, run the code and save it to the desired location
'and give it a new filename and type
'if the file is not there, the program just ends
If Len(Dir("C:mydata\originalFile.txt")) > 0 Then

    Do Something

    ActiveWorkbook.SaveAs Filename:="C:\newlocation\newFile.csv", FileFormat:=xlCSV, _
    CreateBackup:=False
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True

End If

End Sub

How to kill workbooks

2 Answers2