need to open latest excel workbook automatically

[clammastak]

I have been using the following code successfully for a while now. Upon activation of macro, the program prompts the user to open an excel file , with the specific folder path already open. From there, the user double clicks (opens) the selected file and the programming continues. However, I would like to modify it slightly so the user DOES NOT have to select his/her own file, but Excel will just automatically find the newest file with "Date Last Modified" (usually the first file on top in my share drive) upon activation. Can anyone assist me? It seems relatively simple but couldn't get any short and concise responses online.

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim myFile As String
Dim YourFolderPath As Variant
Dim y As Integer, yY As Integer, x As Integer, xX As Integer
Dim pT As PivotTable, pI As PivotItem
Dim ws As Worksheet


Application.ScreenUpdating = False
'Turn Off Automatic Calculation During Macro Execution (At least one Worksheet Must be Shown)
Application.Calculation = xlCalculationManual


' this workbook
Set wb1 = ActiveWorkbook

    
'initiate Open window with folder destinatoin
YourFolderPath = "S:\Department\Private"
ChDrive YourFolderPath
ChDir YourFolderPath
myFile = Application.GetOpenFilename(, , Title:="S:\Department\Private")

If myFile = "False" Then Exit Sub

'MsgBox myFile
Set wb2 = Workbooks.Open(fileName:=myFile)

[Greg M]

Hi there,

Try the following code and see if it does what you need:

Sub OpenLatestFile()

    Const sFILE_PATH    As String = "S:\Department\Private\"

    Dim dteLatestFile   As Date
    Dim bFileExists     As Boolean
    Dim dteThisFile     As Date
    Dim sFileName       As String
    Dim vFile           As Variant

    sFileName = vbNullString
    dteLatestFile = 0

    On Error Resume Next
        vFile = Dir(sFILE_PATH, vbNormal)
    On Error GoTo 0

    If Not IsEmpty(vFile) Then
          bFileExists = True
    Else: MsgBox "The folder """ & sFILE_PATH & """ cannot be located", vbExclamation
          bFileExists = False
    End If

    While bFileExists = True

        If vFile = vbNullString Then

              bFileExists = False

        Else: dteThisFile = FileDateTime(sFILE_PATH & vFile)

              If dteThisFile > dteLatestFile Then
                  dteLatestFile = dteThisFile
                  sFileName = vFile
              End If

              vFile = Dir

        End If

    Wend

    If sFileName <> vbNullString Then
        Workbooks.Open Filename:=sFILE_PATH & sFileName
    End If

End Sub

The highlighted value can be changed to suit your requirements.

The above routine will open the file most recently modified in the specified folder. It assumes that the most recently modified files in the folder will always be Excel workbooks.

Hope this helps - please let me know how you get on.

Regards,

Greg M