Open Latest File in a folder

investinthebest

New Member
Joined
Jun 7, 2006
Messages
18
This is what I'm trying to accomplish:
open latest file in a folder, copy the contents to a "master file" on a new tab, close the latest file, then find another latest file from a different folder, wash and repeat the above about 10 more times.

This will take a bunch of separate spreadsheets in different locations and put them all into one nice "master file" for me. I found some code that works great for one file, but I can't get the 2nd (and so on) files to work. The macro just pulls in the first file and stops, no errors, but doesn't give me additional tabs either. Below is the code I'm using - one can see I just copied and pasted the code a second time for "variable 2" but changed the variable names and location of the file. Can anyone please help me?

Sub OpenLatestFile()
'Variable 1
'Declare the variables
Dim MyPath As String
Dim MyFile As String
Dim LatestFile As String
Dim LatestDate As Date
Dim LMD As Date

'Specify the path to the folder
MyPath = "C:\Users\abc\Desktop\Excel Test2\"

'Make sure that the path ends in a backslash
If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"

'Get the first Excel file from the folder
MyFile = Dir(MyPath & "*.xlsx", vbNormal)

'If no files were found, exit the sub
If Len(MyFile) = 0 Then
MsgBox "No files were found...", vbExclamation
Exit Sub
End If

'Loop through each Excel file in the folder
Do While Len(MyFile) > 0

'Assign the date/time of the current file to a variable
LMD = FileDateTime(MyPath & MyFile)

'If the date/time of the current file is greater than the latest
'recorded date, assign its filename and date/time to variables
If LMD > LatestDate Then
LatestFile = MyFile
LatestDate = LMD
End If

'Get the next Excel file from the folder
MyFile = Dir

Loop

'Open the latest file
Workbooks.Open MyPath & LatestFile

'Move that tab to the Master File
ActiveSheet.Copy Before:=Workbooks( _
"latestFileMacroTest.xlsm").Sheets(1)

'Close the file
ActiveWorkbook.Close False


'Variable 2
'Declare the variables
Dim MyPath1 As String
Dim MyFile1 As String
Dim LatestFile1 As String
Dim LatestDate1 As Date
Dim LMD1 As Date

'Specify the path to the folder
MyPath1 = "C:\Users\abc\Desktop\Excel Test\"

'Make sure that the path ends in a backslash
If Right(MyPath1, 1) <> "\" Then MyPath1 = MyPath1 & "\"

'Get the first Excel file from the folder
MyFile1 = Dir(MyPath1 & "*.xlsx", vbNormal)

'If no files were found, exit the sub
If Len(MyFile1) = 0 Then
MsgBox "No files were found...", vbExclamation
Exit Sub
End If

'Loop through each Excel file in the folder
Do While Len(MyFile1) > 0

'Assign the date/time of the current file to a variable
LMD1 = FileDateTime(MyPath1 & MyFile1)

'If the date/time of the current file is greater than the latest
'recorded date, assign its filename and date/time to variables
If LMD1 > LatestDate1 Then
LatestFile1 = MyFile1
LatestDate1 = LMD1
End If

'Get the next Excel file from the folder
MyFile1 = Dir

Loop

'Open the latest file
Workbooks.Open MyPath1 & LatestFile1

'Move that tab to the Master File
ActiveSheet.Copy Before:=Workbooks( _
"latestFileMacroTest.xlsm").Sheets(1)

'Close the file
ActiveWorkbook.Close False

End Sub
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
I am using Excel 2007 on a PC. What the master file (where I'd be running the macro from) would look like, would be a separate tab for each xls file found in each folder.
 
Upvote 0
Hi investinthebest,

Your code was having problems as the master workbook was the ActiveWorkbook when you closed the ActiveWorkbook so with the file containing the macro closed the code could not continue. Below is a more efficient structure as it loops through all the directories (rather than repeating the code), explicitly references the workbooks and is scalable.

In your master workbook add a Sheet called "File_Locations". On this sheet from A1 down put in the directories where you want to get the latest files, then use the following Code.


Code:
Sub OpenLatestFile()
'Variable 1
'Declare the variables
Dim MyPath As Range
Dim MyFile As String
Dim LatestFile As String
Dim LatestDate As Date
Dim LMD As Date
Dim CurrentWorkBook As Workbook


'Specify the path to the folder
Set MyPath = Sheets("File_Locations").Range("A1")
Do While MyPath.Value <> ""

    LatestDate = 0
    'Make sure that the path ends in a backslash
    If Right(MyPath.Value, 1) <> "\" Then MyPath.Value = MyPath.Value & "\"
    
    'Get the first Excel file from the folder
    MyFile = Dir(MyPath.Value & "*.xls*", vbNormal)
    
    'If no files were found, exit the sub
    If Len(MyFile) = 0 Then
        MsgBox "No files were found...", vbExclamation
        Exit Sub
    End If
    
    'Loop through each Excel file in the folder
    Do While Len(MyFile) > 0
    
        'Assign the date/time of the current file to a variable
        LMD = FileDateTime(MyPath.Value & MyFile)
        
        'If the date/time of the current file is greater than the latest
        'recorded date, assign its filename and date/time to variables
        If LMD > LatestDate Then
            LatestFile = MyFile
            LatestDate = LMD
        End If
        
        'Get the next Excel file from the folder
        MyFile = Dir
    Loop
    
    'Open the latest file
    Set CurrentWorkBook = Workbooks.Open(MyPath & LatestFile)
    
    'Move that tab to the Master File
    ActiveSheet.Copy Before:=ThisWorkbook.Sheets(1)
    
    'Close the file
    CurrentWorkBook.Close False
    Set MyPath = MyPath.Offset(1, 0)
Loop
End Sub

Let me know how you go.

Teeroy.
 
Upvote 0
Thanks for the reply Teeroy, the part of your code I'm having a hard time with is it doesn't seem like it 'points to' any specific files. In my original code, it 'pointed to' the following folder C:\Users\abc\Desktop\Excel Test2\ - copied it, placed in the master file, closed it - then went to another folder C:\Users\abc\Desktop\Excel Test\ to find the latest file in there. Obviously my code wasn't working, but ideally it would take latest file from folder 1, place it on it's own tab in master file, close that file, open the latest file in folder 2, place it on it's own tab in the master file, close it - wash and repeat with a number of other latest files in different folders. At the end, there would only be the master file opened and it would have about 10 different tabs with data from about 10 different places.

Where would those 'point to' commands go in your code?
 
Upvote 0
Thanks for the reply Teeroy, the part of your code I'm having a hard time with is it doesn't seem like it 'points to' any specific files. In my original code, it 'pointed to' the following folder C:\Users\abc\Desktop\Excel Test2\ - copied it, placed in the master file, closed it - then went to another folder C:\Users\abc\Desktop\Excel Test\ to find the latest file in there. Obviously my code wasn't working, but ideally it would take latest file from folder 1, place it on it's own tab in master file, close that file, open the latest file in folder 2, place it on it's own tab in the master file, close it - wash and repeat with a number of other latest files in different folders. At the end, there would only be the master file opened and it would have about 10 different tabs with data from about 10 different places.

Where would those 'point to' commands go in your code?
Code is working like a charm for one folder, I understand to 'point to' the first folder in cell A1, but what I'm struggling with is how to change code (if at all) and where to place that location in the sheet - on B1? Then C1, etc?
 
Upvote 0
I would suspect I'd change the code to something like this:
Set MyPath = Sheets("File_Locations").Range("A1:A10")
Then on the File_Locations tab paste in the folder in each cell - i.e. C:\Users\abc\Desktop\Excel Test2\ in A1, C:\Users\abc\Desktop\Excel Test\ in A2, etc.

But tried that and getting an error. Any tips?
 
Upvote 0
Hi investinthebest,

Your code was having problems as the master workbook was the ActiveWorkbook when you closed the ActiveWorkbook so with the file containing the macro closed the code could not continue. Below is a more efficient structure as it loops through all the directories (rather than repeating the code), explicitly references the workbooks and is scalable.

In your master workbook add a Sheet called "File_Locations". On this sheet from A1 down put in the directories where you want to get the latest files, then use the following Code.


Code:
Sub OpenLatestFile()
'Variable 1
'Declare the variables
Dim MyPath As Range
Dim MyFile As String
Dim LatestFile As String
Dim LatestDate As Date
Dim LMD As Date
Dim CurrentWorkBook As Workbook


'Specify the path to the folder
Set MyPath = Sheets("File_Locations").Range("A1")
Do While MyPath.Value <> ""

    LatestDate = 0
    'Make sure that the path ends in a backslash
    If Right(MyPath.Value, 1) <> "\" Then MyPath.Value = MyPath.Value & "\"
    
    'Get the first Excel file from the folder
    MyFile = Dir(MyPath.Value & "*.xls*", vbNormal)
    
    'If no files were found, exit the sub
    If Len(MyFile) = 0 Then
        MsgBox "No files were found...", vbExclamation
        Exit Sub
    End If
    
    'Loop through each Excel file in the folder
    Do While Len(MyFile) > 0
    
        'Assign the date/time of the current file to a variable
        LMD = FileDateTime(MyPath.Value & MyFile)
        
        'If the date/time of the current file is greater than the latest
        'recorded date, assign its filename and date/time to variables
        If LMD > LatestDate Then
            LatestFile = MyFile
            LatestDate = LMD
        End If
        
        'Get the next Excel file from the folder
        MyFile = Dir
    Loop
    
    'Open the latest file
    Set CurrentWorkBook = Workbooks.Open(MyPath & LatestFile)
    
    'Move that tab to the Master File
    ActiveSheet.Copy Before:=ThisWorkbook.Sheets(1)
    
    'Close the file
    CurrentWorkBook.Close False
    Set MyPath = MyPath.Offset(1, 0)
Loop
End Sub

Let me know how you go.

Teeroy.

Code works perfect, there was a slight change I had to make based on my file names, but finally figured it out. Thanks for sharing Teeroy!
 
Upvote 0
I'm glad you got it working for you.

I'm curious about what change you had to make though.
 
Upvote 0
I'm glad you got it working for you.

I'm curious about what change you had to make though.
One of the folders I was 'pointing to' also included my 'master file' which was a xlsm file - the code looked for latest xls* - so it was trying to open my master, even though it was already in use.

I posted in another thread another issue I've run across though, maybe you can help? The code:
MyFile = Dir(MyPath.Value & "*.xls*", vbNormal)
Works great if looking for .xls files - but I'm also looking for .lis files. Any tips for a revision to that code? The only way I can seem to get it to work is removing ".xls" entirely and leaving the quotes blank - then it picks up ALL files. While the folders only have xlsx, xls, and lis files in them, I would worry if another file accidentally got up into a folder, the macro would flip out trying to open a non-excel supported file.
 
Upvote 0
You could either


  1. loop through the folder twice, once for each extension; or
  2. get all files then check whether each file has either a .xls* or .lis extension

if you plan on only having .xls* or .lis extensions in the directories I'd us method 2 getting the extension with something like:

Code:
FileExt = Right(MyFile, Len(MyFile) - InStr(MyFile, "."))
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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