Open latest file and copy data to different workbook

Question

Im completely new to VBA. I'm trying to write macro what open latest file from folder and copy and past data from specific sheet. I need to copy data from file opened by VBA (latest file from folder) and copy data from one sheet to my current file (Expiry date sheet).

I don't know how to declare open file as workbook from where I want to copy data. Any advice?

Private Sub CommandButton1_Click()

'Declare the variables
Dim MyPath As String
Dim MyFile As String
Dim LatestFile As String
Dim LatestDate As Date
Dim LMD As Date


Set Y = Workbooks("TEST")


MyPath = "C:\Users\e9\Desktop\Automatyczne sprawdzanie expiry date\New folder\"


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

score 0 · Answer 1 · answered Aug 12 '14 at 17:16

Once the latest file is opened,

      Sheets("x").Activate
      ActiveSheet.Range("x:y").select
      selection.copy
      workbooks("x").activate
      sheets("X").activate
      activesheet.range("x").select
      selection.paste

Replace the xs and ys with desired names/ranges. afterward continue your loop

score 0 · Accepted Answer · edited May 23 '17 at 12:03

Below is code sample taken from various references ..

Find Last modified file You have to add reference for the FileSystemObject .. FileSystemObject how to add reference

Tested by adding a ActiveX button to worksheet on destination file (Book2.xlsm). Change the path and also "Book2.xlsm" to your path and filename.

    Dim sFldr As String
Dim fso As Scripting.FileSystemObject
Dim fsoFile As Scripting.File
Dim fsoFldr As Scripting.Folder
Dim dtNew As Date, sNew As String
Dim sFileName As String

Set fso = New Scripting.FileSystemObject

sFldr = "C:\Temp\stackoverflow\excel\"

Set fsoFldr = fso.GetFolder(sFldr)

For Each fsoFile In fsoFldr.Files
    If fsoFile.DateLastModified > dtNew Then
        sNew = fsoFile.Path
        sFileName = fsoFile.Name
        dtNew = fsoFile.DateLastModified
    End If
Next fsoFile

Workbooks.Open Filename:=sNew
Sheets("Sheet1").Copy Before:=Workbooks("Book2.xlsm").Sheets(1)
Windows(sFileName).Activate
ActiveWindow.Close

Open latest file and copy data to different workbook

2 Answers2