Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Creating a seach engine in an excel 2013 spreadsheet

0

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.

Answer
Discuss

Discussion

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: 1989) 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: 1989) 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: 1989) Jul 10, '17 at 7:48 am
Add to Discussion

Answers

0
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"
.Show
    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

            Else

                xFname = Dir

            End If

        Loop

    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.

Discuss


Answer the Question

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