Import Sheet from Most Recent File in a Folder

[David04Ruiz]

Hi all, I wanted to see if there was a way to import a sheet into my workbook from the most recent file in a specified folder? I know how to import a closed workbook based off of the file name but I wanted to import it based off the file creation date if possible. I want to import the first sheet of the closed most recent workbook. Super new to VBA so if anyone has a recommendation of how to do this id very much appreciate it!

 

[Joe4]

See if this does what you want: VBA To Open Latest File in Folder

 

[David04Ruiz]

See if this does what you want: VBA To Open Latest File in Folder

I believe this code only opens the latest file in the selected folder but what I need is for it to get imported into my current workbook. If that is possible that would be great. Thank you!

 

[Joe4]

You would put that VBA code in the workbook that you want to copy the data to.
Once opened, you copy/paste what you need, then close the workbook.
You can get a lot of the code you need for that by using the Macro Recorder, performing those tasks manually, then closing the workbook.

There are a bunch of threads out there which already do stuff like this, such as this one:

Macro : Open Latest File in Folder and Copy Paste Below Last Cell

How can i combine these two parts of code in Excel VBA? Part 1: Sub Copypastelastrow() Dim MyPath As String Dim MyFile As String Dim LatestFile As String Dim LatestDate As Date ...

stackoverflow.com

They would just require some minor edits to suit your conditions (i.e. sheet names and ranges to copy).

 

[David04Ruiz]

You would put that VBA code in the workbook that you want to copy the data to.
Once opened, you copy/paste what you need, then close the workbook.
You can get a lot of the code you need for that by using the Macro Recorder, performing those tasks manually, then closing the workbook.

There are a bunch of threads out there which already do stuff like this, such as this one:

Macro : Open Latest File in Folder and Copy Paste Below Last Cell

How can i combine these two parts of code in Excel VBA? Part 1: Sub Copypastelastrow() Dim MyPath As String Dim MyFile As String Dim LatestFile As String Dim LatestDate As Date ...

stackoverflow.com

They would just require some minor edits to suit your conditions (i.e. sheet names and ranges to copy).

Yeah I've been having trouble coding it to open, extract from, and close the sheet that it opens.

I found this code below on Stack Overflow that imports EVERY sheet from the most recent file into my workbook. Would you happen to know of how to adjust it so that only the first sheet (or "Sheet1") gets imported into my workbook?

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

 

[Joe4]

You will need to remove the loop which goes through all the sheets:

    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

and replace it with just the one copy command:

        Workbooks(fileName).Worksheets(sheet.Name).Copy _
            after:=Workbooks("Docket .xls").Worksheets(total)

substituting in the names that pertain to your workbooks/worksheets.

 

[David04Ruiz]

You will need to remove the loop which goes through all the sheets:

    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

and replace it with just the one copy command:

        Workbooks(fileName).Worksheets(sheet.Name).Copy _
            after:=Workbooks("Docket .xls").Worksheets(total)

substituting in the names that pertain to your workbooks/worksheets.

That works thanks! It pulls in the correct workbook sheet, the only issue is the following error message shows every time I run it:

 

[Joe4]

I think "Filename" is a reserved word in Excel VBA (meaning it is already used for some system setting).
Using reserved words as variables can cause errors and unexpected results, as VBA may not be able to determine if you are calling your variable, or the built-in reference).

Try changing all your references of "fileName" to something like "fName", and see if that clears it up.

 

[Alex Blakenburg]

That works thanks! It pulls in the correct workbook sheet, the only issue is the following error message shows every time I run it:

The Dir is the second part of a pair of statements and you are doing all that in the function.
The function has already returned the File you want so your main routine does not need any of the looping.

Remove the struck out lines.

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

        Workbooks(fileName).Worksheets("Template").Copy _
            after:=Workbooks("Book1.xlsm").Worksheets("Sheet1")

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

PS: An example of the pair of Dir statements are in the function

fileName = Dir(directory & FileSpec, 0)
' This next line won't work without the previous line
fileName = Dir

 

[Joe4]

The Dir is the second part of a pair of statements and you are doing all that in the function.
The function has already returned the File you want so your main routine does not need any of the looping.

Remove the struck out lines.

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

        Workbooks(fileName).Worksheets("Template").Copy _
            after:=Workbooks("Book1.xlsm").Worksheets("Sheet1")

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

PS: An example of the pair of Dir statements are in the function

fileName = Dir(directory & FileSpec, 0)
' This next line won't work without the previous line
fileName = Dir

Ah yes, this morning I forgot that we were just dealing with one file again (sometimes I get lost amongst the various threads when I help on a whole bunch and they spill over into multiple days).
Time for my morning caffeine jolt!