VBA to search files within a folder for a specific string and return file name

Hi everyone,

First time on a forum.

I'm fairly new to excel vba and I'm currently using 2010.

I'm looking for a code in Excel vba that will:
- Take a string from a cell A1 i.e. "hello" and search a folder with 20 word files for that string.
- The output will be a list of file names (rows B1 to B..) in which "hello" appears at least once.

Is this possible? I've research into Dir and wildcard functions but I'm too much of a novice to even begin.

Any help would be greatly appreciated.

Thanks.

Bernie,

I really appreciate your response.

I actually need to search through the contents of the files.

You say this if different, not difficult...hmmm haha.

Cheers again,

Tim

Bernie,

I also want a command button to activate the macro (i.e. a search button).

The folder location to search in is: P:\023 Capability Management System (CMS).

Apologies for piling it on.

Hi..

This works.. I haven't really done much at all concerning Word.. but it seems to do the trick..

Change the Filepath and Sheet names to suit..

Private Sub CommandButton1_Click()
    Dim x, i As Long, wrnge
    ' Set Folder to Search
    strpath = "D:\Test1\"

    ' Put all .doc* files found in folder (and subfolders) into an array
    x = Split(CreateObject("wscript.shell").exec("cmd /c Dir """ & strpath & "*.doc"" /s/b").stdout.readall, vbCrLf)

    ' Loop though the array of .xls* files
    For i = LBound(x) To UBound(x) - 1

        With GetObject(x(i))
            'Search for first instance of text, and if true, then do stuff
            Set wrnge = .Content
            wrnge.Find.Text = Range("A1").Value
            If wrnge.Find.Execute Then
                Sheets("Sheet1").Range("B" & Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row).Offset(1).Value = x(i)
            End If
            .Close
        End With
    Next i
End Sub

Many thanks - I was just about to ask a very similar question.

Can the solution(s) be adapted:

1) to allow subfolders to be searched
2) for the file path results in the Excel worksheet to be produced as hyperlinks that allow the relevant file to be opened?

Thanks.

Originally Posted by 1819

Many thanks - I was just about to ask a very similar question.

Can the solution(s) be adapted:

1) to allow subfolders to be searched
2) for the file path results in the Excel worksheet to be produced as hyperlinks that allow the relevant file to be opened?

Thanks.

Apologies - it already does subfolders.