VBA code to identify latest file in folder and copy contents to another workbook

[edwardj3]

Hi. Hope someone can assist with the following.

We have a shared folder where csv files are dropped on monthly basis. I need to identify the latest files with name File1 and File2. The file name changes each month as it ends with the month it is produced e.g. File1Feb18, File1Mar18, File2Feb18, File2Mar18 so this is why I need to identify the files using part of the name and the date modified. I've found this code which identifies the files I need but on returns the full file name and date it was created/modified. How can I adapt it so it copies the data in range A1:C100 for both File1 and File2 and pastes into the workbook e.g. Combined_Report from which the VBA code is going to be run e.g. File1 data into sheet 1 col A:C of Combined_Report and File2 data into sheet 1 col E:G of Combined_Report

Sub LatestFileWithName()
 
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
 
‘ Specify the folder….
strPath = “M:\Shared_Folder\Folder_where_csv_files_located” 
 
‘ Specify the word in the file name
strFind = “File1”
 
 
 
‘ Use Microdoft Scripting runtime
Set objFSO = CreateObject(“Scripting.FileSystemObject”)
Set objFolder = objFSO.GetFolder(strPath)
 
‘Check data on each file in the 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
 
‘ Display file name in message box - I would not need this
If Len(strName) = 0 Then
strName = “None found”
Else
strName = strName & “ – is the latest file – “ & varDate
End If
MsgBox strName, , “ Latest file”
 
Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing
 
End Sub
[\code]


My VBA knowledge is very limited so I'd be grateful for detailed notes/explanation with any solution put forward.  Thanks