Prompt to select certain files within a folder

[drom]

Hi and thanks in advance!

Imagine I have a Path and a String:

• wPath = "C:\Users\MrExcel\Desktop\Works"

• wName= "France"

I would like to prompt the user to select any Excel file within the Path = wPath containing the Name = wName


I know How to prompt the user to select within the Folder wPath only excel files , but I do not know how to show only the files containing the name = wName

So if the wPath has 50 Excel Files and only 4 of them containing the string "France"
eg:

• "2018-08-05 France Sales"

• "2018-10-11 France Customers"

• "Sales from France"

• "customers in France"

When prompting the User only show these Files

 

[NdNoviceHlp]

Not really clear on "show users only these files. Here's a start that seems like it should work...

Private Sub test()
Dim FSO As Object, FolDir As Object, FileNm As Object, WName As String
On Error GoTo Erfix
Set FSO = CreateObject("scripting.filesystemobject")
Set FolDir = FSO.GetFolder("C:\Users\MrExcel\Desktop\Works")
WName = "France"

Application.ScreenUpdating = False
For Each FileNm In FolDir.Files
If InStr(FileNm.Name, WName) Then
MsgBox FileNm.Name
'Workbooks.Open FileName:=FileNm
'Workbooks(FileNm.Name).Close SaveChanges:=False
End If
Next FileNm
Application.ScreenUpdating = True
Set FolDir = Nothing
Set FSO = Nothing
Exit Sub
Erfix:
On Error GoTo 0
MsgBox "Error"
Application.ScreenUpdating = True
Set FolDir = Nothing
Set FSO = Nothing
End Sub

HTH. Dave

 

[drom]

I do not want to know how many files contain the String "France" within a Folder

• I know how to do so

I would like to prompt the user to select a file inside a folder (I do know this) , but only show the files with the string France.

so stead of giving me the option to see 50 files within the folder I would like to see only 4 (according to my criteria= France)
And once I have only my 4 options... pick one of them

Probably this is not possible, but who knows

Not really clear on "show users only these files. Here's a start that seems like it should work...

Private Sub test()
Dim FSO As Object, FolDir As Object, FileNm As Object, WName As String
On Error GoTo Erfix
Set FSO = CreateObject("scripting.filesystemobject")
Set FolDir = FSO.GetFolder("C:\Users\MrExcel\Desktop\Works")
WName = "France"

Application.ScreenUpdating = False
For Each FileNm In FolDir.Files
If InStr(FileNm.Name, WName) Then
MsgBox FileNm.Name
'Workbooks.Open FileName:=FileNm
'Workbooks(FileNm.Name).Close SaveChanges:=False
End If
Next FileNm
Application.ScreenUpdating = True
Set FolDir = Nothing
Set FSO = Nothing
Exit Sub
Erfix:
On Error GoTo 0
MsgBox "Error"
Application.ScreenUpdating = True
Set FolDir = Nothing
Set FSO = Nothing
End Sub

HTH. Dave

 

[Fluff]

How about

Dim fname As String
With Application.FileDialog(3)
   .InitialFileName = "C:\Users\MrExcel\Desktop\Works\*france*"
   .AllowMultiSelect = False
    If .Show = -1 Then fname = .SelectedItems(1)
End With

 

[NdNoviceHlp]

drom my only thoughts are that U create a userform with a listbox that populates with the file names of interest. Opening the files would then occur with a listbox selection. I don't think U can adjust the Application.FileDialog filter to screen out parts of file names... but I've been wrong many times before. Dave