VBA Excel FileDialog设置/重置过滤器 [英] VBA Excel FileDialog to set/reset filters

问题描述

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

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.

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

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

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:

完成此数据分析后，用户将运行第二个子菜单来选择另一个文件，但该文件只能是CSV文件.因此，我使用此代码请求CSV:

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:

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

问题在于FileDialog框会记住第一个过滤器(自定义XLS)，他们需要单击下拉列表以查看仅适用于CSV的适当过滤器...

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...

所以这肯定会使用户感到困惑……我猜我需要在用户完成第一个宏之后清除"我们的第一个过滤器.关于该代码的任何建议，以清除(或重置)第一个过滤器?

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?

当我发现我认为是类似问题的问题时，尝试将其添加到下面 FileDialog保留了以前的过滤器:

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

With .Filters
.Clear 
End With

但是会导致编译错误:引用无效或不合格

But results in Compile error: Invalid or unqualified reference

推荐答案

这在我的环境中有效.我所做的唯一不同是将对话框声明为 FileDialog 而不是 Object .

This works in my environment. The only thing I made differently was to declare dialogs as FileDialog instead of 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

这篇关于VBA Excel FileDialog设置/重置过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！