[VBA] Open file that starts with cell value

[ExcelNooberino]

Hi guys! First of all hope everybody is doing fine and wishes for a good 2021...

So, the thing is, I'm trying to create a macro that allows me to open a specific file within a folder path.. Untill here, nothing new, even I've done this before. The hard part is when the file name is not controlled by me, and I need to look for the file name from the code that it starts with. For example, a product code in my case, the file name is "301263 - Product Description" and I'm trying to open it by searching the path and the code itself... I know that in the windows file explorer it's possible to filter the file with * like, *301263* is there anyway i can implement something similar to my macro? So far I've got this:

Public Sub Yolo()

Dim LastRowQuantity As Long
Dim LastRow As Long
Dim LastRowData As Long
Dim sht As Worksheet
Dim data As Worksheet

Application.Wait (Now + TimeValue("0:00:01"))

Application.ScreenUpdating = False

Set data = Sheets("Data")
Set sht = Sheets("Code List")

LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
 
    For cell = 2 To LastRow
        
Dim sFileName As String, fPath As String
Dim pCode As Variant
Dim pDesc As Variant

pCode = sht.Range("A" & cell).Value
pDesc = sht.Range("B" & cell).Value

fPath = "Z:\Folder1\"
sFileName = fPath & "*" & pCode & "*" & ".XLSX"   'Open the product BOM File
If sFileName <> "" Then
    Set wb1 = Workbooks.Open(sFileName)
End If

Next cell

End Sub

 

[ExcelNooberino]

Yes but what exactly does it show when you hover the mouse over pCode?

The value of the cell, in this case 301263

 

[Fluff]

Is that what you see when you hover the mouse over pCode?

 

[ExcelNooberino]

Is that what you see when you hover the mouse over pCode?

Yes, just like the image shows:

 

[Fluff]

In that case is there a file in your Z:\Folder1\ directory with that number & is it an xlsx file & not an xls or xlsm file?

 

[ExcelNooberino]

In that case is there a file in your Z:\Folder1\ directory with that number & is it an xlsx file & not an xls or xlsm file?

So stupid of me... I'm sorry, the file does exist but it is in the .xls format ?

 

[Fluff]

In that case use

sfilename = Dir(fpath & "*" & pcode & "*.XLS*")

This will catch all Xl files.

 

[ExcelNooberino]

Yes I did that change in the code as well and now it's opening the one I wanted, no problems at all! I really appreciate your help mate, cheers!

Happy holiday and a better 2021 ?

 

[Fluff]

You're welcome & thanks for the feedback.