Excel VBA. Search tool

Question

I am a beginner with Excel VBA and have some questions.

I want to search a specified folder based on user input (a file name). I can get that part to work, however, I want it to search for more than just the one format (.docx), and also include a search of both .pdf and .doc.

Clarification:
The folder under G:\NEWFOLDER\NAMEFOLDER contains files with extensions .doc, .docx, and .pdf and I want to search the entire folder and report back to my spreadsheet on Sheet2.

Dim NAME As String
Dim File_Path As String
    NAME = InputBox(" Enter Your NAME (EX: JOHNP) ")
    File_Path = "G:\NEWFOLDER\NAMEFOLDER" & NAME & ".docx"

    If Dir(File_Path) <> "" Then
        ThisWorkbook.Sheets("Sheet2").Activate
        Range("D5") = ("Checked")
        Range("E5") = NAME
    Else
        MsgBox "NAME Not found"
    End If
End Sub

How do I search the document within?

Clarification:
The above code only tells me if the user input is located inside the coded path. The next step I want to do is to search within that document for keyword and report back to spreadsheet. For example, within JOHNP.doc there is a column of age. I want the code to report back to Sheet2 cell with "22".

Is this even possible with word document search, or is it better if the JOHNP is in excel format?

Here is a good description of "How to use an Excel Macro to search a Word Document https://www.mrexcel.com/forum/excel-questions/646835-use-excel-macro-find-text-docx-file.html — FloLie, Apr 02 '18 at 15:45
And here is a good description of how to search for files within a folder with `wildcards` so you can just search for the name without the file type https://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba — FloLie, Apr 02 '18 at 15:47

score 0 · Answer 1 · answered Apr 02 '18 at 15:59

This should help you a little bit - This will cycle through files in the named folder location (if it exists), and will only target ones that are .doc, .docx or .pdf.

As for your second question - Yes, you can pull that number from your documents, however, you'll need to be more specific as to where that number is. If it's in the same spot each time, then that would be fairly easy - hopefully in a Table, then it would have an explicit reference (like ActiveDocument.Tables(1).Cells(1,1), etc. For now, this code below will go through all the files and when it finds the first match, it'll open the word document for you (then exit the loop).

Sub Test()
Dim NAME As String
Dim File_Path As String
Dim StrFile As String

    NAME = InputBox(" Enter Your NAME (EX: JOHNP) ")
    File_Path = "G:\NEWFOLDER\NAMEFOLDER\" & NAME & "\"

    StrFile = Dir(File_Path)

    If Dir(File_Path) <> "" Then
        Do While Len(StrFile) > 0
            If InStr(StrFile, ".doc") > 0 Or _
               InStr(StrFile, ".pdf") > 0 Then
                Debug.Print StrFile
                'ThisWorkbook.Sheets("Sheet2").Range("D5") = ("Checked")
                'ThisWorkbook.Sheets("Sheet2").Range("E5") = NAME

                If InStr(StrFile, ".doc") > 0 Then
                    Set wordapp = CreateObject("word.Application")
                    wordapp.documents.Open File_Path & StrFile
                    wordapp.Visible = True
                    Exit Do
                End If
            End If
            StrFile = Dir
        Loop
    Else
        MsgBox NAME & " Not found"
    End If
End Sub

I tried your above code but the only thing pop up is the Else MsgBox. I don't think it found anything. ( i am sure the path and the file name i tested on is correct.) — LitDuck, Apr 02 '18 at 18:01
@LitDuck So if you entered "JOHN" in the search box, that's looking for the directory "G:\NEWFOLDER\NAMEFOLDER\JOHN\". The only reason it's returnin that `MsgBox` is because that doesn't exist. — dwirony, Apr 02 '18 at 20:01

Excel VBA. Search tool

1 Answers1