List matching files in a folder, part two

In my previous post titled List matching files in a directory, I posted a method that creates a string array of filenames in a folder that match a given file extension. Here we are going to put that code to some real use.

Don't forget to visit that post and grab the GetFilesList and GetFileType procedures.

Here's the problem: I have a friend who has a column of values on a worksheet. He has a bunch of other workbooks he needs to search for the values in the column, so he has put those workbooks into a separate folder. The challenge? Search each of the workbooks for each of the values in the column, then copy and paste part of the found cell's row next to the value in the original workbook.

Normally I would just use VLOOKUP, INDEX/MATCH or OFFSET to retrieve the value, but the other workbooks have the data all out of order. In other words, the value we're looking for isn't guaranteed to be in a certain column. It could be anywhere on the first worksheet.

Warning: The procedures below are slightly hard coded — this is a real application after all.

First we'll use the GetFilesList procedure to loop through a specified folder on my friend's desktop and create a string array of workbook names.

Sub FindWorkbookData()

Dim folder As String
Dim extension As String
Dim dummyString As String
Dim fileslist() As String
Dim i As Long

folder = Environ("userprofile") & "\Desktop\Forms\"
extension = "xls"

' build list of Excel files from folder
fileslist = GetFilesList(folder, extension)

' check if array is empty (i.e. no files matching extension exist in given folder)
On Error Resume Next
dummyString = fileslist(0)

If Err <> 0 Then
  MsgBox "No files found with " & extension & " extension in" & vbCrLf & folder, vbExclamation
  Exit Sub
End If
On Error GoTo 0

For i = 0 To UBound(fileslist)
  Call ProcessFile(fileslist(i))
Next i

End Sub

Once we have the array of names, we call a custom function called ProcessFile which takes a string argument. In this case, we want to process each of the spreadsheets in the folder.

ProcessFile will first set a reference to the currently open workbook (the one with the values we want to search the other workbooks for). The values are present in column A, starting in cell A2. Then the procedure will open the specified workbook (the first one in the array) and use the Range.Find Method against each value in the column, to see if the workbook contains any of the values we need to find. If the given value in column A is found in the newly opened workbook, we copy the values in columns A through L in the same row as the found value into the original workbook, right next to the search value. Sort of like a VLOOKUP on steroids.

At the end, we print the filename where the value was found, in case there are a lot of workbooks and we need to know later where specifically the value was located. We don't want to have to then go manually through each workbook!

Sub ProcessFile(fileName As String)

Dim folder As String
Dim rng As Excel.Range
Dim cell As Excel.Range
Dim wkbk As Excel.Workbook
Dim wksht As Excel.Worksheet
Dim currentWkbk As Excel.Workbook
Dim currentWksht As Excel.Worksheet
Dim currentRange As Excel.Range

Set wkbk = ActiveWorkbook
Set wksht = wkbk.Sheets("My first sheet name")
Set rng = wksht.Range(wksht.Range("A2"), wksht.Range("A" & Rows.Count).End(xlUp))

folder  = Environ("userprofile") & "\Desktop\Forms\"

' open workbook
Set currentWkbk = Workbooks.Open(folder & fileName)
Set currentWksht = currentWkbk.Sheets(1)
Set currentRange = currentWksht.UsedRange

Dim foundRange As Excel.Range
For Each cell In rng

If cell.Offset(0, 1).Value = "" Then ' only check if previous info wasn't found

On Error Resume Next
  Set foundRange = currentRange.Find(cell.Value)
  If Not foundRange Is Nothing Then  ' range found in this file
    Set foundRange = currentWksht.Range(currentWksht.Range("A" & foundRange.Row), currentWksht.Range("L" & foundRange.Row))

    foundRange.Copy cell.Offset(0, 1)
    cell.Offset(0, 13).Value = fileName
    cell.EntireRow.AutoFit
  End If

On Error GoTo 0

End If

Next cell

currentWkbk.Close False

End Sub

Right after the For loop starts, we check to see if anything already exists in column B (the place where we are writing information when the value is found). If so, that means the value was already found and we can skip to the next one. This assumes that there is nothing already in column B and that each value in column A exists in only one (at most) of the other workbooks. Both of which are true in this case.