Open files from folder by matching name

[K]

Hi all, I have name in column A like see below

A………..col
Denise Buky
John Well
Sophy Bell

and I have files in folder "C:\Record" like see below

Record Sheet - Denise Buky.xls
Record - Roy William.xls
Record (John Well).xls
Record Sheet Craig Brown.xls
Record - Sophy Bell.xls
Record Sheet - Dean Owen.xls

I need macro which should check names in column A and only open those
files from above folder in which that name match or appear. I tried
doing this with below macro but this opens up all the files in
folder. Please can any friend can help that how can i do it.

Sub test()
fldrName = "C:\Record"
fName = Dir(fldrName & "\*.xls")
lastcl = Workbooks("Data.xls").Sheets("Sheet1").Cells(Rows.Count,
"A").End(xlUp).Row
Do While fName <> ""
Set c = Workbooks("Data.xls").Sheets("Sheet1").Range("A2:A" &
lastcl).Find(What:=fName, _
LookIn:=xlValues, LookAt:=xlWhole)
If c Is Nothing Then
Set bk = Workbooks.Open(Filename:=fldrName & "\" & fName)
bk.Close False
End If
fName = Dir()
Loop
End Sub

 

[K]

Hi Steve, Thanks for replying. sorry mate i tried your macro but its
not working. your macro is also opening all the files from folder
instead of the given name ones. My macro does work but i need some
thing on line
Set c = Workbooks("Data.xls").Sheets("Sheet1").Range("A2:A" & _
lastcl).Find(What:=fName, _
LookIn:=xlValues, LookAt:=xlWhole)
If i put some kind of critaria in above line where it say
"What:=fName" then i got everything working perfect. In first
question i just put top half of my macro and therefore i am looking
for small macro sultion. it can be solved if lets say i got files
names listed in column and i put some formula in next column which
should only exract the people name form the file and then i need same
formula thing in my macro to do the job. If you have any suggestions
please do share.