File Array Search?

[onethumb]

Hi

I was wondering if there is a way to search a whoe list of files in folders and subfolders and list the information?

Here is the problem.

1. In Column A, i have a list of filenames
2. In Column B, i have a list of file extension of the file in Column A
3. In cell D1 i have the root folder

I would like to search through all folders and subfolders in the root folder looking for each file and extension and if found list the full path in column C in the same row against the file name.

Can anyone please guide me.

Thanks in advance.

 

[Andrew Fergus]

Hi

I have had a crack at this but I'm not sure if it is the most efficient way to tackle this task. To do this you will need to use VBA code. Open the spreadsheet and then open the VBA editor by pressing the Alt and F11 keys together, navigate your way to the 'ThisWorkbook' project (on the left hand side of your screen - double click 'ThisWorkbook'), and copy/paste this code into the window pane on the right hand side of your screen:

Sub FindMyFiles()

Dim MyRange As Variant
Dim MyLastRow As Long
Dim LoopCount As Long
Dim SearchDir As String
Dim tmpStr As String
'Change MySheet to the name of the sheet you are using
Const MySheet As String = "Sheet4"

With Sheets(MySheet)
    MyLastRow = .Range("A1").End(xlDown).Row
    MyRange = .Range("A1").Resize(MyLastRow, 2)
    SearchDir = .Range("D1").Value
End With

For LoopCount = 1 To MyLastRow
    With Application.FileSearch
        .NewSearch
        .LookIn = SearchDir
        .SearchSubFolders = True
        .Filename = MyRange(LoopCount, 1) & "." & MyRange(LoopCount, 2)
        If .Execute(msoSortByFileName, msoSortOrderAscending) > 0 Then
            tmpStr = .FoundFiles(1)
            tmpStr = Left$(tmpStr, Len(tmpStr) - Len(MyRange(LoopCount, 1)) - Len(MyRange(LoopCount, 2)) - 1)
        Else
            tmpStr = "n/a"
        End If
    End With
    Worksheets(MySheet).Cells(LoopCount, 3).Value = tmpStr
Next LoopCount

MsgBox "Finished!"

End Sub

Make sure you change the name of the worksheet where I have indicated within the code. Save and close the VBA editor. Back in your spreadsheet select menu option Tools > Macro > Macros > ThisWorkbook.FindMyFiles > Run.

I have assumed the file names start at cell A1 - if this contians headings then that is fine provided the filenames then start at A2. Please note I haven't included any error checking in this code - it is pretty raw but hopefully it is enough to get you started.

HTH, Andrew

 

[onethumb]

Hi Andrew

Thanks a ton for this. I works very well and gave a better start than i was hoping for. I will slowly add a few bits more to make it more dynamic.