VBA Excel FileDialog 设置/重置过滤器

VBA Excel FileDialog to set/reset filters

提问by joshjayse

I have a macro that asks a user to choose multiple files for data analysis. User selects a Excel or CSV file first (XLSX, XLS, CSV), then asks for a second file but CSV only. The intent of the tool is to combine the two data files into one.

我有一个宏要求用户选择多个文件进行数据分析。用户首先选择 Excel 或 CSV 文件（XLSX、XLS、CSV），然后要求第二个文件，但仅 CSV。该工具的目的是将两个数据文件合二为一。

In one Sub, I ask the user to select any compatible XLSX, XLS, or CSV files using the FileDialog code:

在一个 Sub 中，我要求用户使用 FileDialog 代码选择任何兼容的 XLSX、XLS 或 CSV 文件：

Dim myObj As Object
Dim myDirString As String
Set myObj = Application.FileDialog(msoFileDialogFilePicker)
With myObj
    .InitialFileName = "C:\Users\" & Environ$("Username") & "\Desktop"
    .Filters.Add "Custom Excel Files", "*.xlsx, *.csv, *.xls"
    .FilterIndex = 1
    If .Show = False Then MsgBox "Please select Excel file.", vbExclamation: Exit Sub
    myDirString = .SelectedItems(1)
End With

It seems to filter appropriately:

它似乎适当地过滤：

After this data analysis in complete, then the user runs a second sub to select another file, but it must be a CSV file only. So I use this code to request CSV:

完成此数据分析后，用户运行第二个子程序以选择另一个文件，但它只能是 CSV 文件。所以我用这个代码来请求 CSV：

Dim yourObj3 As Object
Dim yourDirString3 As String
Set yourObj3 = Application.FileDialog(msoFileDialogFilePicker)
With yourObj3
    .InitialFileName = "C:\Users\" & Environ$("Username") & "\Desktop"
    .Filters.Add "CSV Files", "*.csv"
    .FilterIndex = 1
    If .Show = False Then MsgBox "Please select CSV file.", vbExclamation: Exit Sub
    yourDirString3 = .SelectedItems(1)
End With

The problem is the FileDialog box remembers the first filter (Custom XLS) and they need to click the drop down to see the appropriate filter for CSV only...

问题是 FileDialog 框记住了第一个过滤器（自定义 XLS），他们需要单击下拉列表才能查看仅适用于 CSV 的适当过滤器...

So this would certainly be confusing to the user...I'm guessing I need to "clear" our that first filter after the user completes the first macro. Any suggestions on that code to clear (or reset) the first filter?

所以这肯定会让用户感到困惑......我猜我需要在用户完成第一个宏后“清除”我们的第一个过滤器。关于清除（或重置）第一个过滤器的代码有什么建议吗？

Tried adding this below it when I found what I thought was a similar question FileDialog persists previous filters:

当我发现我认为是类似的问题FileDialog 保留以前的过滤器时，尝试在它下面添加它：

With .Filters
.Clear 
End With

But results in Compile error: Invalid or unqualified reference

但导致编译错误：无效或不合格的引用

回答by VBobCat

This works in my environment. The only thing I made differently was to declare dialogs as FileDialoginstead of Object.

这适用于我的环境。我所做的唯一不同的是将对话框声明为FileDialog而不是Object.

Sub Test()
    Dim myObj As FileDialog
    Dim myDirString As String
    Set myObj = Application.FileDialog(msoFileDialogFilePicker)
    With myObj
        .InitialFileName = "C:\Users\" & Environ$("Username") & "\Desktop"
        .Filters.Clear
        .Filters.Add "Custom Excel Files", "*.xlsx, *.csv, *.xls"
        .FilterIndex = 1
        .Show
    End With
    Dim yourObj3 As FileDialog
    Dim yourDirString3 As String
    Set yourObj3 = Application.FileDialog(msoFileDialogFilePicker)
    With yourObj3
        .InitialFileName = "C:\Users\" & Environ$("Username") & "\Desktop"
        .Filters.Clear
        .Filters.Add "CSV Files", "*.csv"
        .FilterIndex = 1
        .Show
    End With
End Sub

回答by Andreas Dietrich

Although it is not directly the answer to the specific msoFileDialogFilePickerfrom the OP (and googled this answer), I had the same problem with the msoFileDialogSaveAsdialog in Excel 2010 where errors are raised trying to modify the filters in any way because it obviously is not supported:-/

尽管它不是msoFileDialogFilePickerOP 中特定问题的直接答案（并在 google 上搜索了此答案），但我msoFileDialogSaveAs在 Excel 2010 中的对话框中遇到了同样的问题，其中尝试以任何方式修改过滤器时会引发错误，因为它显然不受支持： -/

The msoFileDialogSaveAsdialog does NOT support file filters

该msoFileDialogSaveAs对话框不支持文件过滤器