Creating a seach engine in an excel 2013 spreadsheet


Hi All,

Been tasked with creating a search engine in excel to search a targeted folder containing many files in different formats.

Is there a way I can search for files/folders containing a word entered into an excel cell? Then listing all the files in the excel spreadsheet, when clicking on a search command button? List starting from a specific cell.

In essence the excel spreadsheet search engine acting like a librarian for a database.

many thanks in advance.



The built-in windows search feature exists to do exactly this....
Are you sure this needs to be in Excel?
It can be done, I just wouldn't recommend it unless you have no choice.
don (rep: 1745) Jul 10, '17 at 5:09 am
Thats what my answer was. But the powers that be want a stand alone one made to be placed in a specific folder, and only to search in that folder. think they have plans to use this for "in field use"
Daz1983 (rep: 6) Jul 10, '17 at 5:14 am
Can this be done in VBA? I have seen a few examples, tried to implement them into the command button click() with no luck.
Daz1983 (rep: 6) Jul 10, '17 at 5:27 am
I am not at a computer right now but it can be done. I imagine the easiest way to do it is to combine a macro that lists or iterates through all files from a directory with a search macro or code that filters results during the iteration process. That should be easy and combines a couple macros that are here on teahexcel - you can find them by searching from the top of the site.

There might be a specific file search feature but Im not entirely sure; however the above method should work just fine.
don (rep: 1745) Jul 10, '17 at 6:54 am
Thanks Don, ill have a look and see what comes up.
Daz1983 (rep: 6) Jul 10, '17 at 7:11 am
Didn't have much joy, i know this is sounding like a cry for help. which it pretty much is. This is way out of my VBA knowledge.
Daz1983 (rep: 6) Jul 10, '17 at 7:43 am
Just updated with an Answer. It should get you up and running!
don (rep: 1745) Jul 10, '17 at 7:48 am
Add to Discussion


Selected Answer

Here is sample code to get you started:

Sub GetFileNames()
Dim xRow As Long
Dim xDirect, xFname
Dim Dest As Range

On Error Resume Next

'Change this to put the data in a different place in the workbook.
'ActiveCell places the data in the currently selected cell and below.
Set Dest = ActiveCell

With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & "\"
.Title = "Select a folder"
    If .SelectedItems.Count <> 0 Then
        xDirect = .SelectedItems(1) & "\"
        xFname = Dir(xDirect, 7)

        Do While xFname <> ""

            If (xFname Like "*telst*") = True Then

                Dest.Offset(xRow) = xFname
                xRow = xRow + 1
                xFname = Dir


                xFname = Dir

            End If


    End If
End With

End Sub

I modified it a little bit from the macro on teachexcel here:

List All Files from a Directory into Excel

You will need to modify to suit your needs but this is the rough version of what you need.

You can learn more about the "search" feature of the macro, the like keyword here:

Like Operator (microsoft's documentation)

The search term is currently telst and the * characters around that word in the macro mean that that word can be located anywhere in the file names.


Answer the Question

You must create an account to use the forum. Create an Account or Login