how to open latest date in file name

[jason1000rr]

Hi Everyone--
Looking for guidance to open file with the latest date within its name. Below is what I am working with. I typed "Latest Date" where in name it would go. Name is always recent to current month, but date is older. Any help would be much appreciated. Thank you

Fname = "\\global.corp.net\someplace\someplaceProcurement\IQR\IQR " & LatestDate & ".xlsx"
Set wbb = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, Notify:=False)
For Each wb In Application.Workbooks


I used below for another file I used to work with and it worked for when it was current date. But, not able to figure out for latest date.

Dim curDate As String, Fname As String
curDate = Format(Date, "yyyy-mm-dd")
Dim wba As Workbook, wbb As Workbook, wbc As Workbook, wbd As Workbook, wbe As Workbook, wbf As Workbook, wb As Workbook
Fname = "Y:\Consumables\corporation\DMC\DMC - Planning & Materials\On Hand Reports\DMC Site\" & curDate & "_INV_R12_DMC_Onhandreport.xlsx"
Set wba = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, Notify:=False)

 

[davesexcel]

Maybe play with this.

VBA To Open Latest File in Folder

How to Open Newest File in a Folder in Microsoft Excel In case you want a VBA code which will allow you to open the recently saved file in the folder with just a click on macro button. Question): … Continue reading →

www.exceltip.com

 

[Yongle]

I am away from my PC so cannot test this
I think this will find the latest file based on the NAME of the file using your naming convention
Place both procedures in the same nodule
Amend file path and file name
Run TestFunction

Sub TestFunction()
    Const fPath = "c:\folder\subfolder\etc"                     'amend
    Const fExt = "xlsx"
    Const fName = "name excluding date stamp"                   'amend
    MsgBox GetLatestFile(fPath, fName, fExt)

End Sub

Private Function GetLatestFile(xPath, xName, xExt)
    Dim f As String, latest As String
    f = Dir(xPath & "\" & xName & "*." & xExt)
    Do While Len(f) > 0
        If f Like xName & "*." & xExt Then latest = f
        f = Dir
    Loop
    GetLatestFile = latest
End Function

 

[jason1000rr]

Maybe play with this.

VBA To Open Latest File in Folder

How to Open Newest File in a Folder in Microsoft Excel In case you want a VBA code which will allow you to open the recently saved file in the folder with just a click on macro button. Question): … Continue reading →

www.exceltip.com

Thank you. This worked quite well.

 

[jason1000rr]

Below is what I entered and worked for opening the file with latest date. Got one more issue I have been searching to resolve without a solution yet.
This excel file has 4 tabs. I need to select the tab that has a date format as the tab name (ex. 12262019). It is the only tab with a name as such.
And finally, need to close the below excel file once done. Could you please assist? Thank you

Dim MyPath As String
Dim MyFile As String
Dim LatestFile As String
Dim LatestDate As Date
Dim LMD As Date
MyPath = "\\global.corp.net\brea\Procurement\IQR"
If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
MyFile = Dir(MyPath & "*.xlsx", vbNormal)
If Len(MyFile) = 0 Then
MsgBox "No files were found...", vbExclamation
Exit Sub
End If
Do While Len(MyFile) > 0
LMD = FileDateTime(MyPath & MyFile)
If LMD > LatestDate Then
LatestFile = MyFile
LatestDate = LMD
End If
MyFile = Dir
Loop
Workbooks.Open MyPath & LatestFile

 

[GWteB]

As far as I know there is no way to identify a worksheet based on the formatting of its name.
A possible approach is to use a procedure that determines whether the first eight characters of the sheet name consist of numbers, for example ...

Public Sub Example()

    Dim MyPath      As String
    Dim MyFile      As String
    Dim LatestFile  As String
    Dim LatestDate  As Date
    Dim LMD         As Date
    
                ' additional declarations
                Dim oWb         As Workbook
                Dim oWs         As Worksheet
                Dim lngTmp      As Long
            
    MyPath = "\\global.corp.net\brea\Procurement\IQR"
    If Right(MyPath, 1) <> "\" Then
        MyPath = MyPath & "\"
    End If
    MyFile = Dir(MyPath & "*.gif", vbNormal)
    If Len(MyFile) = 0 Then
        MsgBox "No files were found...", vbExclamation
        Exit Sub
    End If
    Do While Len(MyFile) > 0
        LMD = FileDateTime(MyPath & MyFile)
        If LMD > LatestDate Then
            LatestFile = MyFile
            LatestDate = LMD
        End If
        MyFile = Dir
    Loop
    
                ' additional code
                Set oWb = Workbooks.Open(MyPath & LatestFile)
                With oWb
                    For Each oWs In .Worksheets
                        On Error Resume Next
                        lngTmp = CLng(Left(oWs.Name, 8))
                        On Error GoTo 0
                        If lTmp <> 0 Then
                            Exit For
                        End If
                    Next

                    If Not oWs Is Nothing Then
                        With oWs
' -------------------
' your code goes here
' oWs points to your worksheet (no selection required)
' -------------------
                        End With
                    End If

                    ' closing the workbook we've just opened                    
                    .Close
                End With
                ' cleaning up
                Set oWs = Nothing
                Set oWb = Nothing
End Sub

 

[GWteB]

Beware! I made a typo in the above code (sorry) ...
Replace te For Each loop with the code below.

                    For Each oWs In .Worksheets
                        On Error Resume Next
                        lngTmp = CLng(Left(oWs.Name, 8))
                        On Error GoTo 0
                        If lngTmp <> 0 Then
                            Exit For
                        End If
                    Next

 

[jason1000rr]

Beware! I made a typo in the above code (sorry) ...
Replace te For Each loop with the code below.

                    For Each oWs In .Worksheets
                        On Error Resume Next
                        lngTmp = CLng(Left(oWs.Name, 8))
                        On Error GoTo 0
                        If lngTmp <> 0 Then
                            Exit For
                        End If
                    Next

Thank you. Couldn't get it to work. It doesn't give an error code, but doesn't change tabs either.

 

[jason1000rr]

i was thinking maybe something like if the sheet name has a 20 in it then activate. (just not sure how to do so)

 

[GWteB]

... but doesn't change tabs either.

You're right, but that's not necessary. The oWs already refers to the chosen sheet; if you insert a line in your code (at "your code goes here") like
.Select
the sheet (tab) will be selected.