To open the most recently modified Excel workbook in a folder, have VBA compare each eligible file’s modified date and time, then pass the newest file’s full path to Workbooks.Open. The macro below lets you choose a folder, skips Excel’s temporary lock files and the workbook containing the code, and opens the newest match read-only.
Use VBA to find and open the newest workbook
This example treats “last file” as the workbook with the latest file-system modification time. It searches for standard Excel workbook extensions and ignores files whose timestamps cannot be read. Save a copy of your workbook before running any macro: VBA changes cannot be undone with Ctrl+Z.
Option Explicit
Public Sub OpenNewestWorkbookInFolder()
Dim folderPath As String
Dim fileName As String
Dim candidatePath As String
Dim newestPath As String
Dim extension As String
Dim candidateDate As Date
Dim newestDate As Date
On Error GoTo OpenError
' Ask the user to choose a folder.
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select a folder containing Excel workbooks"
.AllowMultiSelect = False
If .Show <> -1 Then Exit Sub
folderPath = .SelectedItems(1)
End With
If Right$(folderPath, 1) <> Application.PathSeparator Then
folderPath = folderPath & Application.PathSeparator
End If
' Dir returns matching file names from the selected folder.
fileName = Dir$(folderPath & "*.xls*")
Do While Len(fileName) > 0
extension = LCase$(Mid$(fileName, InStrRev(fileName, ".") + 1))
If Left$(fileName, 2) <> "~$" Then
Select Case extension
Case "xlsx", "xlsm", "xlsb", "xls"
candidatePath = folderPath & fileName
' Do not select the workbook that contains this macro.
If StrComp(candidatePath, ThisWorkbook.FullName, vbTextCompare) <> 0 Then
' Skip a file if its timestamp cannot be read.
Err.Clear
On Error Resume Next
candidateDate = FileDateTime(candidatePath)
If Err.Number = 0 Then
If Len(newestPath) = 0 Or candidateDate > newestDate Then
newestDate = candidateDate
newestPath = candidatePath
End If
End If
On Error GoTo OpenError
End If
End Select
End If
fileName = Dir$()
Loop
If Len(newestPath) = 0 Then
MsgBox "No readable Excel workbooks were found in that folder.", vbInformation
Exit Sub
End If
' Open without updating external links, and protect the source from edits.
Workbooks.Open Filename:=newestPath, UpdateLinks:=0, ReadOnly:=True
Exit Sub
OpenError:
MsgBox "The workbook could not be opened: " & Err.Description, vbExclamation
End Sub
The macro uses Excel’s folder picker, so it does not require you to type or hard-code a path. To allow editing, change ReadOnly:=True to ReadOnly:=False, or remove the argument; Excel’s normal default is not read-only. Keep in mind that opening a workbook for editing can expose it to accidental changes.
How the search and date comparison work
Dir$ searches the selected folder for names matching *.xls*. That pattern is deliberately broad enough to find common Excel extensions, but the subsequent Select Case accepts only .xlsx, .xlsm, .xlsb, and .xls. It therefore does not intentionally open every file whose name happens to start with “xls.” Add another extension to the case list if your workflow genuinely uses it.
For each accepted file, FileDateTime supplies its recorded modification date and time. The macro keeps a path whenever its timestamp is later than the current winner, then opens the saved full path after the search ends. It skips names beginning with ~$, which Excel commonly uses for temporary owner or lock files while a workbook is open. Those are not the workbook you want to select.
The comparison uses a strict “later than” test. If two files have identical recorded timestamps, the first one encountered remains the selection; Dir does not promise a particular order that should be used as a tie-breaker. If ties matter, add an explicit second rule, such as choosing the alphabetically later name. Also note that this macro searches only the folder you select, not its subfolders.
Decide what “last file” means
People use “last” to mean different things. The example answers the common question, “Which workbook was modified most recently?” That is usually appropriate for a folder where a report is overwritten or updated as work progresses. It does not necessarily identify the file that was created last, copied into the folder last, or given the latest name.
- Most recently modified: Compare file timestamps, as the macro does. Editing or saving an older workbook can make it the newest by this measure.
- Newest creation date: Use a different file-system method to inspect creation metadata. Copies and downloads can have creation times that reflect when they arrived on the computer rather than when the original document was made.
- Latest date in the filename: Parse the name, for example a pattern such as
Sales_2026-11-03.xlsx. This is useful when the filename is the official reporting period, but requires consistent naming and date parsing. - Last item in alphabetical order: Sort names and choose the final one. This is not the same as choosing the most recently changed file unless the naming convention encodes dates in a sortable format.
Choose the rule that matches the business meaning of “latest.” A workbook with a date in its title may refer to the period covered, while its modified time records a later correction. Those are both valid dates, but they answer different questions.
Choose the folder and file types carefully
The folder picker is useful when the location changes from one run to another. If the folder is always fixed, you can instead assign a path directly to folderPath, but a typed path must be valid on every computer where the macro runs. A mapped drive such as Z:\Reports may not exist for another user, while a network share may require access permissions or a connection.
The code includes four common workbook formats. It omits Excel templates, add-ins, and unrelated files on purpose. If you want templates too, decide whether opening one should create a new workbook rather than open the template itself, and adjust the workflow accordingly. Keep macro-enabled files such as .xlsm only if they are expected and trusted; this macro does not make their embedded macros safe.
For a folder containing multiple kinds of files, filtering by extension is important. A generic “newest file” search could select a PDF, a text file, or an Excel lock file and then fail when Excel tries to open it as a workbook. Narrowing the eligible set reduces that risk and makes the selection predictable.
Install and run the macro safely
- Save a working copy of the workbook that will contain the macro as an Excel Macro-Enabled Workbook (
.xlsm), unless the macro is stored in another trusted macro-enabled location. - In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. If the Developer tab is available, you can also choose Developer > Visual Basic. On Mac, use Option+F11 (some keyboards require Fn as well), or choose Tools > Macro > Visual Basic Editor where that menu is available.
- In the editor, choose Insert > Module, paste the procedure into the module, and save the workbook.
- Return to Excel and run
OpenNewestWorkbookInFolderfrom Developer > Macros, or press Alt+F8 on Windows and select it. On Mac, the macro dialog shortcut is commonly Option+F8; the Developer menu is an alternative. - Select the folder in the dialog. If a readable matching workbook exists, Excel opens the one with the newest modification timestamp.
Excel for the web can display a workbook containing VBA, but it cannot create, edit, or run VBA macros. Use the desktop Excel application. On Mac, VBA is supported in desktop Excel, but file access, security prompts, and dialog behavior can differ; test the macro with a copy in a folder you can access. Do not lower macro security globally just to run an unfamiliar file. Use only code and workbooks from sources you trust.
Common problems and practical fixes
If the macro reports that no workbooks were found, check that the chosen folder is the one containing the files and that their extensions appear in the code’s Select Case list. A file may look like an Excel workbook while actually having an unsupported extension, or the visible extension may be hidden by Windows Explorer. Check that the workbook is not in a subfolder, because the sample does not search recursively.
If the wrong workbook opens, first inspect the files’ modification times in File Explorer. The macro follows file-system timestamps, not the date written in a worksheet or filename. Cloud-synced folders and network locations may also present timestamps affected by synchronization or copying. If the intended “latest” value is a date embedded in the file name, change the selection logic rather than assuming the modification time represents it.
If Excel says the file cannot be opened, verify that it is not damaged, that you have permission to read it, and that it is not already being changed by another process. The sample opens read-only and suppresses automatic updates to external workbook links by setting UpdateLinks:=0; that does not remove links or guarantee that every security or format prompt will disappear. If you need linked data refreshed, handle that deliberately after opening.
If VBA stops at FileDateTime, the folder may contain an inaccessible item or a path with an access issue. The supplied code skips individual files when it cannot read their timestamps. If the error instead occurs on Workbooks.Open, the selected file may be protected, damaged, unavailable, or unsuitable for the current Excel installation. The message box includes Excel’s error description to help narrow down the cause.
Alternatives for fixed folders and automated workflows
For a fixed, trusted folder, remove the folder-picker block and assign the folder path directly. This saves a click but makes the macro dependent on that exact location. A robust team solution can store the folder in a worksheet cell or named range, validate that the directory exists, and show the chosen path before opening anything. That is especially useful when colleagues have different user names or drive mappings.
Another approach is to keep files named with sortable dates, such as year-month-day in descending significance: Inventory_2026-11-03.xlsx. Alphabetical sorting then follows calendar order, provided every name uses the same fixed-width format. This can be more meaningful than modification time when a late edit should not change which reporting period counts as current. It still requires validation to reject unexpected names.
Power Query may be a better fit when the goal is to combine or import files from a folder rather than open one workbook. In desktop Excel for Windows, the usual entry point is Data > Get Data > From File > From Folder. It can list and transform folder contents, but it is not a substitute for a macro that opens a single workbook window. Choose based on the outcome: open a workbook, or retrieve data from files.
Frequently Asked Questions
Does this open the newest file by name or by date?
It opens the eligible workbook with the latest file modification date and time. It does not sort filenames or inspect dates inside the workbook. Change the comparison rule if “newest” means something else in your process.
Will the macro search inside subfolders?
No. It checks only the folder selected in the dialog. Recursive searching requires additional code to visit each subfolder and compare files across all of them.
Can I make it open the newest CSV file?
Yes, but CSV files are not Excel workbooks in the same sense as .xlsx files. Add CSV handling deliberately and decide whether to open it as a text-delimited workbook or import it with specific encoding and delimiter settings. Do not simply add csv to the workbook extension list without considering how the data should be interpreted.
Why does it open read-only?
The sample uses ReadOnly:=True to reduce the chance of accidentally changing the source. Replace it with ReadOnly:=False if editing is required and you have permission, or omit the argument to use Excel’s default behavior.
Can this macro run in Excel for the web?
No. Excel for the web does not run VBA. Open the file in desktop Excel for Windows or Mac to use the macro; on a Mac, test the folder selection and file access in the specific desktop version you use.
Related
- Excel Shortcut to Insert Another Row in Windows and Mac
- Excel Shortcut to Select to the End of a Column
- Excel Shortcut to Select Cells to the End of a Column
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Macro Recorder Best Options for Excel and Windows PCsThe best macro recorder depends on what you want to automate: for repeated tasks inside Excel, start with…
- VBA AutoFilter Date: Filter Dates ReliablyTo filter dates with VBA, apply AutoFilter to a range that includes its header row, then set date…
- Excel VBA Open a Given File: Code and OptionsYes. In Excel for Microsoft 365 on Windows, VBA opens a specific workbook with the Workbooks.Open method, supplying…
- VBA Dictionary: How It Works and When to Use ItYes. A VBA Dictionary exists as the Scripting.Dictionary object: a key-value container that lets a macro store items…
- Excel Macro Step Into: How to Debug VBA Line by LineIn Excel VBA, Step Into runs a macro one statement at a time and enters any procedure or…
- VBA Double Quotes in Strings: How to Escape a QuoteTo include a double quote inside a VBA string, type two double quotes in a row: VBA treats…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.