Replace Your File API’s With The FileDialog Object

Finding An MDB File

As I have alluded to in previous articles that I have written for Smart Access, I always like to write to develop a wrapper (see Sidebar) function or class module to reduce the complexity of an object such as FileDialog or Excel. In the case of the re-linking exercise, I require the user to select the location of the backend database. The first example that I’ve setup (see figure 1, button 1) asks the user to locate the Northwind database by using the FindDatabase wrapper function. As you can see there are only a few lines of code needed to achieve this.


Private Sub cmdFindDb_Click()
'This subroutine shows how to find an MDB file using the
'Access 2002 FileDialog object.

Dim thisDbfolder As String
Dim FileSelected As String

'Find the folder that this database is in
thisDbfolder = GetDBasePath_FX

'Open a function that calls the file dialog object
FileSelected = FindDatabase("northwind.mdb", thisDbfolder)

If Len(FileSelected) > 0 Then
  MsgBox "The path to Northwind is " & FileSelected
Else
  MsgBox "No file was selected"
End If

End Sub

  << Click to enlarge
Figure 2. The file picker that is enables by the FileDialog object.


Testing In The Immediate Window

The FindDatabase function is one of two wrapper functions that I have included with my samples. The other function (called FindAFile) allows you to pick any file using the FileDialog object. I explain this second function later in the article. With both functions, all arguments are optional to provide flexibility. The eight examples shown in the Immediate window (see Figure 3) highlight the variety of arguments you can use. You may note that the Immediate window (press keys Ctrl+G to display) allows comments as well.

  << Click to enlarge
Figure 3. Different ways to use the FileDialog wrapper functions.


Find A Database Using The FileDialog Object

Now the time has come to show you how to use the FileDialog object. In the following example, I demonstrate the wrapper function that returns the database location. The first half of the function prepares the FileDialog object and sets up all the properties suited to the type of file picker that we want. The code also manages issues relating to the optional arguments of the FindDatabase function.

Function FindDatabase(DataBaseName As Variant, _
Optional FolderPath As Variant) As String

'Display file dialog box to help you find the database

'Declare a variable as a FileDialog object.

Dim fd As FileDialog

On Error GoTo FindDatabase_Error

'Create a FileDialog object for File selections.
Set fd = Application.FileDialog( _
msoFileDialogFilePicker)

'Declare a variable to contain the path of each
'selected item. Even though the path is a String,
'the variable must be a Variant
'because For Each...Next routines only work with
'Variants and Objects.

Dim vrtSelectedItem As Variant

If IsMissing(DataBaseName) Then
  DataBaseName = "*.mdb"
End If
If IsMissing(FolderPath) Then
  FolderPath = ""
Else
If Right(FolderPath, 1) <> "\" Then
  FolderPath = FolderPath & "\"
End If
End If

With fd
.Filters.Clear
.InitialFileName = FolderPath & DataBaseName
.InitialView = msoFileDialogViewDetails
.Filters.Add "All databases", "*.mdb"
.AllowMultiSelect = False

Once you have the FileDialog properties as you would like them, the Show method displays the file dialog picker. If the user then hits the Cancel button (see Figure 2), the Show method returns False. Otherwise a list of files is returned to the FileDialog object that you instantiated (ie fd). The software then establishes the selected file by iterating a loop once. Note that the user is only able to select one file because we set the AllowMultiSelect property to false.

'Use the Show method to display the File Picker
'dialog box and return the user's action.
If .Show = True Then
  'The user pressed the action button.

  'Loop through all files selected (even if 1)
  For Each vrtSelectedItem In .SelectedItems
    'vrtSelectedItem is a String that contains
    'the path of each selected item.
    'You can use any file I/O functions that you
    'want to work with this path.
    'if AllowMultiSelect is false, this loop will
    'only run once
    FindDatabase = vrtSelectedItem
  Next vrtSelectedItem
Else
  'The user pressed Cancel.
End If

End With

FindDatabase_Exit:

  'Set the object variable to Nothing.
  Set fd = Nothing
  Exit Function

FindDatabase_Error:

  MsgBox "Error in FindDatabase function {No. " & _
  Err.Number & " } " & Err.Description
  FindDatabase = ""
  GoTo FindDatabase_Exit

End Function

Finding Any File With The File Dialog Box (Filters)

In the second wrapper function, I have expanded the functionality to allow the user to select any single file. This function highlights how optional arguments are useful in wrapper functions.

Function FindAFile(Optional FileName, _
Optional FolderPath, Optional FileType) As String

Dim fd As FileDialog
Const ALLFILES = "All Files"
Const ALLFILETYPES = "*.*"

On Error GoTo FindAFile_Error

'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog( _
msoFileDialogFilePicker)

Dim vrtSelectedItem As Variant

If IsMissing(FileName) Then
  FileName = ""
End If

If IsMissing(FolderPath) Then
  FolderPath = ""
Else
  If Right(FolderPath, 1) <> "\" Then
    FolderPath = FolderPath & "\"
  End If
End If

'Use a With...End With block to reference the
'FileDialog object.
With fd
  .Filters.Clear
  .InitialFileName = FolderPath & FileName
  .InitialView = msoFileDialogViewDetails
  If Not IsMissing(FileType) Then
    'Add the filter supplied through the function.
    .Filters.Add "Files Required", FileType
  End If
FileType = ALLFILETYPES
.Filters.Add ALLFILES, ALLFILETYPES

.AllowMultiSelect = False

In the code above, I would like to draw attention to the Add method of the Filters property. In this example, the user can select from the file type provided by your software plus a second “*.*” choice, which is added as a second filter. You might also be aware that you can use wildcards in the FileName argument as shown in line 2 of Figure 3.

Finding More Than One File

The FileDialog object is quite versatile and has some cool extensions that are worth exploring. One thing that I use the FileDialog object for is to select the file paths of multiple picture files. In Figure 4, not only have I turned on multi-select but also I have changed the the file picker to preview mode to see samples of the images before I select them. Unfortunately, for you guys, I have included a picture of myself which will definitely reduce the circulation of Smart Access for a while.

  << Click Picture to view
Figure 4. A picture of yours truly and 2 other files are selected using the FileDialog object.

So what do we do differently to setup this type of file picker (see vb that follows).
• Use the multiple types of image files defined by the IMAGETYPES constant.
• Change the InitialView property to msoFileDialogViewPreview so that we can see pictures and other viewable objects.
• Set the AllowMultiSelect property to True

Dim fd As FileDialog, lstStr As String
Const IMAGEFILES = "Image Files"
Const IMAGETYPES = "*.jpg;*.bmp;*.gif;*.tif"

On Error GoTo cmdAddFilesToList_Error
lstStr = ""

'Create a FileDialog object as a File Picker.
Set fd = Application.FileDialog( _
msoFileDialogFilePicker)

Dim vrtSelectedItem As Variant

With fd
  .Filters.Clear
  .InitialView = msoFileDialogViewPreview
  .InitialFileName = ""
  .Filters.Add IMAGEFILES, IMAGETYPES

Now we loop through the pictures selected and display the results in a listbox. To do this you need to set the RecordSourceType property of the list box to Value List and then build a concatenated string where a semi colon separates the file paths. The list box shown in Figure 1 displays the selections from Figure 4.

  If .Show = True Then
    lstStr = ""
    For Each vrtSelectedItem In .SelectedItems
      lstStr = lstStr & vrtSelectedItem & ";"
    Next vrtSelectedItem
  Else
    'The user pressed Cancel.
  End If
End With

cmdAddFilesToList_Exit:

'Set the object variable to Nothing.
  Set fd = Nothing

  lstFilesSelected.RowSource = lstStr
Exit Sub

A New Way To Parse The Directory Path Of Your Database

Deep down in the code used in the first example, I utilize a function called GetFilePath_FX. This function is off interest to developers because the StrReverse function introduced in Access 2000 and Visual Basic 6 is used.

Function GetDBasePath_FX() As String

Dim strPath As String
strPath = CurrentDb.Name
GetDBasePath_FX = GetFilePath_FX(strPath)

End Function

Function GetFilePath_FX(FilePathStr As String) _
As String

GetFilePath_FX = left$(FilePathStr, Len(FilePathStr) _
- InStr(StrReverse(FilePathStr), "\") + 1)

End Function

This StrReverse function is useful because it simplifies the more long-winded GetDbPath function that is popular amongst Access developers. Please note that I have split the functionality into two subroutines for greater flexibility (see above). The original GetDbPath function follows

Function GetDBPath() As String

Dim strPath As String
Dim intLastSlash As Integer

strPath = CurrentDb.Name
For intLastSlash = Len(strPath) To 1 Step -1
  If Mid(strPath, intLastSlash, 1) = "\" Then
    Exit For
  End If
Next intLastSlash
GetDBPath = left(strPath, intLastSlash)

End function

Summing Up

Some of you lucky programmers have probably been programming with Access 2002 for more than a year now. I have too but unfortunately our development being with databases in 2000 format. Now our clients are waking from their slumbers and starting to open those new Access 2002+ software boxes. This means it’s time to start abandoning that old API file picker code and get into the FileDialog object. As a bonus, learning about this object will help you with VB.net as it’s included in that development environment.
 

Useful Further Reading and Resources

Search your Access 2002/2003 Visual Basic help for "FileDialog". 

The Access solutions database demonstrates many useful programming tricks. Unfortunately, only some of that original software exists in the 2002 release and then only in the expensive Microsoft Office Developer libraries. You can still download the full older versions of the database from Microsoft and I have setup a web page that tells you all about it.

http://www.vb123.com/toolshed/99_free/solutions.htm

Otherwise, go to http://www.vb123.com/search  and type “solutions”

Tony reported that he couldn;t get File Dialog to work with Access runtime. If you try the following link into Google Groups, you will find a lot of people have had the same problem. 

Author Bio.
Garry Robinson is the founder of GR-FX Pty Limited, a company based in Sydney, Australia. If you want to keep up to date with the his latest postings on Access Issues, visit his companies web site at http://www.vb123.com/ and sign up for his Access email newsletter. If you like the approach Garry took on this article, why not try out the new version of Graf-FX which uses remote queries to graph data from any remote table or query . When Garry is not sitting at a keyboard, he can be found playing golf or exploring the remote National Parks that surround Sydney. Contact details … Click Here

Other Pages On This Site You Might Like To Read

Consolidate your data with queries
Remote Queries In Microsoft Access
Uncorrupt your Access database - plenty of options
IT Departments and Microsoft Access ~ Developers Have Their Say
Implementing a Successful Multi-user Access/JET Application

Click on the following button to jump to the next page in the document loop.

Links >>>  Home | Search | Workbench | Orders | Newsletter | Access Security | Access professionals