none
Open the last modified file in a directory but specify the filename to be opened RRS feed

  • Question

  • Hello oh great gurus of VBA. ;)

    I currently have a macro that will go to a specified directory and open the last modified excel file. What I can't seem to find is how to do this, only specify the name of the file to look for in a case where the directory may have several different files.

    Here is what I currently have...

    Sub OpenLatestFile()
    
        
    Dim ofile As Object
    Dim dteMax As Date
    Dim oFoundFile As Object
    Dim oFold As Object
    Dim FSO As Object
    Set FSO = CreateObject("scripting.filesystemobject")
    'change folder to suit
    Set oFold = FSO.GetFolder("G:\SAFETY PERFORMANCE SUMMARY\2012 Daily Safety Summary")
    dteMax = 0
    For Each ofile In oFold.Files
    x = ofile.Name
    y = ofile.datelastmodified
    Z = ofile.Type
    With ofile
    
        If .Type = "Microsoft Excel 97-2003 Worksheet" Or .Type = "Microsoft Excel Worksheet" Then
            If .datelastmodified > dteMax Then
                Set oFoundFile = ofile
                dteMax = .datelastmodified
            End If
        End If
    
    End With
    Next ofile
    Workbooks.Open oFoundFile.Path, ReadOnly:=True
    Set FSO = Nothing
    
    Set oFold = FSO.GetFolder("G:\Storage")
    
    End Sub
    

    The file name I am looking for is "ReportDetails.xlsx"

    Wednesday, February 1, 2017 8:28 PM

Answers

  • Change

      If .datelastmodified > dteMax Then

    to

      If .Name = "ReportDetails.xlsx" And .datelastmodified > dteMax Then

    If you want to look at a name pattern, then use code like

      If .Name Like "*Report*" And .datelastmodified > dteMax Then

    Wednesday, February 1, 2017 9:03 PM

All replies

  • Change

      If .datelastmodified > dteMax Then

    to

      If .Name = "ReportDetails.xlsx" And .datelastmodified > dteMax Then

    If you want to look at a name pattern, then use code like

      If .Name Like "*Report*" And .datelastmodified > dteMax Then

    Wednesday, February 1, 2017 9:03 PM
  • Thanks Bernie! Works perfectly! Much appreciated for the quick response as well!
    Wednesday, February 1, 2017 9:30 PM
  • Great! Thanks for letting us know.
    Wednesday, February 1, 2017 10:18 PM