Search and open a Word file by name from the cell

Question

I have code that searches for and opens a Word file with the same name that value from the cell ("B9"):

Sub find()
    Dim f As String, folder As String, file_name As String
    Set wordapp = CreateObject("word.Application")
    folder = ThisWorkbook.Path & "\" 'Folder
    file_name = LCase(Range("B9")) & ".docx" 'Cell with file name
    f = Dir(folder)
    While Not Len(f) = 0
        If LCase(f) = file_name Then
            wordapp.documents.Open folder & f
            wordapp.Visible = True
            Exit Sub
        End If
        f = Dir()
    Wend
End Sub

How to modify this code so that it takes a value to search for a file not from a specific cell, but checks the entire column "B", if it does not find a file with the same cell name, then notifies that there is no such file.

Can someone help me with these? Thanks.

I'd suggest you to have a Range e.g. "B1:B100" (if you have 100 rows) and iterate over the result with a For-Loop. Match the cell content inside that For-Loop and if it the cell contents matches, exit the For-Loop. Perhaps https://stackoverflow.com/questions/9414969/excel-vba-exit-for-loop can help — Igor, Jun 02 '18 at 17:38

score 1 · Accepted Answer · answered Jun 02 '18 at 20:29

Try the following.

Sub find()
    Dim f As String, folder As String, file_name As String

    Set wordapp = CreateObject("word.Application")
    folder = ThisWorkbook.Path & "\" 'Folder
    f = Dir(folder)
    While Not Len(f) = 0
        If Not (IsError(Application.VLookup(LCase(f), Worksheets("Sheet1").Range("B:B"), 1, True))) Then
            wordapp.documents.Open folder & f
            wordapp.Visible = True
            Exit Sub
        End If
        f = Dir()
    Wend
End Sub

Search and open a Word file by name from the cell

1 Answers1