Import sheets from the most recent excel file in a folder? first time using VBA

Question

I spent all day trying to understand VBA and I tried so many different websites to find the right code, but i just cant make it work. The code i am using right now imports all the worksheets on all the excel files I have on my folder. I only need to import the most recent one. I added a command button with it just as suggested in the website where i got this code. In the long run i would like to be able to apply the data imported to a table i already have on the main worksheet, following by printing the template and then deleting the information so i can start over with the next recent spreadsheet. BUT for now i only want to know how to get ONLY the most recent file imported 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

Import sheets from the most recent excel file in a folder? first time using VBA

1 Answers1