PDA

View Full Version : [SOLVED] VBA to search given cell value in given folder path



samuel_1982
05-19-2015, 04:32 AM
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
05-19-2015, 07:09 AM
If I understand correctly,



You want to put the file names in the folder shown in A1 into F1, G1, etc?
OR
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
05-19-2015, 08:13 AM
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
05-19-2015, 06:26 PM
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
05-20-2015, 03:25 AM
Hi Sam

Thanks for the code,

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

SamT
05-20-2015, 06:24 AM
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
05-20-2015, 07:51 AM
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
05-20-2015, 08:23 AM
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
05-20-2015, 11:13 AM
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