excel - 在主文件夹或子文件夹上获取仅数字 `highest number` 的文件名的完整路径？

Question

我正在使用数据库软件并从中导出 excel 文件并以 ********.xls 之类的名称保存在我的桌面上
***** 仅表示数字，例如 08134471.xls
23222578.xls
文件名上的这些数字在数量和长度上是随机的。
路径不变D:\Users\Waleed\Desktop\但是，文件名每次都会递增地更改为更高的数字。
所以 ，我需要用 highest number 打开工作簿在引用的路径上。
提前，感谢您的帮助。

Sub Open_Numeric_File()
 
    Workbooks.Open "D:\Users\Waleed\Desktop\08134471.xls"
 
End Sub

Answer 1

请尝试下一个功能。它将独立于数字名称模式返回。我的意思是，作为“0002345”或“02346”，它处理文件夹和子文件夹中的所有文件:

Function getLastFileName(strFold As String, Optional strext As String = "*.*") As String
    Dim arrD, i As Long, lastName As String, lngNb As Long, arrN, El
    'return all files name in an array
    arrD = Filter(Split(CreateObject("wscript.shell").Exec("cmd /c dir """ & strFold & strext & """ /b /s").StdOut.ReadAll, vbCrLf), "\")
    For Each El In arrD   'iterate between the array elements
            arrN = Split(El, "\") 'make an array splitting the name by "\"
            'check if the name is numeric:
            If IsNumeric(Split(arrN(UBound(arrN)), ".")(0)) Then
                'compare the lngNb variable (initially 0) with the numeric value:
                If lngNb < CLng(Split(arrN(UBound(arrN)), ".")(0)) Then
                     'addapt lngNb like the bigger number
                     lngNb = CLng(Split(arrN(UBound(arrN)), ".")(0)): lastName = El
                End If
            End If
    Next
    getLastFileName = lastName 'build the necessary path
End Function

可以通过以下方式进行测试:

Sub testGetLastFileName()
    Debug.Print getLastFileName("D:\Users\Waleed\Desktop\", "*.xls*")
End Sub

它不包括工作簿名称不是数字...
已编辑 :
下一个版本仅返回(并处理)主文件夹中的文件(不包括子文件夹中的文件):

Function getLastNumberFile(strFold As String, Optional strext As String = "*.*") As String
    Dim arrD, i As Long, lastName As String, lngNb As Long, El
    'return all files name in an array
    arrD = Split(CreateObject("wscript.shell").Exec("cmd /c dir """ & strFold & strext & """ /b").StdOut.ReadAll, vbCrLf)
    If UBound(arrD) = -1 Then MsgBox "Nothing could be found in the path you supplied...": Exit Function
    arrD(UBound(arrD)) = "@@##": arrD = Filter(arrD, "@@##", False) 'remove the last (empty) element

    For Each El In arrD   'iterate between the array elements
            If IsNumeric(Split(El, ".")(0)) Then
                'compare the lngNb variable (initially 0) with the numeric value:
                If lngNb < CLng(Split(El, ".")(0)) Then
                     'addapt lngNb like the bigger number
                     lngNb = CLng(Split(El, ".")(0)): lastName = El
                End If
            End If
    Next
    getLastNumberFile = strFold & lastName 'build the necessary path
End Function

它可以用来简单地设置必要的工作簿:

  Set wb1 = Workbooks.Open(getLastNumberFile("D:\Users\Waleed\Desktop\", "*.xls*"))

下一个版本，能够返回这两种情况。它使用了一个技巧，使用文件夹路径和“|”之间的连接获得的分隔符拆分连接的数组。然后删除最后一个数组元素(如果返回没有子文件夹文件):

Function getLastFileN(strFold As String, Optional strext As String = "*.*", Optional boolSubfolders = False) As String
    Dim arrD, i As Long, lastName As String, lngNb As Long, arrN, El
    'return all files name in an array
    If boolSubfolders Then 'subfolders included
        arrD = Filter(Split(CreateObject("wscript.shell").Exec("cmd /c dir """ & strFold & strext & """ /b/s").StdOut.ReadAll, vbCrLf), "\")
    Else                   'without subfolders
        arrD = Split(CreateObject("wscript.shell").Exec("cmd /c dir """ & strFold & strext & """ /b").StdOut.ReadAll, vbCrLf)
        arrD = Split(strFold & Join(arrD, "|" & strFold), "|")  'add the folder path to the file names
        arrD(UBound(arrD)) = "@@##": arrD = Filter(arrD, "@@##", False) 'remove the last (empty) array element
    End If

    For Each El In arrD           'iterate between the array elements
            arrN = Split(El, "\") 'make an array splitting the name by "\"
            'check if the name is numeric:
            If IsNumeric(Split(arrN(UBound(arrN)), ".")(0)) Then
                'compare the lngNb variable (initially 0) with the numeric value:
                If lngNb < CLng(Split(arrN(UBound(arrN)), ".")(0)) Then
                     'addapt lngNb like the bigger number
                     lngNb = CLng(Split(arrN(UBound(arrN)), ".")(0)): lastName = El
                End If
            End If
    Next
    getLastFileN = lastName
End Function