Match Cell Value with File Name in Folder Directory and get it’s Path
I have a table with two columns (A:B), column A cells contain the names of PDF files that are in a folder directory “C:UsersTaylorDesktopFolder” and in the image I have in column B the file path of the values (1000, 1001, 1002, 1003) How can I loop through a folder and match the names of pdf files with cells in column A values and extract their path URL, and you see in the picture that number 1004 and 1005 in col B are black and don’t have hyperlink because they don’t exist in the folder.
In this folder path I have pdf files 1000.pdf, 1001.pdf, 1002.pdf, 1003.pdf
IMAGE: https://ibb.co/5rN4xdg
The code works well, I’m getting error in this line:
Sheet1.Hyperlinks.Add Anchor:=cel.Offset(, 1).Value, Address:=fso.GetFile(strFilepath), TextToDisplay:=cel.Value
MISMATCH ERROR
Code:
‘ GO TO TOOLS THEN REFERENCES THEN ADD MICROSOFT SCRIPTING RUNTIME
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(“Data”)
Set tbl = ws.ListObjects(1)
‘Path of the Source folder with files
rootFolder = “C:UsersTaylorDesktopNew folder”
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(1).Interior.ColorIndex = xlNone
For Each cel In tbl.DataBodyRange.Columns(1).Cells
strFilepath = rootFolder & cel.Value & “.pdf”
newFilePath = newFolder.Path & “” & cel.Value
If fso.FileExists(strFilepath) Then
cel.Interior.Color = vbYellow
Sheet1.Hyperlinks.Add Anchor:=cel.Offset(, 1).Value, Address:=fso.GetFile(strFilepath), TextToDisplay:=cel.Value
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 have a table with two columns (A:B), column A cells contain the names of PDF files that are in a folder directory “C:UsersTaylorDesktopFolder” and in the image I have in column B the file path of the values (1000, 1001, 1002, 1003) How can I loop through a folder and match the names of pdf files with cells in column A values and extract their path URL, and you see in the picture that number 1004 and 1005 in col B are black and don’t have hyperlink because they don’t exist in the folder.In this folder path I have pdf files 1000.pdf, 1001.pdf, 1002.pdf, 1003.pdfIMAGE: https://ibb.co/5rN4xdgThe code works well, I’m getting error in this line: Sheet1.Hyperlinks.Add Anchor:=cel.Offset(, 1).Value, Address:=fso.GetFile(strFilepath), TextToDisplay:=cel.Value MISMATCH ERROR Code:’ GO TO TOOLS THEN REFERENCES THEN ADD MICROSOFT SCRIPTING RUNTIME
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(“Data”)
Set tbl = ws.ListObjects(1)
‘Path of the Source folder with files
rootFolder = “C:UsersTaylorDesktopNew folder”
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(1).Interior.ColorIndex = xlNone
For Each cel In tbl.DataBodyRange.Columns(1).Cells
strFilepath = rootFolder & cel.Value & “.pdf”
newFilePath = newFolder.Path & “” & cel.Value
If fso.FileExists(strFilepath) Then
cel.Interior.Color = vbYellow
Sheet1.Hyperlinks.Add Anchor:=cel.Offset(, 1).Value, Address:=fso.GetFile(strFilepath), TextToDisplay:=cel.Value
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 Read More
