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

 

[Incidental]

Hi There

One method you could use is the File System Object which is handy in
this sort of situation. It will check each file in the given folder
to see if it is an Excel file if so you can use the InStr function to
look for a string within a string to check the name of the file and if
you get a match you can then open the file or do what ever else you
need.

Option Explicit

Sub OpenFilesUsingFSO()

Dim intRow As Integer
Dim intLastRow As Integer
Dim objFSO As Object
Dim objFile As Object
Dim objFolder As Object
Dim strSearchFor As String

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.getfolder("C:\Test")

With workbooks("Data.xls")
intLastRow = .Sheets("Sheet1").Cells _
(Rows.Count, "A").End(xlUp).Row

For intRow = 1 To intLastRow

strSearchFor = .Sheets(1).Cells(intRow, 1).Value

For Each objFile In objFolder.Files
If Right(objFile.Name, 4) = ".xls" Then
If InStr(1, objFile.Name, strSearchFor, vbTextCompare) <>
0 Then
Workbooks.Open (objFile)
End If
End If
Next objFile
Next intRow
End With

Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing

End Sub

I hope this helps you out

Steve

 

[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.

 

[Incidental]

Hi Again

First off sorry my first code didn't work for you, not sure why as i
tested it and it runs fine for me with the info from your post? Anywho
i can't think of a way around it using your proposed method however i
have come up with a sort of combo of the two, this time without the
FSO but still using the InStr function as i think this is the easiest
way to check for a name in a string...

I have added a few escape routes to try and get out of the loops to
prevent unnecessary cycles, let me know how you get on with the code
and of course if you find a way around the problem

Dim fldrName As String
Dim fName
Dim lastcl As Integer
Dim i As Integer
Dim intFound As Integer

Sub test()
fldrName = "C:\Record"
fName = Dir(fldrName & "\*.xls")
With Workbooks("Data.xls")
lastcl = .Sheets("Sheet1").Cells _
(Rows.Count, "A").End(xlUp).Row
Do While fName <> ""
For i = 1 To lastcl
If InStr(1, fName, .ActiveSheet.Cells(i, 1).Value, _
vbTextCompare) <> 0 Then
Workbooks.Open (fldrName & "\" & fName)
intFound = intFound + 1
i = lastcl
End If
Next i
fName = Dir()
If intFound = lastcl Then Exit Do
Loop
End With
End Sub


Steve