Open files by looking at list names in column A

[K]

Hi all, I got names in column A of Workbooks("Summary.xls").Sheets
("Sheet1") like see below

A………….col
John
Vikki
Ali
Sophy
Dean

In folder "C:\Documents\Records" I have two files like see below

Record for John
Record for Sophy

I want macro which should match names of column A with the file names
and if exist then it should open that file. I wrote below macro which
only opening file with name "Record of John" as it should also open
"Record for Sophy" because the name Sophy also exist in column A
list. Please can any friend help that whats wrong with macro below

Sub Test()
fldrName = "C:\Documents\Records"
With Workbooks("Summary.xls").Sheets("Sheet1")
lastcl = .Cells(Rows.Count, "A").End(xlUp).Row
For RowCount = 2 To lastcl
If .Range("A" & RowCount) <> "" Then
pnm = .Range("A" & RowCount).Value
fName = Dir(fldrName & "\" & "*" & pnm & "*.xls")
If fName = "" Then GoTo lastmsg
Set bk = Workbooks.Open(Filename:=fldrName & "\" & fName)
End If
fName = Dir()
Next
End With
lastmsg:
MsgBox "Its Done!", vbInformation, "Done"
End Sub

 

[smartin]

Hi all, I got names in column A of Workbooks("Summary.xls").Sheets
("Sheet1") like see below

A………….col
John
Vikki
Ali
Sophy
Dean

In folder "C:\Documents\Records" I have two files like see below

Record for John
Record for Sophy

I want macro which should match names of column A with the file names
and if exist then it should open that file. I wrote below macro which
only opening file with name "Record of John" as it should also open
"Record for Sophy" because the name Sophy also exist in column A
list. Please can any friend help that whats wrong with macro below

Sub Test()
fldrName = "C:\Documents\Records"
With Workbooks("Summary.xls").Sheets("Sheet1")
lastcl = .Cells(Rows.Count, "A").End(xlUp).Row
For RowCount = 2 To lastcl
If .Range("A" & RowCount) <> "" Then
pnm = .Range("A" & RowCount).Value
fName = Dir(fldrName & "\" & "*" & pnm & "*.xls")
If fName = "" Then GoTo lastmsg
Set bk = Workbooks.Open(Filename:=fldrName & "\" & fName)
End If
fName = Dir()
Next
End With
lastmsg:
MsgBox "Its Done!", vbInformation, "Done"
End Sub

Your inner-most loop is breaking at
If fName = "" Then GoTo lastmsg
because there is no "Vikki" file.

Try this:

' code begin ---------------------------------
Sub Test()
fldrname = "C:\Documents\Records"
With Workbooks("Summary.xls").Sheets("Sheet1")
lastcl = .Cells(Rows.Count, "A").End(xlUp).Row
For RowCount = 2 To lastcl
If .Range("A" & RowCount) <> "" Then
pnm = .Range("A" & RowCount).Value
fName = Dir(fldrname & "\" & "*" & pnm & "*.xls")
If fName <> "" Then
Set bk = Workbooks.Open(Filename:=fldrname & "\" & fName)
End If
End If
Next
End With
lastmsg:
MsgBox "Its Done!", vbInformation, "Done"
End Sub
' code end ------------------------------------

A few suggestions:
* use Option Explicit and declare all variables
* indent your code for better readability
* avoid using "GoTo" -- there is almost always a better way to handle
conditional execution

Hope this helps.

 

[Don Guillett]

Try this simple approach. Skips blanks

Sub OpenFilesInList()
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
On Error Resume Next
For Each wb In Range("a1:a" & lastrow)
Workbooks.Open Filename:="C:\documents\records\" & wb & ".xls"
Next wb
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Hi all, I got names in column A of Workbooks("Summary.xls").Sheets
("Sheet1") like see below

A………….col
John
Vikki
Ali
Sophy
Dean

In folder "C:\Documents\Records" I have two files like see below

Record for John
Record for Sophy

I want macro which should match names of column A with the file names
and if exist then it should open that file. I wrote below macro which
only opening file with name "Record of John" as it should also open
"Record for Sophy" because the name Sophy also exist in column A
list. Please can any friend help that whats wrong with macro below

Sub Test()
fldrName = "C:\Documents\Records"
With Workbooks("Summary.xls").Sheets("Sheet1")
lastcl = .Cells(Rows.Count, "A").End(xlUp).Row
For RowCount = 2 To lastcl
If .Range("A" & RowCount) <> "" Then
pnm = .Range("A" & RowCount).Value
fName = Dir(fldrName & "\" & "*" & pnm & "*.xls")
If fName = "" Then GoTo lastmsg
Set bk = Workbooks.Open(Filename:=fldrName & "\" & fName)
End If
fName = Dir()
Next
End With
lastmsg:
MsgBox "Its Done!", vbInformation, "Done"
End Sub