VBA to open LATEST Modifide file based on partion file name

[amxtomzo]

I do think I am close with what I am attempting

I would like to open the latest modified file based on a partial file name
the partial file name is "WTBY Diff Thursday" ( they usually end in a date ddmmyyy )
all files are .XLS
there usually only 2 to 4 files with this partial name in the folder I am looking through

I think I am close, based on other posts and books that I have looked at

so far "I THINK" am only locating the folder, but since nothing is opening
I am not sure
and I still have to figure out how to open the latest modified file with that partial name

here is what I have so far

Sub openlatestfile()
Dim sStartPath As String
Dim sFileName As String
Dim sFileExt As String
Dim sBOFile As String 'File to open
sFileExt = "*.xls*" 'This is my File Ext- Change to suit
sStartPath = "v:\\repub-file1\Packaging\Waterbury Reports\" 'Location of Folder to look in
'Reads the first file in the directory
sFileName = Dir(sStartPath + sFileExt, vbNormal)
'If the two left characters are "WT" assume as targeted file
If Left(UCase(sFileName), 2) = "WTBY Diff Thursday" Then
    sWTFile = sFileName ' NOTE TO SELF.. see the WT in sWTFile
End If
Debug.Print sFileName
'looking thru rest of files in directory
Do While sFileName <> ""
'  I THINK....some where in here I need to find the latest file
' of "WTBY Diff Thursday"
 
    sFileName = Dir
    
    'If the two left characters are "WT" then assume as targeted file
    If Left(UCase(sFileName), 2) = "WTBY Diff Thursday" Then
        sWTFile = sFileName
    End If
    Debug.Print sFileName
    
Loop
If sWTFile <> "" Then
    MsgBox "Open: " & sWTFile
    'Open the file here
    Application.Workbooks.Open sStartPath & sWTFile
    
Else
    MsgBox "No file found"
End If
End Sub

I would still need to figure how to find the latest modified file,
and only open that one file

as always that you very much for your help and support

Thomas

 

[JoeMo]

I'd be inclined to use the file system object for this. Here's some code that will get you started. Untested, so try running it as is and if it works then note the comment near the end for suggested adaptation to identify the latest version and open it.

Sub openlatestfile()
Const sPartialName As String = "WTBY Diff Thursday"
Dim sStartPath As String
Dim sFileExt As String
Dim fso, fldr, fil, S

sStartPath = "v:\\repub-file1\Packaging\Waterbury Reports\" 'Location of Folder to look in
sFileExt = "*.xls*"
Set fso = CreateObject("Scripting.Filesystemobject")
Set fldr = fso.getfolder(sStartPath).Files
For Each fil In fldr
    If fil.Name Like sPartialName & "*" & sFileExt Then
        'you can assign the date of interest below to a variable and test it for
        'each file to find the file that produces the latest date
        S = fil.Name & vbCrLf
        S = S & "Created: " & fil.DateCreated & vbCrLf
        S = S & "Last Accessed: " & fil.DateLastAccessed & vbCrLf
        S = S & "Last Modified: " & fil.DateLastModified
        MsgBox S
    End If
Next fil
End Sub

 

[amxtomzo]

JoeMo
thanks again for your help

I had to remove the drive letter from the destination folder, within the code,
And the code found 3 files.

Looks great,
I see all the info in the message box and it
displayed them 1 at a time, in the Message box,
the last one it displayed was the one with latest modified date

So, how do I open that file, the one with the latest modified date?

Thomas

 

[JoeMo]

JoeMo
thanks again for your help

I had to remove the drive letter from the destination folder, within the code,
And the code found 3 files.

Looks great,
I see all the info in the message box and it
displayed them 1 at a time, in the Message box,
the last one it displayed was the one with latest modified date

So, how do I open that file, the one with the latest modified date?

Thomas

As I suggested, replace the message box with a test of each file's date to see which is the latest. Untested, but something like this:

Sub openlatestfile()
Const sPartialName As String = "WTBY Diff Thursday"
Dim sStartPath As String
Dim sFileExt As String
Dim fso, fldr, fil, S, myDate, filToOpen As Workbook

sStartPath = "v:\repub-file1\Packaging\Waterbury Reports\" 'Location of Folder to look in
sFileExt = "*.xls*"
Set fso = CreateObject("Scripting.Filesystemobject")
Set fldr = fso.getfolder(sStartPath).Files
For Each fil In fldr
    If fil.Name Like sPartialName & "*" & sFileExt Then
       If fil.datelastmodified > myDate Then
              myDate = fil.datelastmodified
              Set filToOpen = fil
       End If
End If
Next fil
If Not filToOpen Is Nothing Then
       Workbooks.Open Filename:=filToOpen.Name
Else
       MsgBox "No workbook found in specified folder."
End If
End Sub

BTW your drive letter was followed by an extra "" which is why it errored. I have removed this and strongly suggest you leave the drive letter in place so the full path is available.

 

[amxtomzo]

JoeMo

I don't want you think I gave up on this project, just had to put it on the back burner

I am hoping to get back into it over the week end
I will make note of the quotes and leave the driver letter also

Thanks again

Thomas

 

[JoeMo]

JoeMo

I don't want you think I gave up on this project, just had to put it on the back burner

I am hoping to get back into it over the week end
I will make note of the quotes and leave the driver letter also

Thanks again

Thomas

FYI - the quotes were meant to contain a backslash character. Your original post included two backslashes after the drive letter - one should be removed. I would suggest copying and pasting the code in post #4 directly from your browser to get the correct syntax.