Copy latest .csv with wildcard for file name

[kendo679]

Hello there,

I'm new to VBA, I've got a book on the subject en route from amazon, and up until now I've been able to pull off what I need by searching the web and clipping parts of other folk's examples.

The code below does exactly what I need, but it takes a LOOOONG time...
Im searching for the two latest .csv's in a folder containing thousands of files, they have "-L" and "-R" in the file name, repectively. The file is copied into the destination folder, renamed, opened, closed, and deleted.

All of this is to refresh the data in the master sheet from which the sub is ran on a daily basis. Other than the -L and -R, the file names vary greatly depending on the person generating the .csv files in the source folder.

I would greatly appreciate any advice on how I could improve my process or maybe point me in the right direction when I begin studying a little deeper.

Sub LatestFileWithName()
'Jim Cone - San Francisco, USA - July, 2006
'Displays the latest file name in the strPath folder,
' if the file name contains a specified word.

Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim strPath As String
Dim strName As String
Dim varDate As Variant
Dim strFind As String
Dim sTarget As String

' Specify the folder...
strPath = "\\FILPRTSRV01\Data\Shared\Quality\CMM Data\MSQC CMM Data\59B Fuel Pump\Revo 3\res\"
' Specify the word in the file name...
strFind = "-L"
sTarget = "C:\Users\KennyS\Desktop\59B FP\"

' Use Microsoft Scripting runtime.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPath)

' Check date on each file in folder.
For Each objFile In objFolder.Files
If InStr(1, objFile.Name, strFind, vbTextCompare) Then
If objFile.DateLastModified > varDate Then
strName = objFile.Name
varDate = objFile.DateLastModified
End If
End If
Next 'objFile

FileCopy strPath & strName, sTarget & strName


strFind = "-R"
' Use Microsoft Scripting runtime.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPath)

' Check date on each file in folder.
For Each objFile In objFolder.Files
If InStr(1, objFile.Name, strFind, vbTextCompare) Then
If objFile.DateLastModified > varDate Then
strName = objFile.Name
varDate = objFile.DateLastModified
End If
End If
Next 'objFile

FileCopy strPath & strName, sTarget & strName
          
'SET VARIABLES TO RENAME DATA FILES
    sSearch = "C:\Users\KennyS\Desktop\59B FP\"
    sTarget = "C:\Users\KennyS\Desktop\59B FP\"
    
    sKey = "-L"
    sFilename = Dir(sSearch & "*" & sKey & "*" & ".CSV")
                FileCopy sSearch & sFilename, sTarget & "FPL.CSV"
                sFilename = Dir()
    

    sKey = "-R"
    sFilename = Dir(sSearch & "*" & sKey & "*" & ".CSV")
                FileCopy sSearch & sFilename, sTarget & "FPR.CSV"
                sFilename = Dir()
        
'OPEN DATA FILES
    Workbooks.Open ThisWorkbook.path & "\" & "FPL.CSV"
    Workbooks.Open ThisWorkbook.path & "\" & "FPR.CSV"
    ThisWorkbook.Activate

   
'CLOSE DATA FILES
    Dim Wb As Workbook
    For Each Wb In Application.Workbooks
        If StrComp(Right(Wb.Name, 4), ".CSV", vbTextCompare) = 0 Then
            Wb.Close
        End If
    Next Wb
    
'DELETE CSV FILES
    'Kill "C:\Users\KennyS\Desktop\59B FP\*.csv"



Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing
End Sub

 

[John_w]

Welcome to MrExcel forums.

Instead of the FileSystemObject loops, we can use the DOS DIR command to find the latest file. Replace the FileSystemObject code with:

    strName = Get_Latest_File(strPath & "*-L*.csv")
    FileCopy strPath & strName, sTarget & strName
    
    strName = Get_Latest_File(strPath & "*-R*.csv")
    FileCopy strPath & strName, sTarget & strName

and add this function:

Private Function Get_Latest_File(fileSpec As String) As String
    Get_Latest_File = Split(CreateObject("WScript.Shell").Exec("cmd /c DIR /B /O-D """ & fileSpec & """").StdOut.ReadAll, vbCrLf)(0)
End Function

 

[kendo679]

That worked perfect, Almost instantly, and much cleaner code now
Thank you John_w
That DOS trick is something I probably wouldn't have learned for quite a while

 

[kendo679]

If anyone has other ideas besides the DOS trick, I'd be happy to hear them.
Don't get me wrong, John_w, I'm grateful for the solution you provided. I'd just like to know if there's other ways of coding this that, while perhaps slower or sloppier than your method, would be a better start for my learning process. The DOS trick seems like I jumped to the back of the book, so to say...

I've got dozens of these workbooks to create and when I do, I like to go back a couple months and build up some data to get trend charts jump started. So, I was hoping to build upon any suggestions I receive to add more capability. Such as... instead of the latest file, maybe the last file created on a specific date entered in a cell... or sometimes there are multiple search directories and I need to filter down to the latest file between 3 different locations. (I've imagined how to pull that off with your method John, but it adds up to a lot of DOS windows popping up)

I'm not formally trained in excel or obviously, VBA but I have taught myself to do some tricky stuff with both that helps me greatly in my profession. I'm a machinist by trade and I program CNC machines to cut aluminum parts for the automotive industry. The data I'm handling is output from a measuring device that probes the finished parts and I need to feed that back to the CNC when they make bad parts and need to be adjusted.

I imagine there's a better way to pull off a lot of what I've figured out over the years on my own... I just started learning to create charts a few weeks ago for crying out loud! So thanks again, John_w, and also to anyone else who'd like to lend some advise.