Matching file names in Excel column to file names in folder

Silver Contributor

Oct 02, 2020

Let me know if I understood your requirement.

All you want to do is, check whether the files listed in column D are found in the Document folder.

e.g. Check if the file tps report.xls listed in cell D2 is found in the Document folder and if it exists in that folder, highlight the cell D2 which would be like a visual indicator that this file was found in the folder and do the same for all the files in column D.

Is that what you are trying to achieve?

Ozz_Kozz

Copper Contributor

Oct 02, 2020

Subodh_Tiwari_sktneer Hi, yes but if exist in the Document Folder it should be highlighted in the Document Folder. I'm trying to avoid typing in the file name into the search box of the Document folder and save some time. I would want to find the file and extract it from the large folder. Hope that explains a bit more. Thanks.

Subodh_Tiwari_sktneer

Silver Contributor

Oct 03, 2020

Ozz_Kozz

Okay, please try the following code and see if this is something you can work with.

The following code will check the files listed in column 4 of the Table on Sheet called "B" in the Document folder and if it finds the file in that folder, it will highlight the cell with filename in the Table and copy the file from Document folder to a Sub-Folder called "Found Files".

Sub SearchFiles()
Dim ws                      As Worksheet
Dim tbl                     As ListObject
Dim cel                     As Range
Dim rootFolder              As String
Dim strNameNewSubFolder     As String
Dim fso                     As FileSystemObject
Dim newFolder               As Folder
Dim fil                     As File
Dim strFilepath             As String
Dim newFilePath             As String

Set fso = New FileSystemObject
Set ws = Worksheets("B")
Set tbl = ws.ListObjects(1)

'Path of the Source folder with files
rootFolder = "C:\Users\sktneer\Documents"

If Not fso.FolderExists(rootFolder) Then
    MsgBox rootFolder & " doesn't exist.", vbExclamation, "Source Folder Not Found!"
    Exit Sub
End If

'files that are found in the Source Folder would be copied to this New Sub-Folder
'Change the name of the Sub-Folder as per your requirement
strNameNewSubFolder = "Found Files"

If Right(rootFolder, 1) <> "/" Then rootFolder = rootFolder & "/"

If Not fso.FolderExists(rootFolder & strNameNewSubFolder) Then
    fso.CreateFolder rootFolder & strNameNewSubFolder
End If

Set newFolder = fso.GetFolder(rootFolder & strNameNewSubFolder)

tbl.DataBodyRange.Columns(4).Interior.ColorIndex = xlNone

For Each cel In tbl.DataBodyRange.Columns(4).Cells
    strFilepath = rootFolder & cel.Value
    newFilePath = newFolder.Path & "/" & cel.Value
    If fso.FileExists(strFilepath) Then
        cel.Interior.Color = vbYellow
        Set fil = fso.GetFile(strFilepath)
        'The following line will copy the file found to the newly created Sub-Folder
        fil.Copy newFilePath
    End If
Next cel
Set fso = Nothing
End Sub

Forum Discussion

Matching file names in Excel column to file names in folder

Resources