excel - vba 打开文件夹中最近的文件

Question

文件夹中有一些 exel 文件。目的是搜索具有最高日期的文件(格式为:Fundings "& Format(LMD, "DDMMYY") & ".xls)并打开它。例如。文件名是 Fundings 270818、Fundings 110618，最新的是第一个。下面的代码遇到“MyFile = Dir(MyPath, vbNormal)”为空的错误。

 Dim MyPath  As String
 Dim MyFile  As String
 Dim LatestFile  As String
 Dim LatestDate  As Date
 Dim LMD  As Date

 LMD = Date

 'Specify the path to the folder
 MyPath = "C:\Users\topal\Desktop\Spreaddeterminierung\Fundings " & Format(LMD, "DDMMYY") & ".xls"



 'Get the first Excel file from the folder
 MyFile = Dir(MyPath, vbNormal)

 'If no files were found, exit the sub
 If Len(MyFile) = 0 Then
     MsgBox "No Sir", 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 = Date

     '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

 End Sub

Answer 1

您可以循环文件夹并提取字符串的日期部分并存储最大值以用于识别文件。以下还应用了 "xlsx" 的文件掩码您可以删除或更改。它使用正则表达式根据您声明的模式查找合格的文件名。

Option Explicit

Public Sub GetLastestDateFile()
    Dim FileSys As Object, objFile As Object, myFolder As Object, strFile As String, dteFile As Long
    Const myDir As String = "C:\Users\User\Desktop\TestFolder"
    Set FileSys = CreateObject("Scripting.FileSystemObject")
    Set myFolder = FileSys.GetFolder(myDir)

    Dim fileName As String, tempDate As Long, fileMask As String

    dteFile = 0: fileMask = "xlsx"
    For Each objFile In myFolder.Files
        If FileSys.GetExtensionName(objFile.Path) = fileMask And ValidateFile(Split(objFile.Name, ".xlsx")(0)) Then
            tempDate = GetDateFromFileName(objFile.Name)
            Dim pseudoDate As String
            pseudoDate = ReArrange(tempDate)
            If pseudoDate > dteFile Then dteFile = pseudoDate
        End If
    Next objFile
    If Not tempDate = 0 Then Workbooks.Open (myDir & "\" & "Fundings " & Format$(ReArrange(dteFile), "000000") & "." & fileMask)
End Sub

Public Function ReArrange(ByVal tempDate As String) As String
    tempDate = Format$(tempDate, "000000")
    ReArrange = Format$(Right$(tempDate, 2), "00") & Format$(Mid$(tempDate, 3, 2), "00") & Format$(Left$(tempDate, 2), "00")
End Function

Public Function ValidateFile(ByVal fileName As String) As Boolean
    With CreateObject("VBScript.RegExp")
        .Global = True
        .MultiLine = True
        .Pattern = "Fundings\s\d{6}$"
        ValidateFile = .test(fileName)
    End With
End Function

Public Function GetDateFromFileName(ByVal fileName As String) As Date
    On Error GoTo errhand
    GetDateFromFileName = Split(Split(fileName, "Fundings ")(1), ".")(0)
    Exit Function
errhand:
    GetDateFromFileName = 0
End Function

正则表达式:

试试正则表达式 here .

解释:

Fundings\s\d{6}$
/
gm

Fundings匹配字符 Fundings字面意思(区分大小写)
\s匹配任何空白字符(等于 [\r\n\t\f\v ] )
\d{6}匹配一个数字(等于 [0-9] )
{6}量词——精确匹配 6 次
$在行尾断言位置