VBA: Open Latest File in Folder and Copy between files

[jjobrien03]

I am looking to open the most recent file in a certain directory, copy certain ranges from that file into specific ranges of another file (the workbook from which I am executing the code) then close out the workbook which I had previously opened. I have the code to open the most recent workbook, but I can't figure out how to copy from that workbook to the workbook from which I am running the code. Any help would be appreciated.

Summary:
Open most recent file
Copy information (from multiple sheets) from that file to other workbook (where I am executing code)
Close opened workbook

The code I have so far, which works perfectly is:

Option Explicit
Sub reportpackage()


    Dim MyPath As String
    Dim MyFile As String
    Dim LatestFile As String
    Dim LatestDate As Date
    Dim LMD As Date
   


    MyPath = "C:\Users\Me\Desktop\Report Packages"
    
  
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
    


    MyFile = Dir(MyPath & "*.xls", vbNormal)
    


    If Len(MyFile) = 0 Then
        MsgBox "No files were found...", vbExclamation
        Exit Sub
    End If


    Do While Len(MyFile) > 0
    
     
        LMD = FileDateTime(MyPath & MyFile)
        
  
        If LMD > LatestDate Then
            LatestFile = MyFile
            LatestDate = LMD
        End If
       
        MyFile = Dir
        
    Loop


    Workbooks.Open MyPath & LatestFile
    
   


End Sub

 

[John_w]

The ThisWorkbook object references the workbook in which the code is executing. After the Workbooks.Open line, the ActiveWorkbook object references the opened workbook. Therefore to copy from the latter to the former you could use code like this:

    ThisWorkbook.Worksheets("Sheet name").Range("A1").Value = ActiveWorkbook.Worksheets("Name of sheet here").Range("A1").Value  'reference sheets by name
    ThisWorkbook.Worksheets(1).Range("A1").Value = ActiveWorkbook.Worksheets(1).Range("A1").Value    'reference sheets by index
    ActiveWorkbook.Close

You can get more specific code by recording a macro whilst copying between the two workbooks and modifying it to use the ThisWorkbook and ActiveWorkbook objects, though the generated code will use the Copy and Paste methods instead of the Value assignment as shown above.

 

[jjobrien03]

Sorry, see revised post. I anticipated needing to clarify. The workbooks that I need to open and copy/paste from/to will never have the same names and, therefore, I can't reference them with a simple "Name".

 

[John_w]

My code doesn't use workbook names, only ActiveWorkbook and ThisWorkbook.

 

[Pinaki Chatterjee]

I am looking to open the most recent file in a certain directory, copy certain ranges from that file into specific ranges of another file (the workbook from which I am executing the code) then close out the workbook which I had previously opened. I have the code to open the most recent workbook, but I can't figure out how to copy from that workbook to the workbook from which I am running the code. Any help would be appreciated.

Summary:
Open most recent file
Copy information (from multiple sheets) from that file to other workbook (where I am executing code)
Close opened workbook

The code I have so far, which works perfectly is:

Option Explicit
Sub reportpackage()


    Dim MyPath As String
    Dim MyFile As String
    Dim LatestFile As String
    Dim LatestDate As Date
    Dim LMD As Date
  


    MyPath = "C:\Users\Me\Desktop\Report Packages"
   
 
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
   


    MyFile = Dir(MyPath & "*.xls", vbNormal)
   


    If Len(MyFile) = 0 Then
        MsgBox "No files were found...", vbExclamation
        Exit Sub
    End If


    Do While Len(MyFile) > 0
   
    
        LMD = FileDateTime(MyPath & MyFile)
       
 
        If LMD > LatestDate Then
            LatestFile = MyFile
            LatestDate = LMD
        End If
      
        MyFile = Dir
       
    Loop


    Workbooks.Open MyPath & LatestFile
   
  


End Sub

Hi , I have some change required, how can open latest two files and copy the tables one by one in active sheet and form one table. Highly appreciate, the help. Thanks & Regards, Pinaki Chatterjee

 

[John_w]

This code displays the latest two files in a folder:

Public Sub Two_Latest_Files()

    Dim folderPath As String
    Dim fileNames As Variant
    
    folderPath = "C:\your\folder\path\"  'CHANGE THIS
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"
    fileNames = Split(CreateObject("WScript.Shell").Exec("cmd /c DIR /B /A-D /O-D """ & folderPath & "*.*" & """").StdOut.ReadAll, vbCrLf)
    MsgBox "The two latest files in " & folderPath & " are " & fileNames(0) & " and " & fileNames(1)
    
End Sub

Please start a new thread with your whole question because the second part of your question is unrelated to the subject of this thread.

 

[Pinaki Chatterjee]

Hi John, This is great but how to open this two files one by one and copy to an active workbook where the command button is there in two sheets name " Data1" and "Data2". Thanks and regards, Pinaki Chatterjee

 

[Pinaki Chatterjee]

Hi,My Challenge is to latest two sets of data and populate in this workbook and combine them. Thanks & Regards, Pinaki Chatterjee