Hi,
I have an Excel spreadsheet with a filename in column A. The filenames listed in column A appear in one or more Ms office .doc files in one or more source directories.
I need Excel to search the .doc files recursively and return the path(s) of the file(s) that contain the filename specified in column A into column B. If more than one file go to column C etc.
I have the macro which does the search file in folder and sub folder and list the path, I want path of those filename which appear in column A
__|______A_____|______B_____|
1 | test_1.doc |c:costtest_1.doc|
2 | test_2.doc |c:costtest_2.doc|
Downloaded from internet : This script does the search in folder & sub folder and returns the path of files.
Code:
Sub search()
Dim myDir As String, myList()
With Application.FileDialog(msoFileDialogFolderPicker)
If .Show = True Then
myDir = .SelectedItems(1)
End If
End With
On Error Resume Next
myList = SearchFiles(myDir, “*.doc”, 0, myList())
If Err = 0 Then
Sheets(1).Cells(2).Resize(UBound(myList, 2), UBound(myList, 1)).Value = _
Application.Transpose(myList)
Else
MsgBox “No file found”
End If
On Error Goto 0
End Sub
Private Function SearchFiles(myDir As String _
, myFileName As String, n As Long, myList()) As Variant
Dim fso As Object, myFolder As Object, myFile As Object
Set fso = CreateObject(“Scripting.FileSystemObject”)
For Each myFile In fso.getfolder(myDir).Files
If (Not myFile.Name Like “~$*”) * (myFile.Name ThisWorkbook.Name) _
* (myFile.Name Like myFileName) Then
n = n + 1
Redim Preserve myList(1 To 2, 1 To n)
myList(1, n) = myDir & “” & myFile.Name
End If
Next
For Each myFolder In fso.getfolder(myDir).subfolders
SearchFiles = SearchFiles(myFolder.Path, myFileName, n, myList)
Next
SearchFiles = IIf(n > 0, myList, “”)
End Function
