VBA Help

[mgm05267]

Hi,
Can someone please tell me how to use a file dialog object to search for the files(whose names are present in a column say A)using VBA.
The code actually has to search for set of files in a specific directory whose filenames are present in the column A.​

​

 

[vaskov17]

What should happen when you find or don't find the files. Also I don't think the file dialog can be used to search.

 

[JLGWhiz]

This is a basic macro to return the names of files in a directory with a For ...Each...Next loop to check against a list of files in column A.
The message box is there so you can see that the files are being returned, but it is unnecessary if you want to delete that line. That is where you would insert code to do something with the returned file name.

Code:
Sub fileSrch()
Dim sh As Worksheet, rng As Range, lr As Long, fSrch As String
Set sh = Sheets(1)
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A2:A" & lr)
myPath = ThisWorkbook.Path
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If
fSrch = Dir(myPath & "*.xls*")
Do
For Each c In rng
If LCase(c.Value) = LCase(fSrch) Then
MsgBox fSrch
End If
Next
fSrch = Dir
Loop While fSrch <> ""
End Sub
Code: