ExcelNooberino
New Member
- Joined
- Jan 2, 2019
- Messages
- 42
- Office Version
- 2016
- Platform
- Windows
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:
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:
VBA Code:
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