Excel 2016 VBA宏优化：文件名随版本（含日期）变化时自动打开指定PDF表单

Got it, let's tweak your code to automatically find and open the latest Form_*.pdf file instead of just opening the folder. Here's how to do it step by step:

Key Approach #

We’ll:

• Scan the target folder for all PDF files starting with Form_
• Compare their last modified dates to pick the newest one
• Open that file directly using your system’s default PDF reader

Modified Full Code #

Sub Open_Template()
    Dim myolapp As Object
    Dim myitem As Object
    Dim answer As Integer
    Dim targetPath As String
    Dim latestFile As String
    Dim file As String
    
    answer = MsgBox("Do you still need to create the required form?", vbQuestion + vbYesNo + vbDefaultButton1, "Form Required")
    
    Set myolapp = CreateObject("Outlook.Application")
    myolapp.Session.Logon
    
    ' Open the email template as before
    Set myitem = myolapp.CreateItemFromTemplate("C:\filepath\email.oft")
    myitem.Display
    
    ' Auto-locate and open the latest form PDF
    If answer = vbYes Then
        targetPath = "C:\filepath\"
        ' Get the first matching PDF file
        file = Dir(targetPath & "Form_*.pdf")
        
        If file <> "" Then
            ' Initialize latestFile with the first found file
            latestFile = file
            ' Loop through all remaining matching files to find the newest
            Do While file <> ""
                ' Compare last modified dates to identify the latest file
                If FileDateTime(targetPath & file) > FileDateTime(targetPath & latestFile) Then
                    latestFile = file
                End If
                ' Get next matching file in the folder
                file = Dir
            Loop
            
            ' Open the latest PDF using your system's default program
            Shell "rundll32.exe url.dll,FileProtocolHandler " & Chr(34) & targetPath & latestFile & Chr(34), vbNormalFocus
        Else
            ' Alert user if no matching files are found
            MsgBox "No Form_*.pdf files found in " & targetPath, vbExclamation, "File Missing"
        End If
    End If
End Sub

Breakdown of the Updated Section #

• Dir(targetPath & "Form_*.pdf"): This filters all PDF files in the folder that start with Form_, so it doesn’t matter what month/year is in the rest of the filename.
• FileDateTime: We use this to check each file’s last modified date, ensuring we pick the most recent one (this is extra safe even if any old versions accidentally stay in the folder).
• Shell Command: Instead of opening the folder, we use rundll32.exe url.dll,FileProtocolHandler to launch the PDF directly with your system’s default reader. The Chr(34) adds quotes around the file path to handle any spaces in the path or filename.

Quick Notes #

• I simplified the double backslashes in your path to single ones since VBA accepts single backslashes for file paths.
• If you ever need to prioritize creation date over modified date, FileDateTime still works for that on Windows systems (it returns the creation date for files that haven’t been edited).

内容的提问来源于stack exchange，提问作者Keesler Water Walker