Search for files, then return file path

**Carlsbergen** · 05-06-2010, 03:53 AM

Hey guys.

I was wondering if you could help me out here.
I have a list of names and I want to search specific folders for any filenames that contains that name and then return the filepath to those files into Excel.

The list of names I have look like this:

07_0088
08_0086
20-07-00889-01
etc..

Then I have a cell which contains the filepath.
Like:
C:\My Documents\

So what I want the macro to do is to search for all files in a given path where the filename
contains *07_0088*. Then return the full filepath back into an Excel sheet.
Something like:
C:\My Documents\07_0088_CR.xls
etc.

I have made something like this before, but that macro only returns one file (the first one it finds). I want something that can look for multiple files and return all results.

Any help will be MUCH appreciated

**antoka05** · 05-06-2010, 04:46 AM

See attached file, I hope it can help you.

This is the macro, In range B2 put the path where to look for files and in range A5:An put files to search:

Sub Macro1()
   Dim fso As Object, myPath As String
   Dim lastRow As Long, r As Long
   Dim myFiles As Range
   Dim myFolder, myFile
   
   Set fso = CreateObject("Scripting.FileSystemObject")
   
   myPath = Range("b2")
   lastRow = Cells(Rows.Count, "a").End(xlUp).Row
   
   Set myFolder = fso.GetFolder(myPath).Files
   For Each myFile In myFolder
      For r = 5 To lastRow
         If myFile Like "*" & Cells(r, "a") & "*" Then
            Cells(r, "b") = myFile
         End If
      Next
   Next

End Sub

Regards,
Antonio

**Carlsbergen** · 05-06-2010, 09:38 AM

Hi Antoka05

The searching works, but it will only return the path of one file even though there are multiple files that match the search criteria.
What I need is something that will return all the matches.

**Leith Ross** · 05-06-2010, 09:54 AM

Hello Carlsbergen,

I have made a correction to Antonio's macro. This work now.

Sub Macro1()
   Dim fso As Object, myPath As String
   Dim lastRow As Long, r As Long
   Dim myFiles As Range
   Dim myFolder, myFile
   Dim N As Long
   
   Set fso = CreateObject("Scripting.FileSystemObject")
   
   myPath = Range("b2")
   lastRow = Cells(Rows.Count, "a").End(xlUp).Row
   
   N = 5
   Set myFolder = fso.GetFolder(myPath).Files
   For Each myFile In myFolder
      For r = 5 To lastRow
         If myFile Like "*" & Cells(r, "a") & "*" Then
            Cells(N, "b") = myFile
            N = N + 1
         End If
      Next
   Next

End Sub