0

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?

Pawel Czyz
  • 1,651
  • 4
  • 17
  • 21
Sw1tch
  • 81
  • 1
  • 9
  • Each time you pull data from a file, store its full path on a worksheet (could be a hidden sheet), along with its "last updated" time. Then you can avoid pulling from any file which hasn't been updated since the last run. – Tim Williams Jun 29 '19 at 04:03
  • @Tim_Williams Could you please elaborate? I am sorry I did not understand how to do it. (Would be great if you'd share the code) – Sw1tch Jun 29 '19 at 14:21
  • 2
    The way I usually deal with this is to move the file once it's been imported, placing it in an `archives` subfolder. That way it's not able to be accidentally imported again, but remains available if I need to troubleshoot issues with it. – Ken White Jun 29 '19 at 14:32

0 Answers0