CODEWITHSUNDEEP
vbaexcelfilter
joshjayse
joshjayse

Reputation: 77

VBA Excel FileDialog to set/reset filters

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.

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:

Custom Excel Files

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

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

Select 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:

With .Filters
.Clear 
End With

But results in Compile error: Invalid or unqualified reference

Upvotes: 3

Views: 37028

Answers (2)

Andreas Covidiot
Andreas Covidiot

Reputation: 4755

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

The msoFileDialogSaveAs dialog does NOT support file filters

Upvotes: 2

VBobCat
VBobCat

Reputation: 2712

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

Upvotes: 7

Related Questions