Forum Discussion
Matching file names in Excel column to file names in folder
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.
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- I'm very new to this, so thank you for what you've provided so far! I'm wondering if someone could help me tweak some things, as I can't seem to get the code to work...
I've gotten it to create the subfolder in the source folder, but I would like to have the program search for partial file names instead of whole names (ex: search for 20448 results in the file labeled: 20448 xxxx-xxxxxxxxxx, xxx-xxx, xxxxx-xx)
I'm wondering what I would need to add to this code to have it return the correct files with only the digits at the beginning of the file name.
Thanks in advance! - Hello! - I would be happy to check with you the possibility of adjusting the code. Because in the current configuration, it does not really work for me.
I need the code to scan all the cells in column A, and if there is a match between the information in the cell and the same file name in the original folder where the excel file is saved, the code will create one new folder, somewhere changeable, and copy all files with a name similar to the data in each cell In column A for the location in the new folder. Subodh_Tiwari_sktneer Thank you for your reply. Where would I enter this code? And can I undo this after I'm done with the task?
You should place this code on a Standard Module like Module1 and to do so follow these steps...
- Open your file and then press Alt+F11 to open VB Editor.
- On VB Editor Ribbon -->go to Insert Tab --> and choose Module. This will insert a new Module called Module1 and open a code window.
- Paste the code into the opened code window, close the VB Editor.
- Save your file as Macro-Enabled Workbook.
- On the sheet with data, insert a Shape or a Button from Form Controls, right click to it and choose Assign Macro and then choose the macro SearchFiles from the available macro list and click OK to finish.
- Now you may click this shape or button to run the code.
Please find the attached in which I have placed the code on Module1 and inserted a button called "Search Files" on the Sheet. You may click this button to run the code.
No, you cannot undo the steps performed by the macro. Btw what is there which you want to undo after the code execution?
In the attached, I have also tweaked the path of the source folder. Now the code assumes that you have a folder called "Document folder" with all the files on your Desktop.
The tweaked line is as below...
rootFolder = Environ("UserProfile") & "\Desktop\Document Folder"
- Hello, everyone. I've been trying to use this macros with no luck. I've already modify path to a folder located in my desktop, names my worksheet after the name, changed colour number but Im only able to creat a folder inside the destination folder. What could possibly is going wrong?
Just forgot to tell you that you will need to add a reference to a library called "Microsoft Scripting Runtime".
To do so, go to Tools on VB Editor's Ribbon --> References --> search for the library called "Microsoft Scripting Runtime" and check the CheckBox next to it and click OK to finish.
