How to import sheets from the most recent Excel file in a folder?

Question

The code imports all the worksheets on all the Excel files on my folder.

I added a command button as suggested in the website where I got this code. In the long run I would like to apply the data imported to a table I have on the main worksheet, followed by printing the template and then deleting the information so I can start over with the next recent spreadsheet.

For now I only want to know how to import the most recent file to my worksheet.

Private Sub CommandButton1_Click()

Dim directory As String, fileName As String, sheet As Worksheet, total As Integer

directory = "C:\ExcelPract\"
fileName = Dir(directory & "*.xl??")

Do While fileName <> ""
    Workbooks.Open (directory & fileName)
        
    For Each sheet In Workbooks(fileName).Worksheets
        total = Workbooks("Docket .xls").Worksheets.count
        Workbooks(fileName).Worksheets(sheet.Name).Copy _
        after:=Workbooks("Docket .xls").Worksheets(total)
    Next sheet
        
    Workbooks(fileName).Close
    fileName = Dir()
Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

This may help you in finding latest file. – skkakkar Mar 12 '19 at 06:56 — skkakkar, Mar 12 '19 at 06:56

score 0 · Answer 1 · answered Mar 12 '19 at 07:07

You could use the below code and call the NewestFile function from the CommandButton1_Click(). I have only replaced the below line in your Sub.

fileName = NewestFile(directory, "*.xls")

Function NewestFile(directory, FileSpec)
' Returns the name of the most recent file in a Directory
' That matches the FileSpec (e.g., "*.xls").
' Returns an empty string if the directory does not exist or
' it contains no matching files
    Dim fileName As String
    Dim MostRecentFile As String
    Dim MostRecentDate As Date
    If Right(directory, 1) <> "\" Then directory = directory & "\"

    fileName = Dir(directory & FileSpec, 0)
    If fileName <> "" Then
        MostRecentFile = fileName
        MostRecentDate = FileDateTime(directory & fileName)
        Do While fileName <> ""
            If FileDateTime(directory & fileName) > MostRecentDate Then
                 MostRecentFile = fileName
                 MostRecentDate = FileDateTime(directory & fileName)
             End If
             fileName = Dir
        Loop
    End If
    NewestFile = MostRecentFile
End Function


Private Sub CommandButton1_Click()

Dim directory As String, fileName As String, sheet As Worksheet, total As Integer

directory = "C:\ExcelPract\"
fileName = NewestFile(directory, "*.xls")


Do While fileName <> ""
    Workbooks.Open (directory & fileName)

    For Each sheet In Workbooks(fileName).Worksheets
        total = Workbooks("Docket .xls").Worksheets.Count
        Workbooks(fileName).Worksheets(sheet.Name).Copy _
        after:=Workbooks("Docket .xls").Worksheets(total)
    Next sheet

    Workbooks(fileName).Close
    fileName = Dir()
Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

How to import sheets from the most recent Excel file in a folder?

1 Answers1