Teedee Access VBA Teedee 2010
File Dialog (Object)
Home Tutorial Code Snippets

File Dialog - Using FileDialog Object

There are many instances where you will require a file dialog to either open or save a file. In earlier versions of Access the best method was to use an API call; Office 2000 introduced the Office Object library containing FileDialog.

Requirements

Microsoft Office nn Object Library. This is available when MS Office is installed. Restrictions Cannot be distributed to users who do not have Office installed.

Code Snippet

Public Function OpenFileDialog(ReturnMulti As Boolean) As Variant       ' Requires reference to Microsoft Office 11.0 Object Library.     ' Returns NULL if cancelled         Dim fDialog As Office.FileDialog     Dim varFile As Variant     Dim varFilelist As Variant         ' Clear listbox contents.     varFilelist = ""         ' Set up the File Dialog.     Set fDialog = Application.FileDialog(msoFileDialogFilePicker)         With fDialog        ' Allow user to make multiple selections in dialog box        .AllowMultiSelect = ReturnMulti                     ' Set the title of the dialog box.        If ReturnMulti Then          .Title = "Please select one or more files"        Else          .Title = "Please select a file"        End If            ' Clear out the current filters, and add our own.        .Filters.Clear        .Filters.Add "Acrobat Reader Files (PDF)", "*.PDF"        .Filters.Add "Access Databases", "*.MDB"        .Filters.Add "Access Projects", "*.ADP"        .Filters.Add "All Files", "*.*"        .FilterIndex = 0               ' Show the dialog box. If the .Show method returns True, the        ' user picked at least one file. If the .Show method returns        ' False, the user clicked Cancel.        If .Show = True Then              'Loop through each file selected and add it to our list box.              For Each varFile In .SelectedItems                 If varFilelist <> "" Then varFilelist = varFilelist & ";"                 varFilelist = varFilelist & varFile              Next        Else           varFilelist = Null        End If        OpenFileDialog = varFilelist     End With End Function

Using the FileDialog Object

To use the FileDialog object it must be added to your References in Access. With the code editor open go to the Tools menu and click References. Scroll down and ensure that Microsoft Office nn Object Library is checked. Calling this function Private Sub btnBrowse_Click()     Dim varRtn As Variant         varRtn = OpenFileDialog(False)     If Not IsNull(varRtn) Then         Me.txtFilecopy = varRtn     End If End Sub Notes In the example above varRtn will contain either Null or a single file (including path) Changing to varRtn = OpenFileDialog(True) potentially returns a semi-colon delimited list of files. This example only scratches the surface of the FileDialog potential