Search for string using getopenfilename (code included)

[Brandon M]

Is it possible to search for a specific string while using the getopenfilename function? I'm just trying to narrow down the files in the folder to be more relevant to the user. Currently my code takes a few inputs from the user and then opens windows explorer to let them locate the files they want. But the folder contains too many irrelevant files. I'd like to allow the user to use an input box to enter a string which would allow them to see only files containing that string.

for example, say a directory contains 9 files:

RT5-CMP-8554-014-9500.txt
RT5-CMP-8554-014-9600.txt
RT5-CMP-8554-014-9800.txt
RT5-CMP-8554-014-10000.txt
RT5-CMP-8554-014-10500.txt
RT5-CMP-8554-020-9500.txt
RT5-CMP-8554-020-9600txt
RT5-CMP-8554-020-9700.txt
RT5-CMP-8554-020-11500.txt


I would like for the user to be able to enter 014 into an input box and the result would be that they only see the files containing that string:

RT5-CMP-8554-014-9500.txt
RT5-CMP-8554-014-9600.txt
RT5-CMP-8554-014-9800.txt
RT5-CMP-8554-014-10000.txt
RT5-CMP-8554-014-10500.txt


The code below seems to work properly, but there is no attempt to filter the data with anything other than .txt files. I can't find anything that even gets me close. Any help is much appreciated.

Sub ImportData()
Dim Path As String
Dim fnameandpath As Variant
Dim WBt As Workbook
Dim WBf As Workbook
Dim WSt As Worksheet
Dim WSf As Worksheet
Dim RPM As Variant
Dim DS As Variant
 
Worksheets("Data").Activate
Set WBt = ActiveWorkbook

'***********************
'Ask for Data Set and RPM site
'***********************
DS = InputBox("Please provide Data Set")
If DS = "" Then
    MsgBox ("No Data Set Provided")
    strcancel = "Y"
    Exit Sub
End If

RPM = InputBox("Please provide RPM site.")
If RPM = "" Then
    MsgBox ("No RPM site Provided")
    strcancel = "Y"
    Exit Sub
End If

' ************************************************
' Select Text File containing data
' ************************************************
strFilt = "Text files (*.txt), *.txt"
intFilterIndex = 2
strDialogueFileTitle = "Select Data Set"
ChDrive "C:"
fnameandpath = Application.GetOpenFilename(filefilter:=strFilt, FilterIndex:=intFilterIndex, Title:=strDialogueFileTitle)

If fnameandpath = False Then exitall = True

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
   
' ************************************************
' Exit If No File Selected
If fnameandpath = "" Then
    MsgBox ("No File Selected")
    strcancel = "Y"
    Exit Sub
    
ElseIf fnameandpath = "False" Then
    MsgBox ("Operation Canceled")
    strcancel = "Y"
    Exit Sub
    exitall = True
    'GoTo line1
End If
'*************************************************
'*************************************************

Application.DisplayAlerts = False
'On Error Resume Next
Workbooks.OpenText Filename:=fnameandpath, DataType:=xlDelimited, Tab:=True, Semicolon:=False, Local:=True
Set WBf = ActiveWorkbook
Set WSf = WBf.ActiveSheet

WBt.Activate
Set WSt = WBt.Sheets(RPM)
WSt.Activate
With WSt
    Range("A5:KZ10000").Clear
    .Range("A5:KZ10000").Value = WSf.Range("A1:KZ10000").Value
End With
WBf.Close savechanges:=False
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
End Sub

 

[Brandon M]

Anyone? Any ideas or workarounds would help me out a lot.

TIA

 

[tonyyy]

Brandon M,

Rather than the .GetOpenFilename method, you might consider using .FileDialog(msoFileDialogFilePicker)...

Sub LimitFileSelection()
Dim fd As FileDialog
Dim Surch As String

Surch = InputBox("Please enter a search term.", Title:="Optional:")

''''Open FileDialog and select file(s)
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
    .InitialView = msoFileDialogViewList
    .AllowMultiSelect = True
    .InitialFileName = "*" & Surch & "*"
    If .Show = 0 Then Exit Sub
End With

End Sub

Cheers,

tonyyy

 

[Brandon M]

That's exactly the route I went.

Thanks for the reply, tonyyy!

 

[tonyyy]

You're welcome...