excel 为FileDialog筛选多个文件名

ymdaylpp  于 2023-03-20  发布在  其他
关注(0)|答案(1)|浏览(138)

我有一个宏,它可以打开一个VBA fileDialog窗口,允许我选择几个文件并返回我选择的文件。
在它运行的文件夹中有几个其他文件,我需要尽可能地为用户提供防猴子的功能,所以我想为他们可以打开的文件名设置一个过滤器。
到目前为止,文件名只有一种结构:“xxxxx_Orders_xxxx.xls”,我是用FileDialog.InitialFileName来做这个的。但是现在我有两种类型的文件:“xxxxx_Orders_xxxx.xls”和“xxxxx_Registrations_xxxx.xls”,并且我无法让文件对话框窗口仅显示文件夹中的这些类型的文件。
我试过FileDialog.InitialFileName = "*Orders*|*Registrations*",但似乎不起作用。
有人能帮忙吗?下面是我获得文件的代码:

Function getFiles(Optional nameFilter As String = "", Optional windowTitle As String = "", Optional multiSelect As Boolean = True, Optional maxSelected As Integer = 10000) As FileDialogSelectedItems
' Prompts the user to choose one or more files and returns the selected files
   Dim wb As Workbook
   Dim wbPath As String

   If windowTitle = "" Then windowTitle = "Select one or more files to process"
   
showWindow:
   With Application.FileDialog(msoFileDialogFilePicker)
      .Filters.Clear
      .Title = windowTitle
      .InitialFileName = ThisWorkbook.path & "\" ' Open current directory by default
      .Filters.Add "Excel Files", "*.xl*;*.xm*"
      .AllowMultiSelect = multiSelect
      .InitialFileName = nameFilter ' <--- NAME FILTER
      If .show = -1 Then ' if OK is pressed
         wbPath = .SelectedItems(1)
      End If
                
      'If inArray(.SelectedItems, ThisWorkbook.FullName) <> -1 Then finishProgram ("You can't select this Workbook.")
      
      Set getFiles = .SelectedItems
   End With
   
   Select Case getFiles.count
      Case 0:  End
      Case Is > maxSelected: GoTo showWindow
      Case Else: ' Continue
   End Select
   
   'If wbPath = "" Then End  'if no file chosen, end the program 
End Function
zfciruhq

zfciruhq1#

您可以在Excel中使用其他文件选取器。虽然有些不同,但可能对您有所帮助。请尝试以下操作:

ChDrive "c:"
ChDir "c:\Folder\Folder\"
SelectedFiles = Application.GetOpenFilename("Orders, *order*.xl*, Registrations, *registrations*.xl*", 1, , , True)

需要ChDriveChDir才能导航到正确的文件夹和驱动器。

相关问题