It is often required to prompt the operator of an Access project to select a file or folder. Sure, it's possible to allow them to type it in freehand, or even paste it in from another application, but generally operators are more comfortable with the facility to select it from the existing file structure as they would in most Windows applications (with a file/folder selectioon window).
This is perfectly possible to do in Access (and any MS Office application of course, but we'll deal with Access here for simplicity). The details of what to do and how to do it can be found below. Attached to this post you can find an example database to play with which includes all this already implemented. There is also an associated video at VBA Tip: Select File or Folder using FileDialog in Access (cc) .
Objects
A single object is necessary. To implement this facility you use the FileDialog object from the MS Office Object library. This requires that the library is included as a reference of the project. If you don't find it already selected in the Tools \ References list (in the VBA IDE or Code Editor window) then select it using that same command. Versions differ, but the full name will probably look similar to "Microsoft Office XX.X Object Library". You may have to scroll down a long way to find it but most of the items in the list are arranged alphabetically.
File or Folder
Selecting files and selecting folders are very similar, but I'll cover the difference here just to be clear.
When selecting a file the operator can choose a folder either way (from the Look in: drop-down or the main section where files/folders within current folder are listed) and it will continue navigating into that folder. It will only return a value when a file is selected.
When selecting a folder however, it is still possible to navigate the folder structure using the Look in: drop-down, and even by double-clicking on a folder from the main section. To return the folder to the calling code, it is necessary to activate the OK button with a particular folder selected from the main section.
Example Database
To illustrate the usage of this I will knock up a simple database with a form containing two controls for selecting :
- A folder.
- A file.
As I don't want to do anything to anyone's files, the payload will simply be to display the full path of the folder or file in the associated label by the Command Button.
Structure and Objects of Example Database
There is very little to this in truth (See picture below for what it looks like).
- A form called frmFileDialog which has on it, apart from a title Label, an Exit button and its associated Label, two Command Buttons called cmdFolder and cmdFile, and an associated label for each (lblFolder and lblFile).
- The module associated with the form which contains the VBA code supporting the various controls.
- Another module, modMain, which simply contains the code for FSBrowse().
VBA Code
Let's start with a routine that provides a simple interface for using this object. I've called it FSBrowse and it's a function procedure.
Expand|Select|Wrap|Line Numbers
- Option Compare Database
- Option Explicit
- 'FSBrowse (File System Browse) allows the operator to browse for a file/folder.
- ' strStart specifies where the process should start the browser.
- ' lngType specifies the MsoFileDialogType to use.
- ' strPattern specifies which FileType(s) should be included.
- Public Function FSBrowse(Optional strStart As String = "", _
- Optional lngType As MsoFileDialogType = _
- msoFileDialogFolderPicker, _
- Optional strPattern As String = "All Files,*.*" _
- ) As String
- Dim varEntry As Variant
- FSBrowse = ""
- With Application.FileDialog(dialogType:=lngType)
- 'Set the title to match the type used from the list
- .Title = "Browse for "
- Select Case lngType
- Case msoFileDialogOpen
- .Title = .Title & "File to open"
- Case msoFileDialogSaveAs
- .Title = .Title & "File to SaveAs"
- Case msoFileDialogFilePicker
- .Title = .Title & "File"
- Case msoFileDialogFolderPicker
- .Title = .Title & "Folder"
- End Select
- If lngType <> msoFileDialogFolderPicker Then
- 'Reset then add filter patterns separated by tildes (~) where
- ' multiple extensions are separated by semi-colons (;) and the
- ' description is separated from them by a comma (,).
- ' Example strPattern :
- ' "MS Access,*.ACCDB; *.MDB~MS Excel,*.XLSX; *.XLSM; *.XLS"
- Call .Filters.Clear
- For Each varEntry In Split(strPattern, "~")
- Call .Filters.Add(Description:=Split(varEntry, ",")(0), _
- Extensions:=Split(varEntry, ",")(1))
- Next varEntry
- End If
- 'Set some default settings
- .InitialFileName = strStart
- .AllowMultiSelect = False
- .InitialView = msoFileDialogViewDetails
- 'Only return a value from the FileDialog if not cancelled.
- If .Show Then FSBrowse = .SelectedItems(1)
- End With
- End Function
Next is the code behind the form which calls the function above. Very little to explain as the complexity level is so basic, but I'll include a few pointers below the code anyway.
Expand|Select|Wrap|Line Numbers
- Option Compare Database
- Option Explicit
- Private Sub Form_Open(Cancel As Integer)
- Call DoCmd.Restore
- With Me
- .lblFolder.Caption = Replace(CurrentProject.Path & "\", "\\", "\")
- .lblFile.Caption = CurrentProject.FullName
- End With
- End Sub
- Private Sub cmdFolder_Click()
- Dim strPath As String
- With Me
- strPath = FSBrowse(strStart:=.lblFolder.Caption, _
- lngType:=msoFileDialogFolderPicker)
- If strPath > "" Then _
- .lblFolder.Caption = Replace(strPath & "\", "\\", "\")
- End With
- End Sub
- Private Sub cmdFile_Click()
- Dim strPath As String
- With Me
- strPath = Left(.lblFile.Caption, InStrRev(.lblFile.Caption, "\"))
- strPath = FSBrowse(strStart:=strPath, _
- lngType:=msoFileDialogFilePicker, _
- strPattern:="MS Access Databases,*.ACCDB; *.MDB~" & _
- "All Files,*.*")
- If strPath > "" Then .lblFile.Caption = strPath
- End With
- End Sub
- Private Sub cmdExit_Click()
- Call DoCmd.Close
- End Sub
- Lines #4 - #10 Form_Open()
This restores the form (in case it's either maximised or minimised).
It also sets up the starting values of the labels that display both the folder (Line #7) and the file (Line #8). These values match, respectively, the folder and full name values for the current projuect. IE. The open database called
Notice that it is only necessary to use Replace() for the folder as folder addresses can be returned either with or without a trailing slash (/) character. This code ensures one is added if, and only if, none is there already. - Lines #12 - #21 cmdFolder_Click()
Lines #13 - #15 and #20 - #21 simply set up the code structure and don't actually do anything specific.
Lines #16 - 17 call the FSBrowse function shown above. The strStart parameter tells it where to start browsing from. In this case it is the same as originally set up in Form_Open() or the value last returned if it has been run before. The lngType parameter tells it to look for a folder.
Lines #18 - #19 simply save the returned value in the Caption of the label if if the FileDialog wasn't cancelled. Notice it also adds a back-slash (\) at the end, but only if there is not one there already. - Lines #23 - #34 cmdFile_Click()
Lines #23 - #26 and #33 - #34 simply set up the code structure and don't actually do anything specific.
Line #27 sets up the starting address. It ensures that the file name part is dropped otherwise the visible files are filtered to match only that name. Essentially it sets the value to match the folder part only of the last returned value from the label's Caption.
Lines #28 - 31 call the FSBrowse function shown above. The strStart parameter tells it where to start browsing from. In this case it is set by line #27. The lngType parameter tells it to look for a file. The strPattern parameter specifies which filters to make available for use. - Lines #36 - #38 cmdExit_Click()
Line #37 simply closes the current object which is the form.
Conclusion
This is actually a very easy thing to accomplish and to incorporate into any of your databases. Once the basics are understood there's no reason why it shouldn't be used anywhere and everywhere such a facility is called for.