Open closed path with wildcard vba

beginvbaanalyst

Board Regular
Joined
Jan 28, 2020
Messages
139
Office Version
  1. 365
Platform
  1. Windows
Hi everyone,

I've been having trouble figuring out how to open a closed excel document without using the direct closed path.
We update this file on occasion when something breaks so I need the macro to understand when a new file has been created with the same text.

My practice macro build is below
VBA Code:
Dim ListScript As String
Dim Path1 As String
Dim Path2 As String
Dim ListFile As String
Dim FileName As String

Path1 = "A:\Scripts Library\Script Execution\Execution Templates\ASSORTMENT\"
Path2 = "WSM3_LISTING.xlsx\"
ListFile = Path1 & Path2
FileName = Dir(ListFile & "*")
If FileName <> "" Then
Workbooks.Open (ListFile & FileName)
End If
End Sub

My Macro breaks at the FileName and I can't figure out why.
Is anyone able to help?
Thank you.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Hi BeginVBA

We need a bit more info
It looks like you have a folder called WSM3_LISTING.xlsx? Its a strange and confusing folder name
How many files are inside the folder and how similar is the naming?

My suggestion would be to check each file name to see if its close to the one you want then redference it.

Clear up some of the questions above and I'll see if I can help
 
Upvote 0
Hi BeginVBA

We need a bit more info
It looks like you have a folder called WSM3_LISTING.xlsx? Its a strange and confusing folder name
How many files are inside the folder and how similar is the naming?

My suggestion would be to check each file name to see if its close to the one you want then redference it.

Clear up some of the questions above and I'll see if I can help
Hello,

It's not a folder it's part of the same path but broken up into two sections, one section I know that will always stay the same. The other section WSM3_LISTING.xlsx will change its name to be WSM3_Listing1.xlsx.
How would I introduce the wildcard in case for an update to the text?
 
Upvote 0
ok so.
Currently your path looks like this
A:\Scripts Library\Script Execution\Execution Templates\ASSORTMENT\WSM3_LISTING.xlsx\
this part: WSM3_LISTING.xlsx\ is understood by the code as a folder

What you want is
A:\Scripts Library\Script Execution\Execution Templates\ASSORTMENT\WSM3_LISTING*.xlsx

So next problem is, you want to open said file. You haven't told VBA what you want to open as this code above may refer to 2 or 3 different files

So do you want to open the original file or, the highest number at the end of the file name, file?
for example

which file wins?
WSM3_LISTING
WSM3_LISTING1
WSM3_LISTING2
WSM3_LISTING3
 
Upvote 0
ok so.
Currently your path looks like this
A:\Scripts Library\Script Execution\Execution Templates\ASSORTMENT\WSM3_LISTING.xlsx\
this part: WSM3_LISTING.xlsx\ is understood by the code as a folder

What you want is
A:\Scripts Library\Script Execution\Execution Templates\ASSORTMENT\WSM3_LISTING*.xlsx

So next problem is, you want to open said file. You haven't told VBA what you want to open as this code above may refer to 2 or 3 different files

So do you want to open the original file or, the highest number at the end of the file name, file?
for example

which file wins?
WSM3_LISTING
WSM3_LISTING1
WSM3_LISTING2
WSM3_LISTING3
Perfect, I didn't realize it was capturing it as a new folder,
The one that would win would be Listing3 as it's the newest script.
How do I implement that?
I figured if I used an asterisk it would look for the most recent file.

Is there a way to just reference the title? WSM3_LISTING; at any place in the C drive, is that at all possible?
 
Last edited:
Upvote 0
The * wildcard just tells the code to not worry about what comes in the middle and because multiple files match the criteria, they all are valid

The bellow code will chech the last created/modified time of the file and open the latest one

what you'll need for the most recent is:

Code:
Sub openFile()
Dim ListScript As String
Dim Path1 As Variant
Dim FileName As String
Dim CurrentFile As Date
Dim File2Open As String

Path1 = "A:\Scripts Library\Script Execution\Execution Templates\ASSORTMENT\"
FileName = "WSM3_LISTING*"

Dim StrFile As String

StrFile = Dir(Path1 & "*" & FileName)
Do While Len(StrFile) > 0
    
    If FileDateTime(Path1 & StrFile) > CurrentFile Then
        File2Open = Path1 & StrFile
        CurrentFile = FileDateTime(Path1 & StrFile)
    End If
    
    StrFile = Dir

Loop

Workbooks.Open File2Open


End Sub
 
Upvote 0
The * wildcard just tells the code to not worry about what comes in the middle and because multiple files match the criteria, they all are valid

The bellow code will chech the last created/modified time of the file and open the latest one

what you'll need for the most recent is:

Code:
Sub openFile()
Dim ListScript As String
Dim Path1 As Variant
Dim FileName As String
Dim CurrentFile As Date
Dim File2Open As String

Path1 = "A:\Scripts Library\Script Execution\Execution Templates\ASSORTMENT\"
FileName = "WSM3_LISTING*"

Dim StrFile As String

StrFile = Dir(Path1 & "*" & FileName)
Do While Len(StrFile) > 0
   
    If FileDateTime(Path1 & StrFile) > CurrentFile Then
        File2Open = Path1 & StrFile
        CurrentFile = FileDateTime(Path1 & StrFile)
    End If
   
    StrFile = Dir

Loop

Workbooks.Open File2Open


End Sub
Holy Crap it worked lol you're the best!

Are you able to add comments to what is being done?
I understand Dim
but not the Do while Len, and what does StrFile mean to you?
Thank you so much for the help!
 
Upvote 0
Code:
Sub openFile()
Dim ListScript As String
Dim Path1 As Variant
Dim FileName As String
Dim CurrentFile As Date
Dim File2Open As String

Path1 = "A:\Scripts Library\Script Execution\Execution Templates\ASSORTMENT\"
FileName = "WSM3_LISTING*"

Dim StrFile As String

StrFile = Dir(Path1 & "*" & FileName) 'set StrFle to first instance that matches criteria
Do While Len(StrFile) > 0 'do the following untll the length of the StrFile string is not 0 (if its 0 then its ref something that doesn't exist) so strFile = ""
    
    If FileDateTime(Path1 & StrFile) > CurrentFile Then 'Currentfile starts as 1/1/1900 00:00. if the FileDateTime of current file being ref is older than                                                                                                         currentFile then
        File2Open = Path1 & StrFile 'set the string of the path and file name to file2open
        CurrentFile = FileDateTime(Path1 & StrFile) 'set currentfile(witch is the file date of the file ref to inFile2Open
    End If
    
    StrFile = Dir ' go to next file instance that matches the criteria

Loop

Workbooks.Open File2Open 'this must be the latest file because if a file with an earlier datetime is discovered it skips


End Sub
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

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