Use These 3 SIMPLE Custom Excel Functions to Get File Data From Multiple Folders!

[@merazmamun-analytics]

Note that I cannot paste my 3 functions into the description box as they are too big, so I will just paste them here as a temporary measure. You can copy and paste the 3 functions below into your Excel add-in file and enable the add-in file to be used in any Excel Workbooks. The formatting (indentations) might look a bit messed up when copying and pasting here, so apologies in advanced!: 'This UDF will retrieve a list of file name from one or more folder paths Function get_file_names(ParamArray folder_paths() As Variant) As Variant 'Variables used to create FileSystemObject, everything associated with it, and keep track of files looped Dim fso As Object Dim fol_path As Object Dim file As Object Dim folder_paths_index As Long Dim file_names() As String Dim file_count As Long Set fso = CreateObject("scripting.filesystemobject") 'Turn on error handling 'If an error occurs, go to label "error_handler" On Error GoTo error_handler file_count = 0 'Loop through each folder path provided by the user For folder_paths_index = LBound(folder_paths) To UBound(folder_paths) 'Check to see if the folder path exists If (fso.FolderExists(folder_paths(folder_paths_index))) Then 'Set fol_path to current folder being looped Set fol_path = fso.GetFolder(CStr(folder_paths(folder_paths_index))) 'Loop through each file in the current folder For Each file In fol_path.Files 'Store the file name in the file_names array and adjust the size of the array for each file stored ReDim Preserve file_names(file_count) file_names(file_count) = file.Name file_count = file_count + 1 Next file End If Next 'If there are items in file_names, then transpose the array so output shows vertical array instead of horizontal 'Otherwise, output "No file(s) found." If (file_count > 0) Then get_file_names = Application.Transpose(file_names) Else get_file_names = Array("No file(s) found.") End If 'Reset object variables to nothing Set fso = Nothing Set fol_path = Nothing Set file = Nothing 'Assuming no errors, exit the function Exit Function error_handler: 'Handle errors and provide error descriptions 'Reset object variables to nothing get_file_names = Array("Error: " & Err.Description) Set fso = Nothing Set fol_path = Nothing Set file = Nothing End Function 'The UDF will show the file type in one column and the count for each file type in the next column from one or more folder paths Function count_files_by_type(ParamArray folder_paths() As Variant) As Variant 'Variables used to create FileSystemObject, everything associated with it, and keep track of files looped Dim fso As Object Dim fol_path As Object Dim file As Object Dim file_extension As String Dim file_count_dict As Object Dim key As Variant Dim result() As Variant Dim folder_paths_index As Long Dim i As Long 'Turn on error handling 'If an error occurs, go to label "error_handler" On Error GoTo error_handler Set fso = CreateObject("Scripting.FileSystemObject") Set file_count_dict = CreateObject("Scripting.Dictionary") 'Loop through each folder path provided by the user For folder_paths_index = LBound(folder_paths) To UBound(folder_paths) 'Check to see if the folder path exists. If (fso.FolderExists(folder_paths(folder_paths_index))) Then 'Set fol_path to current folder being looped Set fol_path = fso.GetFolder(CStr(folder_paths(folder_paths_index))) 'Loop through each file in the current folder For Each file In fol_path.Files 'Get the file extension name and store the name in a dictionary if it does not exist already 'Store the number of times each file extension name appears file_extension = fso.GetExtensionName(file.Path) If (file_count_dict.exists(file_extension)) Then file_count_dict(file_extension) = file_count_dict(file_extension) + 1 Else file_count_dict.Add file_extension, 1 End If Next file End If Next 'Populate the 2D result array with the dictionary values that will be used for count_files_by_type ReDim result(1 To file_count_dict.Count, 1 To 2) i = 1 For Each key In file_count_dict.Keys result(i, 1) = key result(i, 2) = file_count_dict(key) i = i + 1 Next key 'Return the result array count_files_by_type = result 'Reset object variables to nothing Set fso = Nothing Set fol_path = Nothing Set file = Nothing Set file_count_dict = Nothing 'Assuming no errors, exit the function Exit Function error_handler: 'Handle errors and provide error descriptions 'Reset object variables to nothing count_files_by_type = Array("Error: " & Err.Description) Set fso = Nothing Set fol_path = Nothing Set file = Nothing Set file_count_dict = Nothing End Function 'This UDF will check to see if the specific file name exists in one or more folder paths Function file_exists_in_folders(file_name As String, ParamArray folder_paths() As Variant) As Boolean 'Variables used to create FileSystemObject, everything associated with it, and keep track of files looped Dim fso As Object Dim fol_path As Object Dim file As Object Dim folder_paths_index As Long Set fso = CreateObject("scripting.filesystemobject") 'Loop through each folder path provided by the user For folder_paths_index = LBound(folder_paths) To UBound(folder_paths) 'Check to see if the folder path exists If (fso.FolderExists(folder_paths(folder_paths_index))) Then 'Set fol_path to current folder being looped Set fol_path = fso.GetFolder(CStr(folder_paths(folder_paths_index))) 'Loop through each file in the current folder For Each file In fol_path.Files 'If file exists in any of the folders, return function output as TRUE, reset object variables, 'and exit the function If (fso.fileexists(fol_path & "\" & file_name)) Then file_exists_in_folders = True Set fso = Nothing Set fol_path = Nothing Set file = Nothing Exit Function End If Next file End If Next 'If there were no files found in each folder path, then output FALSE file_exists_in_folders = False 'Reset object variables to nothing Set fso = Nothing Set fol_path = Nothing Set file = Nothing End Function