[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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,740
Office Version
  1. 365
Platform
  1. Windows
Is that what you see when you hover the mouse over pCode?
 

ExcelNooberino

New Member
Joined
Jan 2, 2019
Messages
40
Office Version
  1. 2016
Platform
  1. Windows
Is that what you see when you hover the mouse over pCode?
Yes, just like the image shows:

Untitled.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,740
Office Version
  1. 365
Platform
  1. Windows
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

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

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,740
Office Version
  1. 365
Platform
  1. Windows
In that case use
VBA Code:
sfilename = Dir(fpath & "*" & pcode & "*.XLS*")
This will catch all Xl files.
 

ExcelNooberino

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

Watch MrExcel Video

Forum statistics

Threads
1,128,057
Messages
5,628,354
Members
416,313
Latest member
Dan99321

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