2

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
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Sw1tch
  • 81
  • 1
  • 9

1 Answers1

0

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.

Tim Stack
  • 3,209
  • 3
  • 18
  • 39
  • Glad to have helped! If this answer was useful, please don't forget to accept it – Tim Stack Jun 27 '19 at 08:22
  • @Tim_Stack What if file2 was saved yesterday , file3 was saved this morning and file4 was saved 2 mins ago.Now when i run the macro it'll update only file4 (given that it updates files accessed less than 5 mins ago) Assuming i never ran the macro yesterday or this morning ,even though file2 and file3 are new in the folder their data wont be updated in the masterfile. Simply put ,the time interval between saving the file in a folder and updating the mastersheet could vary.In such a case i wont be able to put the "If the file was last accessed less than 5 minutes ago" thingy. How to fix this? – Sw1tch Jun 28 '19 at 15:05