Open closed path with wildcard vba

beginvbaanalyst

Board Regular
Joined
Jan 28, 2020
Messages
116
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.
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

EFANYoutube

Board Regular
Joined
May 19, 2017
Messages
162
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
 

beginvbaanalyst

Board Regular
Joined
Jan 28, 2020
Messages
116
Office Version
  1. 365
Platform
  1. Windows
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?
 

EFANYoutube

Board Regular
Joined
May 19, 2017
Messages
162
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
 

beginvbaanalyst

Board Regular
Joined
Jan 28, 2020
Messages
116
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:

EFANYoutube

Board Regular
Joined
May 19, 2017
Messages
162
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
 

beginvbaanalyst

Board Regular
Joined
Jan 28, 2020
Messages
116
Office Version
  1. 365
Platform
  1. Windows
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!
 

EFANYoutube

Board Regular
Joined
May 19, 2017
Messages
162
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,295
Messages
5,595,289
Members
413,984
Latest member
stikpet

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