How can I limit what files a user can select?

Topic

New Reply

I have a macro which asks the user for a file using:

Code:

paramname = Application.GetOpenFilename(“Excel Files (*.xlsx), *.xlsx”, , “Enter name of parameter file”)

That shows all the “.XLSX” files in the last used directory. What I want to do is limit it so they only see a subset of these, starting with “GR”. So, in DOS, you would do “Dir GR*.XLSX” and get the list back. When I try

Code:

paramname = Application.GetOpenFilename(“Excel Files (GR*.xlsx), GR*.xlsx”, , “Enter name of parameter file”)

It still returns the full list of all XLSX files in that directory.

So, what am I doing wrong?

Cheers

Alan

Reply | Quote

Replies

I have a macro which asks the user for a file using:

Code:

paramname = Application.GetOpenFilename(“Excel Files (*.xlsx), *.xlsx”, , “Enter name of parameter file”)

That shows all the “.XLSX” files in the last used directory. What I want to do is limit it so they only see a subset of these, starting with “GR”. So, in DOS, you would do “Dir GR*.XLSX” and get the list back. When I try

Code:

paramname = Application.GetOpenFilename(“Excel Files (GR*.xlsx), GR*.xlsx”, , “Enter name of parameter file”)

It still returns the full list of all XLSX files in that directory.

So, what am I doing wrong?

Cheers

Alan

Alan, I’m not sure that GetOpenFilename supports file filters other than by specifying the extension. If you have Office 2007 or later you might want to check out the FileDialog object which has extended properties which may help.

Regards

Reply | Quote

I concur with Jeremy that the GetOpenFilename does not support more than an extension filter. The FileDialog can help by entering a starter name, but i do not believe this allows wildcards nor do I think it filters the display.

An idea that comes to mind if no one else has a direct method, is creating a userform with a listbox,
then in the code have the user select a folder [this could be hard-coded if desired] and then do the filtering on that folder by looping through the files with DIR(), and putting the names into the listbox, then call the userform and have the user select from the created list.

Steve

Reply | Quote

You might look at the attached. This may be adaptable to what you want.

The main code is:

Code:

Option Explicit
Global gsFolderPath As String
Global gsFilter As String
Global gsWkb As String

Sub FilterFileList()
  Dim sFile As String
  Dim sWorkbook As String
  gsFilter = "Gr*.xlsx"
  With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Select the Folder..."
    If .Show Then
      gsFolderPath = .SelectedItems(1)
    Else
      MsgBox "No Folder Path Selected"
      Exit Sub
    End If
  End With

  If Dir(gsFolderPath & "" & gsFilter) = "" Then
    MsgBox "There are no files of the type:" & vbCrLf & _
      gsFolderPath & "" & gsFilter
    Exit Sub
  Else
    frmFilteredFiles.Show
    frmFilteredFiles.Hide
    MsgBox "You selected the file:" & vbCrLf & gsWkb & vbCrLf & _
      vbCrLf & "from the folder:" & vbCrLf & gsFolderPath
  End If
End Sub

The code in the userform is:

Code:

Option Explicit

Private Sub UserForm_Activate()
  Dim sFile As String
  Me.lblFolder.Caption = gsFolderPath & "" & gsFilter
  Me.ListBox1.Clear
  sFile = Dir(gsFolderPath & "" & gsFilter)
  Do While sFile  ""
    Me.ListBox1.AddItem sFile
    sFile = Dir
  Loop
End Sub

Private Sub ListBox1_Click()
  gsWkb = Me.ListBox1.Value
  Me.Hide
End Sub

Just press the button on the sheet to select the folder, and display the filtered list.

Steve

Reply | Quote

I concur with Jeremy that the GetOpenFilename does not support more than an extension filter. The FileDialog can help by entering a starter name, but i do not believe this allows wildcards nor do I think it filters the display.

An idea that comes to mind if no one else has a direct method, is creating a userform with a listbox,
then in the code have the user select a folder [this could be hard-coded if desired] and then do the filtering on that folder by looping through the files with DIR(), and putting the names into the listbox, then call the userform and have the user select from the created list.

Steve

Hi Steve, I did some playing on Excel 2007 last night and the following code allows some of the desired functionality:

Code:

Sub GFN()
    Dim fd As FileDialog
    
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .Filters.Add "Spreadsheets", "*.xlsx, *.xls", 1
        .InitialFileName = "P*.xls*"
        .Show
    End With
End Sub

This does work to restrict the file names presented.
However, I don’t think you can have multiple initial file name specs like P*.xls and P*.jog – my example “cheats” by covering .xls .xlsx and .xlsm for example.
Also, if the InitialFileName is “looser” than the filter then the InitialFileName wins – P*.* would show files other than Excel files, even though the Filter setting appears to restrict this.

Jeremy

Reply | Quote

Thanks Jeremy,
Much simpler than my approach. It just shows it is never to late to learn some new techniques…

Steve

Reply | Quote

Thanks both. I’ll play !!!

regards

Alan

Reply | Quote

Jeremy,

Nice code! It got me thinking, a dangerous thing, so I did some research and came up with this general (well mostly) function for doing what the OP wants that allows passing arguments so you can set parameters in the calling procedure which can be of use if you call the function from multiple places in your code. The one thing I could not find out how to accomplish was to keep the dialog box from showing directories (to keep nosy users from looking where you don’t want them to look :lol:). I hope the OP and others reading this thread find the code useful. :cheers:
Function:

Code:

Option Explicit
'                        +--------------------+                 +----------+
'------------------------|   GetFileToOpen()  |-----------------| 01/15/14 |
'                        +--------------------+                 +----------+
'Called by  :
'Arguments  : zSelected - a String array declared empty & ReDimed to 1
'             zExts     - a list of allowed extensions for the filter
'                         Ex: "*.xlsx, *.xls, *.xlsm, *.xlsb"
'                         Note: Only Excel filetypes as function is written!
'             zMulti    - True allows multi select, False allows single select.
'             zFileFilter - Optional - used to limit the files shown by name
'                         pattern, EX: "CA*.xls*" if ommited "*.xls*" will
'                         be used. Note: using "*.*" will over ride the zExts
'                         filter! You can also specify a drive/path to set
'                         the initial folder displayed.
'Notes      : You can uncomment the .Title line and supply your own
'             dialog box title and add an argument if you want to pass it!
'             You can uncomment the .ButtonName to supply a custom OK button
'             caption which can also be passed by argument if desired.


Function GetFileToOpen(ByRef zSelected, zExts As String, bMulti As Boolean, _
                       Optional zFileFilter As Variant) As Long

    Dim fd             As FileDialog
    Dim lCnt           As Long
    
    If IsMissing(zFileFilter) Then zFileFilter = "*.xls*"
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .Filters.Clear   '*** Clear old filters just precautionary ***
        .Filters.Add "Spreadsheets", zExts, 1
        .InitialFileName = zFileFilter  '*** File Name Filter control. ***
'        .Title = "Your Dialog Box Title Here"
'        .ButtonName = "OK button caption"
        .AllowMultiSelect = bMulti 'Note: if not specified defaults to True!
        
        '.Show  Returns: -1 if Open button or 0 if Cancel button is pushed!
        If .Show = -1 Then
        
          ReDim zSelected(.SelectedItems.Count) 'Make array the proper size.
          
          For lCnt = 1 To .SelectedItems.Count 'Load the array with selections.
             zSelected(lCnt) = .SelectedItems.Item(lCnt)
          Next lCnt
        
        End If
        
        GetFileToOpen = .SelectedItems.Count
        
    End With   'fd
    
End Function 'GetFileToOpen

Sample code to call and test the function:

Code:

'*** Code to test the GetFileToOpen Function ***
Sub Main()

   Dim zFileItems()   As String
   Dim zSelectedFiles As String
   Dim zAllowedExts   As String
   Dim lNoFiles       As Long
   Dim lCnt           As Long

   ReDim zFileItems(1)
   zAllowedExts = "*.xlsx, *.xls, *.xlsm, *.xlsb"
   lNoFiles = GetFileToOpen(zFileItems, zAllowedExts, False, "CA*.xls*")
   
   If lNoFiles > 0 Then
     For lCnt = 1 To lNoFiles
        zSelectedFiles = zSelectedFiles & zFileItems(lCnt) & vbCrLf
     Next
     
     MsgBox "You Selected: " & Format(lNoFiles, "##") & " Item(s)." _
            & vbCrLf & zSelectedFiles, vbOKOnly, _
            "Selected item(s):"
   End If
   
End Sub

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

I’ve used Jeremy’s code and it works a treat.

I’ll have a play with RG’s stuff later. But (Grammar Police here), you can’t say “You’re Dialog Box Title Here” – it is “Your”…..:)

cheers

Alan

Reply | Quote

Alan,

To paraphrase Yoda, “Correct you are.”

However, I can because I did but I shouldn’t have!35623-ROTFLOL

I’ve fixed it. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

However, I can because I did but I shouldn’t have!

But you didn’t “say it”, you only had it listed in code. Saying and typing are entirely different…:o

Steve

Reply | Quote

If you wanted to have multiple file filters, you can adapt Steve’s code.

In Steve’s post#4

..you could adapt it and use

gsFilter1 = “Gr*.xlsx” ‘first file filter choice
gsFilter2 = “z*.xlsb” ‘second filter choice for files

and then use something like..

zTest1 = Dir(gsFolderPath & “” & gsFilter1)
zTest2 = Dir(gsFolderPath & “” & gsFilter2)
If zTest1 = “” Then
MsgBox “There are no files of the type:” & vbCrLf & _
gsFolderPath & “” & gsFilter1
end if

If zTest2 = “” Then
MsgBox “There are no files of the type:” & vbCrLf & _
gsFolderPath & “” & gsFilter2
end if

if zTest1 & zTest2 = “” then
MsgBox “There are no files of either type:” & vbCrLf & _
gsFilter1 & ” and ” gsFilter2 & vbcr & _
” in folder:” & vbcr & gsFolderPath
end if

..and then use something like

sFile = Dir(gsFolderPath & “” & gsFilter1)
Do While sFile “”
Me.ListBox1.AddItem sFile
sFile = Dir
Loop
sFile = Dir(gsFolderPath & “” & gsFilter2)
Do While sFile “”
Me.ListBox1.AddItem sFile
sFile = Dir
Loop

et cetera et cetera et cetara

zeddy

Reply | Quote