VBA File search for text

montecarlo2079

Board Regular
Joined
Feb 9, 2011
Messages
207
I want to be able to use text in a certain cell or input box be used to search through a folder and have every file name returned that the text is found in.

for example I want to search for the name "Smith" in a specified folder, and have vba return each file name that the word "smith" appears in

I found thise code, but for some reason its not working at all. in excel 2007

any ideas?


Sub FindText()Dim i As Integer'Search criteriaWith Application.FileSearch .LookIn = "c:\my documents\logs" 'path to look in .FileType = msoFileTypeAllFiles .SearchSubFolders = False .TextOrProperty = "*Find*" 'Word to find in this line .Execute 'start search'This loop will bring up a message box with the name of'each file that meets the search criteria For i = 1 To .FoundFiles.Count MsgBox .FoundFiles(i) Next iEnd WithEnd Sub</PRE>
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
You can't use FileSarch in Office 2007 as it has been removed you have to use Dir commands.

Sample code to open all workbooks in a folder


Sub Open_My_Files()
Dim MyFile As String
MyPath = "M:\Access Files\"
MyFile = Dir(MyPath)
Do While MyFile <> ""
If MyFile Like "*.xls" Then
Workbooks.Open MyPath & MyFile
End If
MyFile = Dir
Loop
End Sub
 
Upvote 0
Hey, was searching for something like this a while back and found out that a piece of functionality was removed from 2007 and 2010 that made this a bit harder. Firstly I'd suggest using windows search for files containing your search term.

If you're looking for something a bit more complex have a go with the attached xlsm. It probably doesnt do exactly what you want as it opens every excel file in a folder and searches for the term then returns that row, but its a start!

I cant take credit for all the code, I mostly just mashed together bits and bobs I found on the net

Silly question - how do I attach a file?
 
Upvote 0
You can't attach a file to the Forum, but you can use a third party upload facility and then add a link to where you have uploaded the workbook.

There are many exampls of file searching in 2007 and 2010 here in the forum.
 
Upvote 0
maybe I should rephrase. Ive searched all over

Cell A1 on my worksheet is a place that a user will input a name or text to search for. they will hit a button and execute the code.


The file names should be returned in columb b starting with cell b1.


This gives the user a head start to find the file its in and search for additional information without having to start searching in windows

Each one of my tabs are a different client with their own specific folders so I can specify different places to look to save some time.
 
Upvote 0
Whittick, that file is awesome. But I am going to be searching through all different types of files, not just spreadsheets. .txt, .pdf etc
 
Upvote 0
Use something like this to return your file names, you can adapt it to point to Cell A1 content.

[QUOTE]
Option 1
Simple List All files in a Folder
Sub mefiles1()
'List files in a folder
F = Dir("m:\Access Files\*.doc") 'Change extension to *.* for all files
Range("A1").Activate
Do While Len(F) > 0
ActiveCell.Formula = F
ActiveCell.Offset(1, 0).Select
F = Dir()
Loop
End Sub

Option 2
Sub mefiles2()
'List files in a folder
'Clear anything first
Sheets("Sheet1").Range("A:A").Clear
F = Dir("m:\Access Files\*.xls")'Change extension to *.* for all files
Range("A1").Activate
Do While Len(F) > 0
ActiveCell.Formula = F
ActiveCell.Offset(1, 0).Select
F = Dir()
Loop
End Sub
[/QUOTE]
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top