VBA: Open Latest File in Folder and Copy between files

jjobrien03

New Member
Joined
Sep 2, 2014
Messages
41
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:

Code:
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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:
Code:
    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.
 
Upvote 0
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".
 
Upvote 0
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:

Code:
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
 
Upvote 0
This code displays the latest two files in a folder:

VBA Code:
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.
 
Upvote 0
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
 
Upvote 0
Hi,My Challenge is to latest two sets of data and populate in this workbook and combine them. Thanks & Regards, Pinaki Chatterjee
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top