Partial File Name FileFilter VBA Dialogue

Question

I have a directory with several TXT files. Let them talk

hi.txt hello.txt hello_test.txt test.txt 

Using the file dialog in VBA, how can I filter to show only "* test.txt" the relevant files (ie the last two) in the drop-down list? Or I can use only *. filters?

The following seems to work, but does not:

 Sub TestIt() Dim test As Variant 'silly vba for not having a return type.. test = Application.GetOpenFilename(FileFilter:="test (*test.txt), *test.txt") End Sub 

edit: clarification in case this is unclear: I want to filter "test.txt" instead of ".txt" files, so I can only choose from hello_test.txt and test.txt in chooser.

Answer 1

I see that you are worried about placing text in the file name field, but this is exactly what you need to do and, apparently, is the norm for your situation. I was hanged on the same topic.

This is what I used:

 Public Sub Browse_Click() Dim fileName As String Dim result As Integer Dim fs With Application.FileDialog(msoFileDialogFilePicker) .Title = "Select Test File" .Filters.Add "Text File", "*.txt" .FilterIndex = 1 .AllowMultiSelect = False .InitialFileName = "*test*.*" result = .Show If (result <> 0) Then fileName = Trim(.SelectedItems.Item(1)) Me!txtFileLocation = fileName End If End With 

Answer 2

What about filedialog?

 Dim dlgOpen As FileDialog Set dlgOpen = Application.FileDialog(msoFileDialogOpen) With dlgOpen .AllowMultiSelect = True .InitialFileName = "Z:\docs\*t*.*x*" .Show End With 

http://msdn.microsoft.com/en-us/library/aa213120(v=office.11).aspx

Answer 3

Is that what you are trying? Insert this into the module and run sub OpenMyFile

 Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _ "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long Private Type OPENFILENAME lStructSize As Long hwndOwner As Long hInstance As Long lpstrFilter As String lpstrCustomFilter As String nMaxCustFilter As Long nFilterIndex As Long lpstrFile As String nMaxFile As Long lpstrFileTitle As String nMaxFileTitle As Long lpstrInitialDir As String lpstrTitle As String flags As Long nFileOffset As Integer nFileExtension As Integer lpstrDefExt As String lCustData As Long lpfnHook As Long lpTemplateName As String End Type Sub OpenMyFile() Dim OpenFile As OPENFILENAME Dim lReturn As Long Dim strFilter As String OpenFile.lStructSize = Len(OpenFile) '~~> Define your filter here strFilter = "Text File (*test.txt)" & Chr(0) & "*test.txt" & Chr(0) With OpenFile .lpstrFilter = strFilter .nFilterIndex = 1 .lpstrFile = String(257, 0) .nMaxFile = Len(.lpstrFile) - 1 .lpstrFileTitle = .lpstrFile .nMaxFileTitle = .nMaxFile .lpstrInitialDir = "C:\Users\Siddharth Rout\Desktop\" .lpstrTitle = "My FileFilter Open" .flags = 0 End With lReturn = GetOpenFileName(OpenFile) If lReturn = 0 Then '~~> User cancelled MsgBox "User cancelled" Else MsgBox "User selected" & ":=" & OpenFile.lpstrFile ' '~~> Rest of your code ' End If End Sub