Prompt to select certain files within a folder

drom

Well-known Member
Joined
Mar 20, 2005
Messages
543
Office Version
  1. 2021
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Not really clear on "show users only these files. Here's a start that seems like it should work...
Code:
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
 
Upvote 0
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...
Code:
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
 
Upvote 0
How about
Code:
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,774
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top