Calling a Function

[jeran042]

Good morning all,

I found a function that I think can be very useful for me, although, Im not sure how to call it in a procedure. Here is the function:

Public Function Recycle(FileSpec As String, Optional ErrText As String) As Boolean
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Recycle
' This function sends FileSpec to the Recycle Bin. There
' are no restriction on what can be recycled. FileSpec
' must be a fully qualified folder or file name on the
' local machine.
' The function returns True if successful or False if
' an error occurs. If an error occurs, the reason for the
' error is placed in the ErrText varaible.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim SHFileOp As SHFILEOPSTRUCT
Dim Res As Long
Dim sFileSpec As String

ErrText = vbNullString
sFileSpec = FileSpec

If InStr(1, FileSpec, ":", vbBinaryCompare) = 0 Then
    ''''''''''''''''''''''''''''''''''''''
    ' Not a fully qualified name. Get out.
    ''''''''''''''''''''''''''''''''''''''
    ErrText = "'" & FileSpec & "' is not a fully qualified name on the local machine"
    Recycle = False
    Exit Function
End If

If Dir(FileSpec, vbDirectory) = vbNullString Then
    ErrText = "'" & FileSpec & "' does not exist"
    Recycle = False
    Exit Function
End If

''''''''''''''''''''''''''''''''''''
' Remove trailing '\' if required.
''''''''''''''''''''''''''''''''''''
If Right(sFileSpec, 1) = "\" Then
    sFileSpec = Left(sFileSpec, Len(sFileSpec) - 1)
End If


With SHFileOp
    .wFunc = FO_DELETE
    .pFrom = sFileSpec
    .fFlags = FOF_ALLOWUNDO
    '''''''''''''''''''''''''''''''''
    ' If you want to supress the
    ' "Are you sure?" message, use
    ' the following:
    '''''''''''''''''''''''''''''''
    .fFlags = FOF_ALLOWUNDO + FOF_NOCONFIRMATION
End With

Res = SHFileOperation(SHFileOp)
If Res = 0 Then
    Recycle = True
Else
    Recycle = False
End If

End Function

I found this function at: http://www.cpearson.com/excel/recycle.aspx

My question is, how do I call this function in a sub? My goal is to have this function run before I run a loop which will output a bunch of reports to a specified folder on a monthly basis,

Very much appreciated,

 

[The_Doc_Man]

From what I see in the post above, something must be missing. I do not know of any intrinsic item available in VBA with the name of SHFILEOPSTRUCT. That means it comes from some specific library declaration or external STRUCT declaration, and I don't see either kind of declaration in the code you presented. I also do not know of a function like SHFileOperation as part of the File System Object.

Therefore, I would bet that without the rest of the supporting infrastructure, you CAN'T get this to work in your VBA. However, if all you want is to delete a particular file silently, the File System Object can perform the deletion, which by default sends the file to the recycle bin anyway.

Search this forum for "Delete File" to see if anyone else has performed a similar function.

 

[jeran042]

From what I see in the post above, something must be missing. I do not know of any intrinsic item available in VBA with the name of SHFILEOPSTRUCT. That means it comes from some specific library declaration or external STRUCT declaration, and I don't see either kind of declaration in the code you presented. I also do not know of a function like SHFileOperation as part of the File System Object.

Therefore, I would bet that without the rest of the supporting infrastructure, you CAN'T get this to work in your VBA. However, if all you want is to delete a particular file silently, the File System Object can perform the deletion, which by default sends the file to the recycle bin anyway.

Search this forum for "Delete File" to see if anyone else has performed a similar function.

Thank you, and you are correct, I failed to post this code:

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

Private Declare Function PathIsNetworkPath Lib "shlwapi.dll" _
    Alias "PathIsNetworkPathA" ( _
    ByVal pszPath As String) As Long

Private Declare Function GetSystemDirectory Lib "kernel32" _
    Alias "GetSystemDirectoryA" ( _
    ByVal lpBuffer As String, _
    ByVal nSize As Long) As Long

Private Declare Function SHEmptyRecycleBin _
    Lib "shell32" Alias "SHEmptyRecycleBinA" _
    (ByVal hwnd As Long, _
     ByVal pszRootPath As String, _
     ByVal dwFlags As Long) As Long

Private Declare Function PathIsDirectory Lib "shlwapi" (ByVal pszPath As String) As Long

Private Const FO_DELETE = &H3
Private Const FOF_ALLOWUNDO = &H40
Private Const FOF_NOCONFIRMATION = &H10
Private Const MAX_PATH As Long = 260

Private Type SHFILEOPSTRUCT
    hwnd As Long
    wFunc As Long
    pFrom As String
    pTo As String
    fFlags As Integer
    fAnyOperationsAborted As Boolean
    hNameMappings As Long
    lpszProgressTitle As String
End Type

In addition, I do have similar code that does this very action:

Private Sub Some_Button_Click()

'Delete all files and subfolders
'Be sure that no file is open in the folder
    Dim FSO As Object
    Dim MyPath As String

    Set FSO = CreateObject("scripting.filesystemobject")

'CHANGE this folder path to the desited folder path
    MyPath = "C:\Users\XXX\Desktop\SOME FOLDER\Test_Folder"

    If Right(MyPath, 1) = "\" Then
        MyPath = Left(MyPath, Len(MyPath) - 1)
    End If

    If FSO.FolderExists(MyPath) = False Then
        MsgBox MyPath & " doesn't exist"
        Exit Sub
    End If

    On Error Resume Next
    'Delete files
    FSO.deletefile MyPath & "\*.*", True
    'Delete subfolders
    FSO.deletefolder MyPath & "\*.*", True
    On Error GoTo 0
    
End Sub

However I come up against this sort of thing often. Where I find a Function online that would be very useful, but am unsure the proper way to call it

 

[Cronk]

Firstly, read up about the difference between function and sub procedures.

To call the function in your initial post, the code would be something like

booResult=Recycle(strFileName)

where strFileName is a variable containing the file name and booResult is a boolean value that the function returns to indicate success or failure.

 

[The_Doc_Man]

Cronk beat me to it. So... what he said is how you would call it.

However, simple File System Object calls would do the job just as well and would require fewer external library references.

 

[Cronk]

If it is only to delete a file, my preference is

Kill strFilename

 

[jleach]

Cronk beat me to it. So... what he said is how you would call it.

However, simple File System Object calls would do the job just as well and would require fewer external library references.

<pedant>
I think technically the Windows API calls would be considered as having less external dependencies than FSO, despite the fact that there's more lines of code to write on our behalf.

FSO being an abstraction that calls those same API functions anyway, it would be an extra dependency in terms of code execution.
</pedant>

In any case, to OP: you can also call a function without returning a value, using the same syntax you would for calling a sub:

booResult=Recycle(strFileName)
'or, just...
Recycle strFileName

Cheers

 

[The_Doc_Man]

True that, Jack.

Also FWIW, since the desired code IS a function (and in fact is a Boolean function), it could even work from a RunCode macro. Not that I would suggest it because of the poorer error handling in macros, but it could work.

 

[jeran042]

Firstly, read up about the difference between function and sub procedures.

To call the function in your initial post, the code would be something like

booResult=Recycle(strFileName)

where strFileName is a variable containing the file name and booResult is a boolean value that the function returns to indicate success or failure.

Thank you very much for your help!