VBA打开文件，只有通配符知道扩展名

I am trying to get Excel to open any file in the a given folder (ThisWorkbook.Path\Peach\Apple) that has .xlsm extension (there is always only 1 file). Is it possible to open it with wildcard character? I do not know the name of the file, just the extension.

我试图让Excel打开一个扩展名为.xlsm的给定文件夹(ThisWorkbook.Path \ Peach \ Apple)中的任何文件(总是只有1个文件)。是否可以使用通配符打开它?我不知道文件的名称,只是扩展名。

If not, is there a way to do it?

如果没有,有办法吗?

4 个解决方案

#1

Just ask the file system for the first matching file:

只需向文件系统询问第一个匹配的文件:

Dim path As String: path = ThisWorkbook.path & "\Peach\Apple\"

FindFirstFile = Dir$(path & "*.xlsm")

If (FindFirstFile <> "") Then 
   Workbooks.Open path & FindFirstFile
Else
   '// not found
End If

(This will not search sub-directories)

(这不会搜索子目录)

#2

You mentioned that it would be nice addition to open last modified file or file with shortest name, so let's start - there's a code example how you can grab all three files (first finded, last modified, with shortest name). You can modify this as you wish (add some parameters, add error handling, return only specified, etc).

你提到打开最后一个修改过的文件或文件名最短的文件会很好,所以让我们开始吧 - 这里有一个代码示例,你可以抓住所有三个文件(首先查找,最后修改,最短名称)。您可以根据需要修改它(添加一些参数,添加错误处理,仅返回指定等)。

Sub Test()
   'declarations
    Dim fso As Object
    Dim folder As Object
    Dim file As Object
    Dim path As String

    Dim first_finded As Object
    Dim recently_modified As Object
    Dim shortest_name As Object
    Dim recently As Date
    Dim shortest As Long
    Dim firstFinded As Boolean

    'setting default recently date(24 hours from now) and path
    recently = DateAdd("h", -24, Now)
    path = ThisWorkbook.path & "\Peach\Apple\"

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set folder = fso.GetFolder(path)


    'iteration over folder
    For Each file In folder.Files
        If file.Name Like "*.xlsm" Then

            'grab first finded .xlsm
            If Not firstFinded Then
                firstFinded = Not firstFinded
                Set first_finded = file
            End If

            'grab lastmodified .xlsm
            If file.DateLastModified > recently Then
                recently = file.DateLastModified
                Set recently_modified = file
            End If

            'grab short named .xlsm
            If shortest = 0 Or shortest > Len(file.Name) Then
                shortest = Len(file.Name)
                Set shortest_name = file
            End If
        End If
    Next

    'debug-print names
    Debug.Print first_finded.Name
    Debug.Print recently_modified.Name
    Debug.Print shortest_name.Name

    'so now you can uncomment this and open what you want
    'Call Workbooks.Open(path & recently_modified.Name)

End Sub

#3

Try the code below, it will open your "*.xlsm" file, in the path you've requested.

尝试下面的代码,它将在您请求的路径中打开您的“* .xlsm”文件。

Sub OpenXLSMWildcardfile()

Dim Path As String

Path = ThisWorkbook.Path & "\Peach\Apple\"
Workbooks.Open (Path & "*.xlsm")

End Sub

#4

PFB for the code required for opening the macro file with extension(.xlsm).

PFB用于打开带扩展名(.xlsm)的宏文件所需的代码。

Sub OpeningFile()

'Declaring variables
Dim FileName, FolderPath As String

'Initializing folder path
FolderPath = ThisWorkbook.Path & "\Peach\Apple\"

'Finding the file name using wildcard
FileName = Dir(FolderPath & "*.xlsm")

'Looping through the workbook which are saved as macro enabled workbooks
While FileName <> ""
    Workbooks.Open FolderPath & FileName
    FileName = Dir()
Wend

End Sub

#1