Open most recent .csv file in a folder

Question

I'm pretty new to VBA but I am trying to learn a lot more. Right now I'm trying to code a macro to open the most recent file in my :Z drive that is a comma delimited file (.CSV). The below code doesn't work but I was wondering if anyone had any advice? thanks for you help!

Sub NewestFile()
Dim MyPath As String
Dim MyFile As String
Dim LatestFile As String
Dim LatestDate As Date
Dim LMD As Date

MyPath = "Z:\"
If Right(MyPath, 1) <> “ \ ” Then MyPath = MyPath & “ \ ”
  MyFile = Dir(MyPath & “ * .csv”, 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
End Sub

Answer 1

Here you go. Version 1 I have simply used a msgbox to display the last modified csv in the folder. Version 2 opens the file and uses filedialog due to OP difficulties with file path from fso.GetFolder.

Add reference to MS Scripting runtime (tool > references ) then

Sub GetLastModifiedCSV()

   'Early binding code. Requires reference to MS Scripting Runtime
    Dim fso As Scripting.FileSystemObject     
    Set fso = New Scripting.FileSystemObject

   'Late binding code. To be used instead of two lines above if "user-defined type not defined" /No reference added. You would uncomment line below.

  'Dim fso As Object: Set fso = CreateObject("Scripting.FileSystemObject") 

    Dim myFolder As Object

    Set myFolder = fso.GetFolder("C:\Users\User\Desktop\Test")

    Dim currentFile As Object
    Dim maxFileName As String
    Dim maxDate As Date

    For Each currentFile In myFolder.Files

        If fso.GetExtensionName(currentFile) = "csv" Then

            If currentFile.DateLastModified > maxDate Then
                 maxDate = currentFile.DateLastModified
                 maxFileName = currentFile.Name
            End If

        End If

    Next currentFile

   Msgbox maxFileName

End Sub

Additional references:

1) How to get the last modified file in a directory using VBA in Excel 2010

2) Using VBA FileSystemObject, specific file File extension

3) File system object explained

4) msoFileDialogFolderPicker

Version 2 Using FileDialog to get folderpath for GetFolder:

Option Explicit

Public Sub GetLastModifiedCSV()

    Const folderPath As String = "C:\Users\User\Desktop\Test"

   'Early binding code. Requires reference to MS Scripting Runtime
    Dim fso As FileSystemObject
    Set fso = New FileSystemObject

   'Late binding code. To be used instead of two lines above if "user-defined type not defined" /No reference added. You would uncomment line below.

  'Dim fso As Object: Set fso = CreateObject("FileSystemObject")

    Dim myFolder As Object
    Dim currentFile As Object
    Dim maxFileName As String
    Dim maxDate As Date

    With Application.FileDialog(msoFileDialogFolderPicker)

    If .Show Then
      Set myFolder = fso.GetFolder(.SelectedItems(1))  ' & "\" 
    Else
        Exit Sub
    End If

    End With

    For Each currentFile In myFolder.Files

        If fso.GetExtensionName(currentFile) = "csv" Then

            If currentFile.DateLastModified > maxDate Then
                 maxDate = currentFile.DateLastModified
                 maxFileName = currentFile.Name
            End If

        End If

    Next currentFile

    'MsgBox maxFileName

    Workbooks.Open fso.BuildPath(myFolder, maxFileName) 

End Sub