filtering Application.GetOpenFilename

[SeniorTom]

I would like to display only certain .xl files, eg *roster*.xls* I have tried both sides of the filter and the best I get is all .xls*. Can I narrow it down to only files with roster in the name?

 ' Set up file listings
    FileInfo = "EXCELL files (*.xls*),*.xl*"
    ' Set Dialog Box caption
    Title = "         *****************************  Select the Roster file to use *****************************"
    'Display the .xl files to select the roster file
    ' shows all the .xl* files in local directory
    fullpath = Application.GetOpenFilename(FileInfo, , Title)

 

[tonyyy]

SeniorTom,

Instead of .GetOpenFilename, you might consider .FileDialog...

Sub LimitFileSelection()
Dim fd As FileDialog
Dim Surch As String
Dim i As Long
Dim wb As Workbook
Surch = "roster"

''''Open FileDialog and select file(s)
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
    .Title = "         *****************************  Select the Roster file to use *****************************"
    .InitialView = msoFileDialogViewList
    .AllowMultiSelect = True
    .InitialFileName = "*" & Surch & "*"
    If .Show = 0 Then Exit Sub
End With
''''Loop through selected file(s)
For i = 1 To fd.SelectedItems.Count
    Set wb = Workbooks.Open(fd.SelectedItems(i))
    ''''More code
    wb.Close savechanges:=False
Next i
End Sub

Cheers,

tonyyy

 

[SeniorTom]

Tonyyy,

Thanks, Just what I needed.

Tom

 

[tonyyy]

You're very welcome, Tom...

 

[SeniorTom]

Tonyyyy,

Your routine is great, but if the file is already open, I have a problem. I can check if it open but 'am having a problem setting the "set WB = fd.SelectedItems(i)" I get type mismatch. Can you guide me?

 

[tonyyy]

...if the file is already open, I have a problem.

What's the error number and description? And which line of code is highlighted when the error occurs?

 

[tonyyy]

..."set WB = fd.SelectedItems(i)" I get type mismatch...

The error occurs because "fd.SelectedItems(i)" returns the full file path, and "set WB" in this case is only looking for the file name. You might try...

For i = 1 To fd.SelectedItems.Count
    On Error Resume Next
        fName = fd.SelectedItems(i)
        fName = Right(fName, Len(fName) - InStrRev(fName, "\"))
        Set wb = Workbooks(fName)
    On Error GoTo 0
    
    If wb Is Nothing Then
        Set wb = Workbooks.Open(fd.SelectedItems(i))
        MsgBox "pause"
        ''''More code
        wb.Close savechanges:=False
    Else
        MsgBox wb.Name & " is open"
        ''''More code
    End If
Next i

 

[SeniorTom]

Perfect, You did it again!

Thanks,
Tom

 

[tonyyy]

You're welcome again, Tom...