Excel VBA to move file to recycle bin

Loibills · May 24th 2016

Thru searching, I found VBA code for moving files from one folder to another folder. But what I am looking for is either move files to recycle bin or deleting or removing a file. Thank you for any help could be provided.

cytop · May 24th 2016

Re: Excel VBA to move file to recycle bin

The standard VBA Kill command permanently deletes a file. You can only send a file to the Recycle Bin using the Windows API

The following will only work in 32 bit Office installions and gives the option to move a file to the recycle bin or delete it permanently. If you choose to permanently delete you will be prompted same as if using Windows Explorer. This can be overridden using the NoPrompt flag (True)

Copy into a new module.

Code

Private Type SHFILEOPTSTRUCT
   hWnd                 As Long
   wFunc                As Long
   pFrom                As String
   pTo                  As String
   fFlags               As Integer
   fAnyOperationsAborted As Long
   hNameMappings        As Long
   lpszProgressTitle    As Long
End Type


Private Declare Function SHFileOperation Lib "Shell32.dll" _
                                         Alias "SHFileOperationA" (lpFileOp As SHFILEOPTSTRUCT) As Long


Private Const FO_DELETE = &H3
Private Const FOF_ALLOWUNDO = &H40
Private Const FOF_NOCONFIRMATION = &H10


Public Sub DeleteFile(Filename As String, _
                      Optional Recycle As Boolean = True, _
                      Optional NoPrompt As Boolean = False)


   Dim fop              As SHFILEOPTSTRUCT
   Dim Flags            As Integer


   On Error GoTo Catch


   With fop
      .wFunc = FO_DELETE
      .pFrom = Filename
      .fFlags = IIf(Recycle, FOF_ALLOWUNDO, 0) + IIf(NoPrompt, FOF_NOCONFIRMATION, 0)
   End With


   If SHFileOperation(fop) <> 0 Then Err.Raise 30001, "Deletefile", "Unable to " IIf(recycle, "recycle ", "delete ") " & UCase(Filename)
   Exit Sub


Catch:


   MsgBox "ERROR: " & Err.Description, vbExclamation, "DeleteFile"


End Sub

Display More

Loibills · May 24th 2016

Re: Excel VBA to move file to recycle bin

First, I would like to thank you for replying to my inquiry so fast and with very detail instruction and explanation. Needless to say, I am very new at these VBA stuffs, and literally exhausted looking for answer to my problem. I am very handicap in many ways: English barrier, computer illiterate, etc. This is what I have in my worksheet: a HYPERLINK column to link 1,300 presentation files (extension ppt.) in a desktop folder, and a column where the VALUE of the hyperlink's data is pasted (the title of the presentation.). So when I click on the hyperlink, the linked file is opened. What I would like to do is: there is a way I can open the folder and go straight to the file where is located and be able to delete.
Thru searching from the internet & YouTube video, I have managed to have the following code written. There is a manual step in this deleting process that I am looking for help. So hopefully I no longer have to do this step manually anymore. The manual step, I have to go to the column where the value I stored (the complete name of the linked file), and copy "that information" so when I run the code, the dialog prompt come up, I then paste the info by hitting Ctr +V. (I might know how to copy the value, and use the keyboard shortcut key "to paste", but other user might not know. and I am sharing this work with other people.) So is there a way, a code or function could be written so that when I click on the cell where I want the file to be deleted, the "computer" could look at that as "Target Cell" and copy the value or the info in that cell without having me to copy and paste the info in the prompt dialog box? Here is the code I got:

Code

Sub TestToDeleteFile()




Dim fso As Scripting.FileSystemObject
Dim file As String
Dim FileName As String




FileName = InputBox("Name")




file = Environ("userprofile") & "\desktop\bss" & FileName




Set fso = CreateObject("Scripting.FileSystemObject")




If fso.FileExists(file) Then
    MsgBox "FOUND and DELETE"
    fso.DeleteFile file, True
Else
    MsgBox file & " does not exist or has already been deleted!" _
            , vbExclamation, "File not Found"
End If




End Sub

Display More

cytop · May 25th 2016

Re: Excel VBA to move file to recycle bin

It would have been very helpful if you had described your situation as in your 2nd post to start with rather than a short "either move files to recycle bin or deleting or removing a file" summary.

The answer I gave will either move a file to the recycle bin or permanently delete it, depending on the options you choose when calling it; which is exactly what I understood your request to be asking.

The code you posted does delete a file but it does not give an option to recycle it - the file is permanently deleted.

camle · Jun 28th 2016

Re: Excel VBA to move file to recycle bin

Is there a 64 Bit version? or maybe a different way in VBA?

Code

Private Type SHFILEOPTSTRUCT      hWnd                 As Long 
    wFunc                As Long 
    pFrom                As String 
    pTo                  As String 
    fFlags               As Integer 
    fAnyOperationsAborted As Long 
    hNameMappings        As Long 
    lpszProgressTitle    As Long 
End Type 
 
Private Declare Function SHFileOperation Lib "Shell32.dll" _ 
Alias "SHFileOperationA" (lpFileOp As SHFILEOPTSTRUCT) As Long 
 
Private Const FO_DELETE = &H3 
Private Const FOF_ALLOWUNDO = &H40 
Private Const FOF_NOCONFIRMATION = &H10 
 
Public Sub DeleteFile(Filename As String, _ 
    Optional Recycle As Boolean = True, _ 
    Optional NoPrompt As Boolean = False) 
     
    Dim fop              As SHFILEOPTSTRUCT 
    Dim Flags            As Integer 
     
    On Error Goto Catch 
     
    With fop 
        .wFunc = FO_DELETE 
        .pFrom = Filename 
        .fFlags = IIf(Recycle, FOF_ALLOWUNDO, 0) + IIf(NoPrompt, FOF_NOCONFIRMATION, 0) 
    End With 
     
    If SHFileOperation(fop) <> 0 Then Err.Raise 30001, "Deletefile", "Unable to " IIf(recycle, "recycle ", "delete ") " & UCase(Filename) 
    Exit Sub


     
Catch: 
     
    MsgBox "ERROR: " & Err.Description, vbExclamation, "DeleteFile" 
     
End Sub

Display More

Code

Private Declare Function SHFileOperation Lib "Shell32.dll" _
Alias "SHFileOperationA" (lpFileOp As SHFILEOPTSTRUCT) As Long

And

Code

If SHFileOperation(fop) <> 0 Then Err.Raise 30001, "Deletefile", "Unable to " IIf(recycle, "recycle ", "delete ") " & UCase(Filename)

Are in red

Thanks

Excel VBA to move file to recycle bin

Participate now!

Share