Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    225
    Thanks
    30
    Thanked 4 Times in 4 Posts

    How can I limit what files a user can select?

    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

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    219
    Thanks
    0
    Thanked 20 Times in 19 Posts
    Quote Originally Posted by alan sh View Post
    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

  4. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,205
    Thanks
    14
    Thanked 331 Times in 324 Posts
    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

  5. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,205
    Thanks
    14
    Thanked 331 Times in 324 Posts
    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
    Attached Files Attached Files

  6. #5
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    219
    Thanks
    0
    Thanked 20 Times in 19 Posts
    Quote Originally Posted by sdckapr View Post
    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

  7. The Following 2 Users Say Thank You to jeremybarker For This Useful Post:

    RetiredGeek (2014-01-15),sdckapr (2014-01-15)

  8. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,205
    Thanks
    14
    Thanked 331 Times in 324 Posts
    Thanks Jeremy,
    Much simpler than my approach. It just shows it is never to late to learn some new techniques...

    Steve

  9. #7
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    225
    Thanks
    30
    Thanked 4 Times in 4 Posts
    Thanks both. I'll play !!!

    regards

    Alan

  10. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,058
    Thanks
    196
    Thanked 764 Times in 698 Posts
    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 ). I hope the OP and others reading this thread find the code useful.
    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
    Last edited by RetiredGeek; 2014-01-16 at 05:17.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  11. #9
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    225
    Thanks
    30
    Thanked 4 Times in 4 Posts
    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

  12. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,058
    Thanks
    196
    Thanked 764 Times in 698 Posts
    Alan,

    To paraphrase Yoda, "Correct you are."

    However, I can because I did but I shouldn't have!ROTFLOL.gif

    I've fixed it.
    Last edited by RetiredGeek; 2014-01-16 at 05:19.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  13. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,205
    Thanks
    14
    Thanked 331 Times in 324 Posts
    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...

    Steve

  14. #12
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,474
    Thanks
    22
    Thanked 168 Times in 164 Posts
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •