CODEWITHSUNDEEP
excelfunctionrecursionoutlookvba
learningthisstuff
learningthisstuff

Reputation: 101

Send Function Result for Use in Sub

I have a sub that calls a function that searches through subfolders and then returns a file path with file name so that the main sub can then use that string to create an attachment. It is a recursive function and as such it keeps resetting my value in strJDFile. I need it to search through all the subfolders as it does, find my file, and then send the strJDFile value through to the main sub. Since it keeps resetting, nothing makes it through to the sub. What am I doing wrong? The function works otherwise. It is just the last step of getting the result to carry through.

Function recurse(sPath As String)
Dim FSO As New FileSystemObject
Dim myFolder As Scripting.Folder
Dim mySubFolder As Scripting.Folder
Dim myFile As Scripting.File
Dim strName As String
Dim strJDFile As String
Dim strDir As String
Dim strJDName As String


Set myFolder = FSO.GetFolder(sPath)
strName = Range("a2").Offset(0, 3)

For Each mySubFolder In myFolder.SubFolders
    For Each myFile In mySubFolder.Files
        If myFile.name Like "*" & strName & "*" Then
            strJDName = myFile.name 
            strDir = mySubFolder & "\"
            strJDFile = strDir & strJDName
            Exit Function
        End If
    Next
    recurse = recurse(mySubFolder.Path)
Next

End Function

I looked at multiple posts on this issue including this one VBA macro that search for file in multiple subfolders and I upvoted the answer there, but that is how to set up a recursive, not how to make the value come through to the sub. The issue is as I said above, each time it hits the 'Next' it resets, so my strJDFile value gets set to "" again. But you need the Next after the recurse-strDir in order to get it to keep going through to the next subfolder until if finds the right value. I just need the value to remain instead of coming through as blank. I stepped through with F8 and that is how I found that it resets when it hits the final Next. That is why I added the Exit Function, but that did not work either.

Upvotes: 0

Views: 111

Answers (1)

niton
niton

Reputation: 9189

"recurse" is returned, not strJDFile.

Private Sub functionTest()

Dim x As String
Dim fPath As String

fPath = "C:\Test"

x = recurse(fPath)

If x = "" Then x = "No results."

Debug.Print " *** recurse has returned: " & x
Debug.Print "Done"

End Sub


Function recurse(sPath As String)

Dim FSO As New FileSystemObject
Dim myFolder As Scripting.folder
Dim mySubFolder As Scripting.folder
Dim myFile As Scripting.file

Dim strName As String
Dim strJDFile As String
Dim strDir As String
Dim strJDName As String

Set myFolder = FSO.GetFolder(sPath)

' strName = Range("a2").Offset(0, 3)
strName = "test.xlsx"

For Each mySubFolder In myFolder.SubFolders

    Debug.Print " mySubFolder: " & mySubFolder

    For Each myFile In mySubFolder.Files

        If myFile.name Like "*" & strName & "*" Then
            strJDName = myFile.name
            strDir = mySubFolder & "\"
            strJDFile = strDir & strJDName

            recurse = strJDFile

            Exit Function

        Else
            Debug.Print "  myFile.name: " & myFile.name

        End If

    Next

    recurse = recurse(mySubFolder.path)

Next

End Function

Upvotes: 2

Related Questions