Choose one or more files in Access (or other VBA) 2010

article #412, updated 1895 days ago

The below is revised from here. It works well, as long as a reference to the Microsoft Office [currentversion] Type Library is created. This is confirmed to work well in Access 2010.

Public Function ChooseFile() As String

    With Application.FileDialog(msoFileDialogFilePicker) 
        .AllowMultiSelect = False 'Select only one file
        .Title = "Choose file" 'Set dialog title
        .ButtonName = "Choose" 'Set the button caption
        '.Filters.Clear 'Make sure the filter list is clear
        'Add 2 filters
            '.Filters.Add "JPEGs", "*.jpg"
            '.Filters.Add "Bitmaps", "*.bmp"
            '.FilterIndex = 2 ' Set the filter index to 2
            '.Filters.Add "All", "*.*"

        'Set initial path
        .InitialFileName = "" 

        'Optionally show files as thumbnails
        '.InitialView = msoFileDialogViewThumbnail
        .InitialView = msoFileDialogViewList

        'Show the dialog and test the return
        If .Show = 0 Then
            'didn't pick a file - exit sub
            ChooseFile = ""
            Exit Function
        End If

        'Should be only one file name - grab it
        ChooseFile = Trim(.SelectedItems(1))  

        'On Error Resume Next 'Set error trap

    End With

End Function

Categories:   Scripting