1

I'm running into an error when I attempt to add filters to a FileDialog.

I've tried to troubleshoot this with multiple posts / articles but nothing seems to have solved it. The online documentation identifies using .Filters.Add but it doesn't seem that .Filters is an appropriate method. .Filter.Add is at least recognized but is still invalid. .Filter.Clear is also recognized but is invalid as well.

Code:

Private Sub Command93_Click()
    Dim objFD As Object, strOut As String

    Set objFD = Application.FileDialog(2)

    With objFD
    .Filter.Clear
    .Filter.Add "Excel File", ".xls"

    If objFD.show = -1 Then
        strOut = objFD.selecteditems(1)
    End If

    End With

    Set objFD = Nothing
    Me.txtSaveFile = strOut
    Me.Refresh
End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
Bill Black
  • 21
  • 1
  • 4

2 Answers2

1

  1. You are using the the filters with the wrong dialog box. .Filters will work with msoFileDialogOpen which has a value of 1 or with msofiledialogfilepicker which has a value of 3. So try .Filters with Application.FileDialog(1) or Application.FileDialog(3) as shown below and it will work but it will not work with the msofiledialogsaveas which has a value of 2

  2. The constants msoFileDialogOpen and msoFileDialogSaveAs are not supported in Microsoft Access. What it doesn't mean: is that the Application.FileDialog is NOT suported. What it means: is the constants are not supported. If you type ?msoFileDialogOpen in Immediate Window in MS Access, you will not see any value and hence we have to pass those values literally or declare them.

  3. You have to use .Filters instead of .Filter

See this example

Option Explicit

Const msoFileDialogOpen As Integer = 1

Sub Sample()
    Dim f As Object
    Dim i As Long

    Set f = Application.FileDialog(msoFileDialogOpen)

    With f
        .Filters.Clear
        .Filters.Add "Excel File", "*.xls*"
        .AllowMultiSelect = True

        If .Show Then
            For i = 1 To .SelectedItems.Count
                MsgBox .SelectedItems(i)
            Next
        End If
    End With
End Sub

Screenshot

enter image description here

EDIT:

If you want to use Filters in while saving then check out the link Display Open and Save As Dialog Boxes in Access with API Functions

Here you will see how to use filters in the using the .SaveFileDialog using the CommonDialogAPI

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • Ok from the above. Answer 1 is indeed on the right path. It seems that the Save dialog doesn't accept Filters so Filters.Clear and Filters.Add are both invalid. The Open dialog (3) doesn't allow you to pick a file that is not in the list. i.e. I can't select a file, edit the name, then click open. Is there a method that allows you to change the file name then click Open? – Bill Black Aug 19 '19 at 02:45
  • Sorry I do not understand. If you edit the name, if the file exists then the name will reflect in the autofill dropdown which you can select and it will work? – Siddharth Rout Aug 19 '19 at 02:52
  • Hi Siddharth, The intent was for someone to be able to save a table to an excel file format and this code was to allow for the file to be specified. Using the answer below works fine. You see a list of excel files and you can either select one of those OR you can click on and then modify the name and hit save. If you hit save on a file that exists you get the message, "This file exists, are you sure?" which is the behavior I want anyway. – Bill Black Aug 19 '19 at 03:40
  • In that case, I have added the link in my post above which shows you how to add filters while save a file. – Siddharth Rout Aug 19 '19 at 05:24
  • Thanks Siddharth. I will check that out. – Bill Black Aug 20 '19 at 16:51
1

Ok here is the answer. First, thanks to the above shots at solving for the issue as they did lead to me reviewing the documentation (again) and making another attempt. The first answer put me on to the issue as .Filters simply won't work with a msoFileSaveAs dialog. However, you can't type in a different file name on an Open dialog (either 1 or 3).

So the answer is to use msoFileSaveAs or (2) and use the .Initialfilename property to set the extension which, in this case, will show a list of excel files which can be selected, or selected and modified and then you can click on "Save"

Working code:

Private Sub Command93_Click()
Dim objFD As Object
Dim strOut As String


Set objFD = Application.FileDialog(2)

With objFD
'.Filters.Clear
'.Filters.Add "Excel File", "*.xls; *.xlsx", 1
.Initialfilename = "*.xlsx"
If .show = -1 Then
    strOut = objFD.selecteditems(1)
End If

End With

Set objFD = Nothing
Me.txtSaveFile = strOut
Me.Refresh
End Sub

Thanks all!

Bill Black
  • 21
  • 1
  • 4