Import CSV into current sheet by finding latest version in network share.

[DiscoSte]

Hi

Would anybody be able to assist? I'm hoping it's relatively simple, but I'm new to this aspect.

I am trying to run a macro to automatically clear the current sheet, then import the data from the latest csv in a folder, where the filename is the date. The naming format is: \\server\folder\dd-MM-yyyy.csv

My current attempt leaves me with 'Subscript out of range errors' and other problems, and they're butchered from samples by people attempting to do things much more complex than I am, and I don't yet know enough to work out what I've not added/omitted.

Any help would be greatly appreciated.

Many Thanks

 

[John_w]

Welcome to MrExcel forums.

Post your code and tell us which line causes the error if you want help with it. However, see if this macro works for you.

Public Sub Import_Newest_CSV_File()

    Dim matchFileSpec As String
    Dim newestFile As String
    
    matchFileSpec = "\\server\folder\##-##-####.csv"
    
    newestFile = Find_Newest_File(matchFileSpec)
    
    If newestFile <> "" Then        
        With ActiveSheet
            .Cells.ClearContents
            Workbooks.Open Filename:=newestFile
            ActiveWorkbook.Worksheets(1).UsedRange.Copy .Range("A1")
            ActiveWorkbook.Close False
        End With        
    Else    
        MsgBox "No files found which match " & matchFileSpec        
    End If

End Sub


Private Function Find_Newest_File(fileNameLike As String) As String

    Dim p As Long
    Dim file As String
    Dim thisFileDate As Date, newestDate As Date
    
    Find_Newest_File = ""
    newestDate = 0
    
    p = InStrRev(fileNameLike, "\")
    file = Dir(Replace(fileNameLike, "#", "?"))
    While file <> vbNullString
        If file Like Mid(fileNameLike, p + 1) Then
            thisFileDate = DateSerial(Mid(file, 1, 2), Mid(file, 4, 2), Mid(file, 7, 4))
            If thisFileDate > newestDate Then
                Find_Newest_File = Left(fileNameLike, p) & file
                newestDate = thisFileDate
            End If
        End If
        file = Dir
    Wend
    
End Function

 

[DiscoSte]

Thank you for the reply John, very much appreciated. That worked as I hoped! The only issue I have is the date aspect. For example, it will designate 20-02-2020 as newer than 19-02-2021. I'm a little unsure as to how to rectify that.

 

[John_w]

Sorry, there's a bug because I didn't check the DateSerial arguments. Change the line to:

            thisFileDate = DateSerial(Mid(file, 7, 4), Mid(file, 4, 2), Mid(file, 1, 2))

 

[DiscoSte]

John. That's perfect. Thank you very much for your help. Much appreciated!