How to open files from selected folder based on partial cell value

[ThrottleWorks]

Hi,

I have list of files names in a column.
I need to match these partial names and open corresponding file from the folder.
For example, I have cell value as ‘Yamaha’, file name in the folder is ‘Yamaha RX100’.

I am trying to match this ‘Yamaha’ from cell value and open ‘Yamaha RX100’ file from folder.
Can anyone please help me in this.

 

[Chihiro]

Is folder location known, or do you need to search for that as well?

If latter, I'd recommend using GREP or other means to find the file, rather than VBA.

If former, something like below.

Sub Demo()
Dim myFile As String
Dim cel As Range
For Each cel In ThisWorkbook.Sheet1.Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    myFile = Dir("C:\Test\" & "*" & cel.Value & "*" & ".xlsx", vbNormal) 'Change path as needed
     If myFile = "" Then
        MsgBox "File Not Found"
        GoTo Skip
    End If
    Do While myFile <> ""
        Workbooks.Open (myFile)
        myFile = Dir
    Loop
Skip:
Next
End Sub

 

[ThrottleWorks]

Hi @Chihiro sir, thanks a lot for the help. I am checking this and will revert with details. Have a nice day ahead.