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