[VBA] Open file that starts with cell value

ExcelNooberino

New Member
Joined
Jan 2, 2019
Messages
40
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
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
Try
VBA Code:
sFileName = Dir(fPath & "*" & pCode & "*.XLSX")   'Open the product BOM File
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
In that case it can't find an xlsx file that contains pcode in the specified folder.
 

ExcelNooberino

New Member
Joined
Jan 2, 2019
Messages
40
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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*"
 

ExcelNooberino

New Member
Joined
Jan 2, 2019
Messages
40
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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"
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
But what is the value of pcode when it fails to find the file?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
Yes but what exactly does it show when you hover the mouse over pCode?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,335
Messages
5,624,090
Members
416,010
Latest member
NJT

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
Top