Open newest folder and specific file in the folder

Hi guys,

Could someone advise me on the following please.

I would like to automatize manual process of opening a specific file in a specific folder.

In location: "\\test\UK_test\reports\" folders get created each day, for example today: "\\test\UK_test\reports\05.05.2017\" ...this folder "05.05.2017" contains a specific excel file called"ratios_20170505" which I have to open and copy data from.

Tomorrow new file will be created in this location, for example: "\\test\UK_test\reports\06.05.2017\" and the name of the file inside will be "ratios_20170506"

Ideally I would like to write a macro that would always locate the latest folder and open the file which name starts with "ratios_xxxxxxxx" ?

Many thanks for your help once again.

Let me know if you need any other details.

Best,
Seb

Hi Tim ... the code below goes in a standard module and will search through the directory in which the file is saved (so save it somewhere before running it) and all sub-directories looking for files that start with "Ratios" and progressively identifying the latest such file. When complete it will open the file with the latest create date.

Option Explicit

Public datNewest As Date
Public strLatestFile As String

Sub OpenLatestRatioFile()
Dim strRootDir As String

    datNewest = CDate("01 Jan 0001")
    strRootDir = ThisWorkbook.Path & "\"
    strLatestFile = ""
    Call GetSubDirectories(strRootDir)
    If strLatestFile <> "" Then
        Workbooks.Open Filename:=strLatestFile, UpdateLinks:=False, ignorereadonlyrecommended:=True
    Else
        MsgBox ("No file beginning with Ratios found in directory or subfolders.")
    End If
    
End Sub

Sub GetSubDirectories(strFolder As String)
Dim objFileSystem As Object: Set objFileSystem = CreateObject("Scripting.FileSystemObject")
Dim objFolder As Object: Set objFolder = objFileSystem.GetFolder(strFolder)
Dim objSubFolder As Object
    
    Call GetFiles(objFolder.Path)
    For Each objSubFolder In objFolder.subfolders
        GetSubDirectories (objFolder.Path & "\" & objSubFolder.Name)   'recursive call
    Next objSubFolder
    
End Sub

Sub GetFiles(strPath As String)
Dim objFile As Object
Dim objFileSystem As Object: Set objFileSystem = CreateObject("Scripting.FileSystemObject")
Dim objFolder As Object: Set objFolder = objFileSystem.GetFolder(strPath)
    
    On Error Resume Next
    For Each objFile In objFolder.Files
        If objFile.DateCreated > datNewest And Left(objFile.Name, 6) = "Ratios" Then
            datNewest = objFile.DateCreated
            strLatestFile = strPath & "\" & objFile.Name
        End If
    Next objFile
    On Error GoTo 0
    
End Sub

If you want to specify a different starting directory then change this line in the first routine:

strRootDir = ThisWorkbook.Path & "\"

To something like:

strRootDir = "C:\Users\Tim\"

Remember the path must end with an "\".
Hope that helps. MM.

You're very welcome ... don't forget to mark the thread closed and if would click on the star / add reputation icon, that would be appreciated too.