Link to home
Start Free TrialLog in
Avatar of mlcktmguy
mlcktmguyFlag for United States of America

asked on

Using Multiple Filters (XLS and XLSX) in File selection dialog

Using VBA I would like to create a file selection routine that would show the user all  the files in the specified path, with the specified file extensions.

For example:  
path="C:\filestorage\"   allowable file extensions are 'xls' and xlsx'
or
path="C:\filestorage\"   allowable file extensions are 'csv' and 'txt'

The routine would present the user file with an explorer listing of the specified path but only show files with the allowable extensions.
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Rgonzo1971
Rgonzo1971

Hi,

pls try
Sub Main() 
 
 'Declare a variable as a FileDialog object. 
 Dim fd As FileDialog 
 
 Set fd = Application.FileDialog(msoFileDialogFilePicker) 
 

 Dim vrtSelectedItem As Variant 
 
 With fd 

 .Filters.Add "Excel", "*.xls; *.xlsx", 1 
 
 If .Show = -1 Then 
 
 For Each vrtSelectedItem In .SelectedItems 
 MsgBox "Selected item's path: " & vrtSelectedItem 
 Next vrtSelectedItem 
 'If the user presses Cancel... 
 Else 
 End If 
 End With 
 
 'Set the object variable to Nothing. 
 Set fd = Nothing 
 
End Sub

Open in new window

for reference
https://msdn.microsoft.com/en-us/library/office/ff860295.aspx

regards
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mlcktmguy

ASKER

Thanks to all.  I'm testing the suggestions and they work.

Scott's additional suggestion ".Filters.Add "Excel", "*.xl*", 1 " accomplished getting both "xls" and "xlsx" in the same view, which is what I was looking for in spreadsheets.  That one works because the string '"xl' is common to both extensions.

Is there a single filter that will present multiple un-like extensions, such as "csv" and "txt"  in the same select box.  I realize using Pat's original suggestion I can create multiple unique filters for those.  However, that requires the user to click the 'File Type' dropdown and select another filter.  Some of my users are probably not going to remember that.  Just trying to save myself "I don't see my file in the selection box" phone calls.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial