Delete Files using VBA
There are many methods to delete the file:- simple VBA Kill method, using FileSystemObject etc. Here is one snippet my friend Devakottai PaneerSelvam used for deleting files (if I can say it, temporarily). The advantage here is that the deleted file is available in Recycle Bin for the user to restore if needed.
Private Type SHFILEOPSTRUCT
hwnd As Long
wFunc As Long
sFrom As String
sTo As String
fFlags As Integer
fAnyOperationsAborted As Boolean
hNameMappings As Long
lpszProgressTitle As String
End Type
Private Declare Function SHFileOperation Lib "shell32.dll" Alias "SHFileOperationA" _
(ByRef lpFileOp As SHFILEOPSTRUCT) As Long
Private Const FO_DELETE = &H3
Private Const FOF_SILENT = &H4
Private Const FOF_NOCONFIRMATION = &H10
Private Const FOF_ALLOWUNDO = &H40
Sub DeleteFileUsingAPI()
Dim oFilAPI As SHFILEOPSTRUCT
Dim lReturn As Long
Dim sFile As String ' File that needs to be deleted
sFile = "C:\ Temp\VBADUD\Hints and Tips.htm"
With oFilAPI
.wFunc = FO_DELETE
.sFrom = sFile
.sTo = vbNullChar
.fFlags = FOF_SILENT + FOF_NOCONFIRMATION + FOF_ALLOWUNDO
End With
' Use WinAPI User Defined Function
lReturn = SHFileOperation(oFilAPI)
Err_Delete:
MsgBox(Err.Number & " - " & Err.Description)
Err.Clear()
Resume Next
End Sub
Delete Files using VBA, Temporary Deletion using VBA, VBA Code to send files to Recycle Bin
See also:
Thank you. Nice work.
ReplyDeleteThis works lovely for files on C:\ but I find that if I've got a file on a network share, it doesn't go to the local Recycle Bin. Where does it go? Or is some network configuration prohibiting this?
ReplyDeleteAnyone know how to update this so it runs with 64 bit Excel?
ReplyDelete