VBA to search given cell value in given folder path

[samuel_1982]

Hi

Sub test()
Dim Foldername As String
Foldername = Range("A1").Value
Shell "explorer.exe """ & Foldername & "", vbNormalFocus
End Sub

I have above code to open the path given in cell A1 and would like to go to search option on the open window and paste value in column E1 in my excel sheet for search, and the search result file path need to be pasted in column F1, if it gives multiple results then results should be pasted in F1 , G1 and so on...can any one help me on this ...thanks

[SamT]

If I understand correctly,

1. You want to put the file names in the folder shown in A1 into F1, G1, etc?
2. OR
3. You want to get the value of the Cell in E1 from each of the files in the Folder in A1, and put the Values found into F1, G1, etc?

[samuel_1982]

Hi
1.In A1 - The user will give the file path
2. In column E has file names which is there on the path which is mentioned in A1
3. In column F , user need first search result in the path with path structure
example: if cell E1 has "Apple" and in A1 user has given path as C:\\My document\Fruit
in F1 the search result may be come as C:\\My document\Fruit\apple.xlsx
in E1 the search result may be come as C:\\My document\Fruit\apple.doc
hope this help in

[SamT]

Try this, it has been tested with one path in A1, two names in F, and for four files with those names and two extensions.

Option Explicit

Sub GetFullName()
'Retrieves Filenames from Path in Cell A1 by Partial name in Column E
'Places Files' FullNames in columns following Column E

Dim SubPath As String
Dim SearchNames As Range
Dim NameColumn As Long
Dim NameRow As Long 'used for multiple names in Column E
Dim Cel As Range
Dim FoundName As String

  With ActiveSheet
    SubPath = .Range("A1").Text
    Set SearchNames = Intersect(Range("E:E"), .UsedRange)
    
    'Loop thru the Cells in Column E
    For Each Cel In SearchNames
      If Cel = "" Then Exit Sub
      NameColumn = 6  'Start each row aiming at column G
      NameRow = Cel.Row
       
      'Search
       FoundName = Dir(SubPath & "\" & Cel.Value & ".*")
     
      Do While FoundName <> "" 'Repeat search until all named files found
        .Cells(NameRow, NameColumn) = SubPath & "\" & FoundName
        NameColumn = NameColumn + 1  'Use next Column
        FoundName = Dir
      Loop
    
    Next Cel 'Next Cell in Column E
  End With
      
End Sub

[samuel_1982]

Hi Sam

Thanks for the code,

The code properly searching the file however it's now showing(printing) file path in column

[SamT]

Of course it does

Hi
1.In A1 - The user will give the file path
2. In column E has file names which is there on the path which is mentioned in A1
3. In column F , user need first search result in the path with path structure
example: if cell E1 has "Apple" and in A1 user has given path as C:\\My document\Fruit
in F1 the search result may be come as C:\\My document\Fruit\apple.xlsx
in E1 the search result may be come as C:\\My document\Fruit\apple.doc
hope this help in

Edit this line to suit

.Cells(NameRow, NameColumn) = SubPath & "\" & FoundName

[samuel_1982]

Thanks Sam It worked well, I really appreciate your help.

Can you guide me to include files from sub folders on this search as it's not including now

[SamT]

You'll need to Dim SubDirName as a variant for this to work

Declare a Collection variable Dim SubDirNames As Collection

Modify this code as needed to add the SubDirName(s) to the Collection SubDirNames

' Display the names in C:\ that represent directories.
MyPath = "c:\"    ' Set the path.'
MyName = Dir(MyPath, vbDirectory) 
' Retrieve the first entry. 
Do While MyName <> "" 
' Start the loop.     ' Ignore the current directory and the encompassing directory.
    If MyName <> "." And MyName <> ".." Then 
        ' Use bitwise comparison to make sure MyName is a directory. 
        If (GetAttr(MyPath & MyName) And vbDirectory) = vbDirectory Then 
            Debug.Print MyName    '''''''''''''''''''''''''''''''''''''''''''''''''''''''' SubDirNames. Add SubDirName
        End If    ' it represents a directory. 
    End If 
    MyName = Dir 
   ' Get next entry.
Loop

Then loop thru the SubDirNames collection with

For Each SubDirName in SubdirNames

in the code you already have.

If you have more than 1 subfolder deep, then you are out of my league. It would take me all night to figure that one out.

Why don't you take all night, then we here at VBAX can go over what you have.

[samuel_1982]

Thanks Sam, This helped me a lot. I am working on including the sub folder will update you once done ... Thanks again for the great help