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

edwardj3

New Member
Joined
Jan 16, 2018
Messages
28
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

Code:
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
 
Last edited:

Forum statistics

Threads
1,081,523
Messages
5,359,263
Members
400,523
Latest member
ExcelNewbie98

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top