Vnalla0419
03-20-2015, 02:03 PM
Hi Everyone,
Hoping some of you might be able to assist me with my problem. I have created a VBA function that should take a folder path as an argument, and traverse through the directory until finding a sub-folder that has no sub-folders, and has files. Basically finding the lowest level folder in that particular directory, which is where the desired files are located. I want the function to return the path of that lowest level folder. My intention is to pass the path of this folder to the calling function, so that some manipulation can be done to those files.
With that in mind, I have written the below function:
*************************************************************************** *********
Public Function getFiles(ByVal inputFolder As String) As String
Dim fileSys As New FileSystemObject
Dim currentFolder As Object
Dim subFolder As Object
Dim returnVal As String
Set currentFolder = fileSys.GetFolder(inputFolder)
If currentFolder.SubFolders.Count = 0 And currentFolder.Files.Count <> 0 Then
getFiles = CStr(currentFolder.Path)
If getFiles <> vbNullString Then
MsgBox getFiles
MsgBox "returning now"
Exit Function
End If
ElseIf currentFolder.SubFolders.Count > 0 Then
For Each subFolder In currentFolder.SubFolders
MsgBox subFolder.Path & " recursively calling function on this"
Call getFiles(subFolder.Path)
Next subFolder
End If
End Function
*************************************************************************** *************************************
So in essence, if the current folder contains files, but no subfolders, the function will return the path of this folder. If the current folder contains subfolders, the function will recursively call itself on each of the sub-folders, passing the subfolder path as the parameter to the function.
The issue I am having is that I am assigning the return value of this function to a string variable in my calling function. However, it seems like the value is not being passed, as the string that I am assigning this return value to is showing as a null string (""), and not having any value. I included the MsgBox getFiles line in the function to test and ensure that the function is finding the desired folder, and it is, as that MsgBox statement does print the folder path that I want to return. However, this value is not being returned to the calling function.
Sorry for the long post, but am I doing anything wrong here? Given that it seems like the function is finding the right folder, and the "MsgBox getFiles " line is even displaying the string that I want to return, is there any reason why this value is not being returned to the calling function? I have worked with VBA and created VBA functions before, but never had this issue before. Not sure if I am forgetting to do something.
Thanks in advance for any assistance you can provide!
Hoping some of you might be able to assist me with my problem. I have created a VBA function that should take a folder path as an argument, and traverse through the directory until finding a sub-folder that has no sub-folders, and has files. Basically finding the lowest level folder in that particular directory, which is where the desired files are located. I want the function to return the path of that lowest level folder. My intention is to pass the path of this folder to the calling function, so that some manipulation can be done to those files.
With that in mind, I have written the below function:
*************************************************************************** *********
Public Function getFiles(ByVal inputFolder As String) As String
Dim fileSys As New FileSystemObject
Dim currentFolder As Object
Dim subFolder As Object
Dim returnVal As String
Set currentFolder = fileSys.GetFolder(inputFolder)
If currentFolder.SubFolders.Count = 0 And currentFolder.Files.Count <> 0 Then
getFiles = CStr(currentFolder.Path)
If getFiles <> vbNullString Then
MsgBox getFiles
MsgBox "returning now"
Exit Function
End If
ElseIf currentFolder.SubFolders.Count > 0 Then
For Each subFolder In currentFolder.SubFolders
MsgBox subFolder.Path & " recursively calling function on this"
Call getFiles(subFolder.Path)
Next subFolder
End If
End Function
*************************************************************************** *************************************
So in essence, if the current folder contains files, but no subfolders, the function will return the path of this folder. If the current folder contains subfolders, the function will recursively call itself on each of the sub-folders, passing the subfolder path as the parameter to the function.
The issue I am having is that I am assigning the return value of this function to a string variable in my calling function. However, it seems like the value is not being passed, as the string that I am assigning this return value to is showing as a null string (""), and not having any value. I included the MsgBox getFiles line in the function to test and ensure that the function is finding the desired folder, and it is, as that MsgBox statement does print the folder path that I want to return. However, this value is not being returned to the calling function.
Sorry for the long post, but am I doing anything wrong here? Given that it seems like the function is finding the right folder, and the "MsgBox getFiles " line is even displaying the string that I want to return, is there any reason why this value is not being returned to the calling function? I have worked with VBA and created VBA functions before, but never had this issue before. Not sure if I am forgetting to do something.
Thanks in advance for any assistance you can provide!