I want to copy data from various excel files that are in a folder to a master file and with the code i have i am able to do so.However ,I'm facing 2 issues when with the same.
1: Since the macro runs every time the master file is opened,the data from the latest files gets updated again(If it is updated before)
EG: I have file1.xlsx and file2.xlsx in a folder.I want to copy data from these files and paste them in my masterfile.xlsm.I open the masterfile.xlsm and the macro runs and does the job.Now when i add file3.xlsx in the folder and open masterfile.xlsm the data from file3.xlsx get's copied and pasted in the masterfile. BUT,when i open the masterfile for the second time and by assuming file4.xlsx has not been added yet to the folder , the macro runs and copies data from file3.xlsx AGAIN since it is the latest file in the folder. (Code I used is given below)
Sub up()
Dim MyFile As String, myrecfile As String, mymostrecfile As String
Dim recdate As Date
Dim mydir As String
mydir = "C:\Users\National\Desktop\TEST Codes\PO\Excel\"
Dim fileext As String
fileext = "*.xls"
If Right(mydir, 1) <> "\" Then mydir = mydir & "\"
MyFile = Dir(mydir & "*.xls")
If MyFile <> "" Then
myrecfile = MyFile
recdate = FileDateTime(mydir & MyFile)
Do While MyFile <> ""
If FileDateTime(mydir & MyFile) > recdate Then
myrecfile = MyFile
recdate = FileDateTime(mydir & MyFile)
End If
MyFile = Dir
Loop
End If
mymostrecfile = myrecfile
If mymostrecfile = "" Then
Exit Sub
Else
'do stuff
Application.ScreenUpdating = False
Workbooks.Open Filename:=mydir & mymostrecfile, ReadOnly:=True
Sheets("DETAILED").Range("A3:S15").Copy
Application.DisplayAlerts = False
ActiveWorkbook.Close
Dim LastRow As Long, WS As Worksheet, LS As Long
Set WS = Workbooks("Z master for PO").Worksheets("sheet1")
LastRow = WS.Range("R" & Rows.Count).End(xlUp).Row + 1
WS.Activate
WS.Range("A" & LastRow).Select
ActiveSheet.Paste Destination:=WS.Range("A" & LastRow)
Application.DisplayAlerts = False
ActiveWorkbook.Save
Application.DisplayAlerts = True
Application.CutCopyMode = False
End If
End Sub
2:If the files fit the time condition in the code they are updated. But , in case the masterfile is updated after quiet a while such files miss to satisfy the time condition and the data from these files doesn't update.
EG: Because of the 1st issue i tried https://stackoverflow.com/a/56749203/9124181 (Thanks to @Tim Stack for that) However , I faced another issue. In the code sent to me by Tim i was able to achieve what i wanted but what if I open the masterfile say after an hour? or in the evening? I might have to check and update the masterfile anytime but, only the data from files "accessed less than 5 minutes ago" will get updated.Now i could always change the "time last accessed" to 10 minutes or any number of my choice.However, the time of saving the files in the folder and the time of updating the master file is variable.The master file could be updated within a minute of saving the excel file or it may not be updated for hours even.In such a case the 5/10/15 minute time constraint wont work. (Please check the link for the code)
The only way i can think of right now is to copy data from only those excel files which haven't been updated/opened/modified after their initial save.
How do i fix this? Is there a better way?