Files in A Folder

[DMT Dave]

Hi Guy's what is the best to display files in a folder on a MsgBox ? but have an input box so the user can narrow the file name down ie:

MyInput = InputBox Enter Abbreviated File name
FileName = Like MyINput & "*.*"
Path "c:\Folder"
If Filename exists Then


FileToOpen = path & FileName & ".xlsx"

Or even better have another input box set as Integer
List the files found that match the search
Select case
1 = Open This File
2 = Open that file
I haven't written this code yet so there are no Dim's set

Thanks in advance

[Minty]

Use a list box rather than a message box.
Then use the listbox selected object as the file to open.

I use two functions to do this from Allen Browne's excellent resource http://allenbrowne.com/ser-59.html

Public Function ListFiles(strPath As String, Optional strFileSpec As String, _
    Optional bIncludeSubfolders As Boolean, Optional lst As ListBox)
    On Error GoTo err_handler
    'Purpose:   List the files in the path.
    'Arguments: strPath = the path to search.
    '           strFileSpec = "*.*" unless you specify differently.
    '           bIncludeSubfolders: If True, returns results from subdirectories of strPath as well.
    '           lst: if you pass in a list box, items are added to it. If not, files are listed to immediate window.
    '               The list box must have its Row Source Type property set to Value List.
    'Method:    FilDir() adds items to a collection, calling itself recursively for subfolders.
    Dim colDirList As New Collection
    Dim VarItem As Variant


    Call FillDir(colDirList, strPath, strFileSpec, bIncludeSubfolders)


    'Add the files to a list box if one was passed in. Otherwise list to the Immediate Window.
    If lst Is Nothing Then
        For Each VarItem In colDirList
            Debug.Print VarItem
        Next
    Else
        For Each VarItem In colDirList
            lst.AddItem VarItem
        Next
    End If


Exit_Handler:
    Exit Function


err_handler:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Exit_Handler
End Function


Private Function FillDir(colDirList As Collection, ByVal strFolder As String, strFileSpec As String, _
    bIncludeSubfolders As Boolean)
    'Build up a list of files, and then add add to this list, any additional folders
    Dim strTemp As String
    Dim colFolders As New Collection
    Dim vFolderName As Variant


    'Add the files to the folder.
    strFolder = TrailingSlash(strFolder)
    strTemp = Dir(strFolder & strFileSpec)
    Do While strTemp <> vbNullString
        colDirList.Add strTemp                   'strFolder & strTemp As I only want the files listed not the path.
        strTemp = Dir
    Loop


    If bIncludeSubfolders Then
        'Build collection of additional subfolders.
        strTemp = Dir(strFolder, vbDirectory)
        Do While strTemp <> vbNullString
            If (strTemp <> ".") And (strTemp <> "..") Then
                If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0& Then
                    colFolders.Add strTemp
                End If
            End If
            strTemp = Dir
        Loop
        'Call function recursively for each subfolder.
        For Each vFolderName In colFolders
            Call FillDir(colDirList, strFolder & TrailingSlash(vFolderName), strFileSpec, True)
        Next vFolderName
    End If
End Function