FileDialog.Filters.Add

[ARW17]

I'm using a FileDialog command to allow users to select a file from a certain folder. It's working, but I'd like to filter down to only files that contain the word "active". I tried .filters.add "active *", "*.csv", but I get files with other names that are also .csv. Eventually, this folder will be pretty full of files, so I'd like to make it easier for the user to find the file they need. As if I was typing "active" in the search box in File Explorer. Here's my code currently:

Function Get_File_Active()

'Declare a variable to contain FileDialog
Dim sMyPath As FileDialog
'Declare a variable to contain the path
Dim aPath As Variant


DoCmd.SetWarnings False
DoCmd.Hourglass True
' Set up the File Dialog.

Set sMyPath = Application.FileDialog(msoFileDialogFilePicker)

With sMyPath

' Do not allow users to make multiple selections in dialog box
.AllowMultiSelect = False

' Set the title of the dialog box.
.Title = "Select your Archived Active File"

' Clear out the current filters, and add your own. Looking for a file like "active 20211221.csv"
.Filters.Clear
.Filters.Add "active *", "*.csv"

'Go to the Archive folder.
.initialfilename = "K:\Customer Service Capacity Queue Data Tool\Queue Data Inputs\Archive"

'Set the text of the button Caption
.ButtonName = "Get Active File"

'Use the Show method to display the File Picker dialog box and return the user's action.
'The user pressed the action button.
If .Show = -1 Then
For Each aPath In .SelectedItems

'MsgBox aPath

'Import into Active table
DoCmd.OpenQuery "ACTIVE_DELETE"
DoCmd.TransferText acImportDelim, "active_import_spec_date", "active", aPath, False

Next aPath
Else
'Show if Canceled is selected in a message box
aPath = "No File Selected to Import."
MsgBox aPath
End If

End With
DoCmd.SetWarnings True
DoCmd.Hourglass False

'Gut check the upload numbers.
Dim MsgA, StyleA, TitleA, ResponseA
MsgA = "ACTIVE file was uploaded and contained " & DCount("*", "ACTIVE") & " records. A typical workday usually has around 70,000."
StyleA = vbOKOnly + vbInformation + vbDefaultButton1
TitleA = "IC1 File Uploads"
ResponseA = MsgBox(MsgA, StyleA, TitleA)


End Function

 

[Micron]

The filters you're trying to use are for file types, not what's in the file name. If what you're getting back is the entire file path, then you could use the Instr function to see if it contains the word you're looking for; e.g.
If Instr(apath, "active") > 0 Then << if true, the word was found. In case you need the reference for Instr

InStr Function - Microsoft Support

support.microsoft.com

If you would paste code within code tags, that would be nice, if you please. Easier to read and will hold proper indentation.

 

[jackd]

I have this routine using FileDialog. It allows me to enter a character or more and it gives a dropdown for selection.
I found it useful for selecting files when testing. May not be applicable to your need.

' ----------------------------------------------------------------
' Procedure Name: GetERDSqlFile
' Purpose: Sample proc to show use of filedialog.
' It doesn't do anything
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 15-Nov-21
' ----------------------------------------------------------------
Function GetERDSqlFile() As String

      'Name    Value   Description
      'msoFileDialogFilePicker        3   File Picker dialog box.
      'msoFileDialogFolderPicker      4   Folder Picker dialog box.
      'msoFileDialogOpen              1   Open dialog box.
      'msoFileDialogSaveAs            2   Save As dialog box.

          Dim fDialog As FileDialog, result As Integer
10        Set fDialog = Application.FileDialog(1) 'msofileDialogOpen
     
          'Optional: FileDialog properties
          
20        fDialog.Title = "Select a file"
30        fDialog.Filters.Add "Text Files", "*.txt", 1
40        fDialog.InitialFileName = ""
     
50        If fDialog.Show = -1 Then
60              GetERDSqlFile = fDialog.SelectedItems(1)
70            Debug.Print "You selected: """ & fDialog.SelectedItems(1) & """"
80        End If
End Function