is it possible to delete a workbook with code?

[Djblois]

I have been testing if you can delete a workbook with code (A regular workbook/ not an add-in). I have looked up in the help, and even macro recorded deleting a workbook, but to my dismay nothing has shown up. Is this possible? I know how to reference the workbook but I can't find a command to delete it?

Daniel

[mvidas]

Hi Daniel,

You can't have vba code in a workbook to delete itself, but you can have it delete any other file.

If you know the name of the file and it isn't open:Kill "C:\file.xls"Or if you need to close it first, Dim vName As String
With Workbooks("filename.xls")
vName = .FullName
.Close False
End With
Kill vNameMatt

[Djblois]

Matt,

How come it doesn't show up in the macro recorder?

[mvidas]

How did you delete the file when recording it? Deleting a file isn't any of Excel's built-in functions so the Recorder didnt really know what you did, I'm guessing you opened up an Open or SaveAs dialog and deleted it from there (which is more or less a mini-explorer window).

And unfortunately, not everything you do in excel shows up in the macro recorder. Good for most things, but not everything

Matt

[Bob Phillips]

Hi Daniel,

You can't have vba code in a workbook to delete itself, but you can have it delete any other file.

Yeah, you can!



On Error GoTo ErrorHandler
With ActiveWorkbook
If .Path <> "" Then
.Saved = True
.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
End If
End With
Exit Sub

ErrorHandler:
MsgBox "Fail to delete file: " & ActiveWorkbook.FullName
Exit Sub

[Simon Lloyd]

Bob, won't that code leave the Excel Application window still open?

[Bob Phillips]

Yes, but why is that a problem. The statement was ... You can't have vba code in a workbook to delete itself, but you can have it delete any other file

[Simon Lloyd]

You're right of course!, but i was just tying to provoke thought as to whether it could be tidied up and close the Application, is it also possible to use Application.Quit or would it be better to Add a new workbook and close that without saving changes?

[Bob Phillips]

Depends upon the objective. Either course is valid, just for different objectives.