How to copy data from only the new excel files that are saved in a predefined folder?

Question

I want to copy specific range from excel files stored in a specific folder and paste it in another excel file.I am able to do so.However,every time i run the code it starts with the very first file in the folder.I want to copy data from only the files that haven't been updated before.Is there a way to do that? EG:

"file1.xlsx" and "file2.xlsx" are in a folder. I want to copy data from the given files and paste it in "NewFile.xlsm" (I'm able to achieve this) However, if I add "file3.xlsx" and "file4.xlsx" in the folder and then run the macro, it copies data from "file1.xlsx" and "file2.xlsx" as well.I want it to copy data only from "file3.xlsx" and "file4.xlsx" this time as the data from previous 2 files is already saved. (The code i have is given below)

Path = "C:\Users\National\Desktop\TEST Codes\PO\Excel\"
Filename = Dir(Path & "*.xls")
Do While Filename <> ""

If Filename = "Z master for PO.xlsm" Then
Exit Sub
End If
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True

Sheets("DETAILED").Range("A3:S15").Copy

Application.DisplayAlerts = False
Application.ScreenUpdating = False

ActiveWorkbook.Close



Dim LASTROW As Long, WS As Worksheet, LS As Long
Set WS = Sheets("sheet1")
LASTROW = WS.Range("R" & Rows.Count).End(xlUp).Row + 1

WS.Range("A" & LASTROW).Select

ActiveSheet.Paste Destination:=WS.Range("A" & LASTROW)

Application.CutCopyMode = False

Filename = Dir()
Loop

Range("A7").Select

Tim Stack Tim Stack · Accepted Answer · 2019-06-25T07:47:20

One way of doing this is by looking at the DateLastAccessed property, or the DateLastModified property. These are both properties of the File object, see this MS documentation.

You can set a minimum date/time, which should exclude the files you don't want processed.

Be sure to set the correct reference

Option Explicit

Sub GoThroughFiles()

Dim Path As String, Filename As String,
Dim fso, fileinfo

Set fso = CreateObject("Scripting.FileSystemObject")
Path = "C:\Users\National\Desktop\TEST Codes\PO\Excel\"
Filename = Dir(Path & "*.xls")

Set fileinfo = fso.GetFile(Path & Filename)

Do While Len(Filename) > 0 
    If fileinfo.DateLastAccessed > DateAdd("n", -5, Now) 'If the file was last accessed less than 5 minutes ago
        'Do stuff with the file
    End If
    FileName = Dir()
Loop

End Sub

Furthermore, avoid using Select and Activate as using both will make your code prone to errors. Here is a thread on how to avoid it. Next to that, I added Option Explicit which makes sure you avoid other errors caused by, for example, spelling mistakes.

How to copy data from only the new excel files that are saved in a predefined folder?

1 Answers