[VBA] Open file that starts with cell value

ExcelNooberino

New Member
Joined
Jan 2, 2019
Messages
42
Office Version
  1. 2016
Platform
  1. 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:

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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Try
VBA Code:
sFileName = Dir(fPath & "*" & pCode & "*.XLSX")   'Open the product BOM File
 
Upvote 0
In that case it can't find an xlsx file that contains pcode in the specified folder.
 
Upvote 0
I don't know about that, but when I do a mouseover the sFileName it shows nothing "" and it's not supposed to... At least it should show "*pCode*"
 
Upvote 0
No it shouldn't. It is showing "" because it cannot find an xlsx file in the specified directory that contains whatever is in pcode.
What is the value of pCode?
By pCode I meant it's value... For example 301263, when the file name in that folder is "301263 - Product Description"
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top