Loop thru range and determine if filename exists in selected folder

[burl_h]

I'm looking for some code that will do the following:-

I have a range of cells that list certain bitmap file names. I want to
loop thru the list in the range, open a folder and determine if a
matching filename from the range of cells exists in the folder. If so
place a simple comment in the adjacent cell next to the filename in
the range of cells. The comment should be something like, "Present" if
a match is found or "Missing" is one is not found.

The range of cells could be "A1:A20"

Any help would be greatly appreciated.

Thanks

burl_h

 

[OssieMac]

Need to confirm how you want to select the folder.

Do you want to Browse and select the required folder or do you want to hard
code the folder name and simply search for the file name?

 

[OssieMac]

If you want to browse to select the folder to search then the following code
should do what you want.

I have assumed that the .bmp extension is already on your files in the range
A1:A20. If not then may need to modify the code.

Sub Macro1()

Dim strFolder As String
Dim strFilename As String
Dim rngToTest As Range
Dim rngCell As Range

With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
If .Show = True Then 'User did not click Cancel
strFolder = .SelectedItems(1)
Else
MsgBox "User clicked Cancel"
Exit Sub
End If
End With

'Edit to your sheet name
With Sheets("Sheet1")
Set rngToTest = Range("A1:A20")
End With

For Each rngCell In rngToTest
strFilename = Dir(strFolder & "\" & rngCell.Value)

If strFilename <> "" Then
rngCell.Offset(0, 1) = "Present"
Else
rngCell.Offset(0, 1) = "Missing"
End If
Next rngCell

End Sub

 

[burl_h]

OssieMac,

The solution is brilliant, it worked perfectly, thank you for your
assistance.

I really like the folder option allowing the user to select the
appropriate folder.

Thanks again.

burl_h