如何在VBA中基于部分文件名选取最新修改的目标文件

Looks like you need a way to auto-open the newest file in a folder that matches a specific filename fragment—here's a solid VBA solution that does exactly that, with error handling to cover edge cases.

Sub OpenMostRecentMatchingFile()
    Dim targetPath As String
    Dim fileNameSnippet As String
    Dim fso As Object
    Dim folder As Object
    Dim file As Object
    Dim latestFile As Object
    Dim latestModTime As Date
    
    ' Set your target path and filename snippet here
    targetPath = "C:\Myfile\"
    fileNameSnippet = "MMM B2 06222018"
    
    ' Initialize FileSystemObject (late binding, no reference needed)
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    ' Check if the target folder exists
    If Not fso.FolderExists(targetPath) Then
        MsgBox "Target folder not found: " & targetPath, vbExclamation
        Exit Sub
    End If
    
    Set folder = fso.GetFolder(targetPath)
    latestModTime = #1/1/1900# ' Initialize with an old date
    
    ' Loop through all files in the folder
    For Each file In folder.Files
        ' Check if the filename contains the target snippet (case-insensitive)
        If InStr(1, file.Name, fileNameSnippet, vbTextCompare) > 0 Then
            ' Update latest file if current file is newer
            If file.DateLastModified > latestModTime Then
                latestModTime = file.DateLastModified
                Set latestFile = file
            End If
        End If
    Next file
    
    ' Open the latest matching file if found
    If Not latestFile Is Nothing Then
        Workbooks.Open latestFile.Path
        MsgBox "Opened the most recent matching file: " & latestFile.Name, vbInformation
    Else
        MsgBox "No files found containing the snippet: " & fileNameSnippet, vbExclamation
    End If
    
    ' Clean up objects
    Set latestFile = Nothing
    Set file = Nothing
    Set folder = Nothing
    Set fso = Nothing
End Sub

Key Features & Explanation: #

• Late Binding: Uses CreateObject("Scripting.FileSystemObject") so you don't need to manually add a reference to the Microsoft Scripting Runtime—avoids "missing reference" headaches for other users running your macro.
• Folder Validation: First checks if your target folder exists to prevent runtime errors from trying to access a non-existent path.
• Case-Insensitive Matching: The vbTextCompare flag in InStr ensures the macro finds matches regardless of uppercase/lowercase (remove this argument if you need strict case-sensitive matching).
• Track Newest File: Starts with a very old date (#1/1/1900#) and updates the latestFile variable every time it encounters a newer matching file.
• User Feedback: Includes clear message boxes for missing folders or no matching files, so you know exactly what's going wrong if the macro doesn't open a file.

Quick Setup Steps: #

1. Open Excel, press Alt + F11 to launch the VBA Editor.
2. Insert a new module (right-click your workbook in the Project Explorer > Insert > Module).
3. Paste the code above into the module.
4. Adjust the targetPath and fileNameSnippet variables if your folder path or filename fragment changes.
5. Run the macro (press F5 in the editor, or assign it to a button in Excel for one-click access).

In your example scenario, this macro will automatically select and open "MMM B2 06222018 Updated" since it has the later modification time (10:00 vs 9:00).

内容的提问来源于stack exchange，提问作者HonnSolo