Display Open and Save As Dialog Boxes in Access with API Functions

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Display Open and Save As Dialog Boxes in Access with API Functions

by Deborah L. Cooper and Sean Kavanagh

Application: Microsoft Access 97/2000/2002

In the February 2001 article "Incorporate Windows dialog box functionality in your applications" we discussed using the Common Dialog ActiveX control COMDLG32.OCX to display the Open and Save As dialog boxes, among others. Unfortunately, as mentioned in the article, there are problems with using the control. Version conflicts can easily arise when you move your application between systems and you need to ship the control along with your Access file.

In this article, we'll show you how to display an Open or Save As dialog box using API functions. Using the API functions improves the overall performance of your application without the cost of using the COMDLG32.OCX control itself, or the hassle of distributing the control to a user's machine.

Displaying dialog boxes without an OCX

After discussing the dialog boxes we'll work with, and the API functions required to display them, we'll look at the details of the OPENFILENAME dialog box type, which can be thought of as a template for creating dialog boxes. To simplify displaying dialog boxes, we'll create a class module, which lets you create a custom object with its own properties and methods. In addition, the class module will contain the user-defined type specifics for creating an OPENFILENAME structure. To demonstrate how to pull everything together, we'll set up a simple form with command buttons on it that launch two dialog boxes. Once you make a selection with a dialog box, the result will be displayed in a message box to confirm that the information was captured.

The dialog boxes we'll work with

The Open dialog box lets you select a file or group of files to work with. You can specify both the drive and directory that contain the files you want to open. Similarly, the Save As dialog box lets you select the name of a file to save. Again, you have complete control over the drive and directory to use when saving the file.

It's important that you understand that using the dialog boxes doesn't automatically save your data to disk or retrieve a file's contents into your application. The two dialog boxes simply provide a user interface that allows you to select a filename to work with. Any subsequent action, such as saving a file or opening a file, must be taken care of through code in your own application.

Using the API functions

You display an Open dialog box by calling the GetOpenFileName function, and display a Save As dialog box by calling the GetSaveFileName function. Both functions require a pointer to a pre-initialized OPENFILENAME structure.

The OPENFILENAME structure tells Windows how to initialize the dialog box when it's displayed. To work with an OPENFILENAME structure, you need to declare the structure as a user-defined type and specify the individual structure elements. Information about the dialog box (such as what file the user selects) is stored in the OPENFILENAME structure when the dialog box is closed. Your application can then process this information as desired. Although we won't work with all of the OPENFILENAME elements, you must declare them all. If you're interested in the details of the structure, refer to the PDF on our FTP site.

The GetOpenFileName function returns a True value if a user selects a file. If the user clicks the Cancel or Close buttons, the GetOpenFileName function returns a False value. A False value is also returned if the lpstrFile element in the OPENFILENAME structure isn't large enough to store the name of the selected file. The GetSaveFileName function returns the exact same results.

After calling the GetOpenFileName or GetSaveFileName functions, the OPENFILENAME structure contains the information about the file(s) selected by the user. To simplify working with the OPENFILENAME structure and the GetOpenFileName and GetSaveFileName functions we'll encapsulate them all in a custom class called CommonDialogAPI.

The CommonDialogAPI class

Using the CommonDialogAPI class is straightforward. First, you need to specify the window that will be used to display the dialog box, which we'll get from a form's Hwnd property. Then, you need to specify the hInstance for your application, which we'll get using the Application.hWndAccessApp method. Next, you must tell the class which directory you want to use. The directory name must be specified as a complete pathname. Finally, you must pass a filter string containing the type of files that should be displayed in the dialog box.

The filter string must be formatted precisely. For each type of file you want to display in the dialog box, you must specify the filter as a description followed by a pattern string. For example, to filter for Access MDB files, you'd use the filter string:

  strFileFilter = "Access Databases (*.mdb)" & _Chr(0) & "*.mdb" & Chr(0)

The description for this filter is "Access Databases (*.mdb)", followed by a Null character, and the pattern. The pattern portion of the filter string is specified as "*.mdb" and is terminated by a Null character. If you set the filter to a Null string, then no files will be displayed in the dialog box. If you want to associate more than one file extension with a filter, you must separate each extension with a semicolon character, as in:

  strFileFilter = "Access Databases (*.mdb, *.mde)" & _
 	Chr(0) & "*.mdb; *.mde" & Chr(0)

We'll add custom properties to our class that simplify working with the results of calling the OpenFileDialog and SaveFileDialog functions. The first will be called GetName, which will store the name of the file the user selects. Similarly, the GetStatus property will tell you if the user actually selected a file in the first place, or if the Cancel or Close button was clicked.

Demonstrating the technique

Now that we've provided the background, let's put together a simple example that shows how to display both the Open and Save As dialog boxes using API functions. To begin, open a new database and create a new form in Design view. Then, ensuring that the Control Wizards button on the Toolbox is disabled, add a command button with the caption Open File and name the button cmdOpenFile. Next, add another command button, assign the caption Export Data and name it cmdSaveFile.

Click the Code button on the Form Design toolbar at this point. Then, choose cmdOpen from the Object dropdown list. At the insertion point, enter the code shown in Listing A. Next, choose cmdSaveFile from the Object dropdown list and enter the code from Listing B at the insertion point.

Listing A: Code to display the Open dialog box

  Dim cdlg As New CommonDialogAPI
Dim lngFormHwnd As Long
Dim lngAppInstance As Long
Dim strInitDir As String
Dim strFileFilter As String
Dim lngResult As Long
    
lngFormHwnd = Me.Hwnd
lngAppInstance = Application.hWndAccessApp
strInitDir = "C:\My Documents\"

strFileFilter = "Access Databases (*.mdb, *.mde)" & _
        Chr(0) & "*.mdb; *.mde" & Chr(0)
        
lngResult = cdlg.OpenFileDialog(lngFormHwnd, _
        lngAppInstance, strInitDir, strFileFilter)

If cdlg.GetStatus = True Then
    MsgBox "You selected file: " & _
        cdlg.GetName
Else
    MsgBox "No file selected."
End If

Listing B: Procedure to display the Save As dialog box

  Dim cdlg As New CommonDialogAPI
        
Dim lngFormHwnd As Long
Dim lngAppInstance As Long
Dim strInitDir As String
Dim strFileFilter As String
Dim lngResult As Long
        
lngFormHwnd = Me.Hwnd
lngAppInstance = Application.hWndAccessApp
strInitDir = "C:\"

strFileFilter = "Excel Files (*.xls)" & _
    Chr(0) & "*.xls" & Chr(0) & _
    "Text Files (*.csv, *.txt)" & _
    Chr(0) & "*.csv; *.txt" & Chr(0)
    
lngResult = cdlg.SaveFileDialog(lngFormHwnd, _
    lngAppInstance, strInitDir, strFileFilter)
        
If cdlg.GetStatus = True Then
    MsgBox "You selected file: " & _
        cdlg.GetName
Else
    MsgBox "No file selected."
End If

Set up the CommonDialogAPI class
At this point, choose Insert | Class Module from the menu bar. Then, press [F4] to display the Properties Window. Change the Name property to CommonDialogAPI.

Now, add the code shown in Listing C to the general declarations section of the class. This code sets up declarations for the API calls into comdlg32.dll and defines the OPENFILENAME user-defined type. We also declare two variables, which will be used to store return values from GetOpenFileName and GetSaveFileName. These will be used to set up custom properties for our CommonDialogAPI class.

For more details on making API calls, see the August 2001 article "Identify the location of special folders with API calls."

Listing C: Class module declarations

  Private Declare Function GetOpenFileName Lib _"comdlg32.dll" Alias "GetOpenFileNameA" _
  (pOpenfilename As OPENFILENAME) As Long
Private Declare Function GetSaveFileName Lib _"comdlg32.dll" Alias "GetSaveFileNameA" _
  (pOpenfilename As OPENFILENAME) As Long

Private Type OPENFILENAMElStructSize As LonghwndOwner As LonghInstance As LonglpstrFilter As _
 StringlpstrCustomFilter As StringnMaxCustFilter As LongnFilterIndex As LonglpstrFile As StringnMaxFile As _
  LonglpstrFileTitle As StringnMaxFileTitle As LonglpstrInitialDir As StringlpstrTitle As StringFlags As _
   LongnFileOffset As IntegernFileExtension As IntegerlpstrDefExt As StringlCustData As LonglpfnHook As _
   LonglpTemplateName As String
End Type

Private mstrFileName As String
Private mblnStatus As Boolean

Add the class properties and methods
To create the properties for the CommonDialogAPI class, add the code shown in Listing D. The properties created by the listed Property procedures, GetName and GetStatus, are accessible through the usual object.property syntax, as in the statement:

  If cdlg.GetStatus = True Then
    MsgBox "You selected file: " & _
        cdlg.GetName

where cdlg is an instance of the CommonDialogAPI class.

Listing D: Code to create class properties

  Public Property Let GetName(strName As String)mstrFileName = strName
End Property

Public Property Get GetName() As StringGetName = mstrFileName
End Property

Public Property Let GetStatus(blnStatus As Boolean)mblnStatus = blnStatus
End Property

Public Property Get GetStatus() As BooleanGetStatus = mblnStatus
End Property

The last bits of code we'll add are the two functions that actually display the dialog boxes. Since we're adding these to a class module, the functions will act as methods for the custom class. Add the functions shown in Listing E, then save the class module and close the VBE.

Listing E: Methods added to the class

  Public Function OpenFileDialog(lngFormHwnd As Long, _lngAppInstance As Long, strInitDir As String, _
	strFileFilter As String) As Long

Dim OpenFile As OPENFILENAME
Dim X As Long

With OpenFile.lStructSize = Len(OpenFile).hwndOwner = lngFormHwnd.hInstance = lngAppInstance.lpstrFilter = _
 strFileFilter.nFilterIndex = 1.lpstrFile = String(257, 0).nMaxFile = Len(OpenFile.lpstrFile) - 1.lpstrFileTitle = _
 OpenFile.lpstrFile.nMaxFileTitle = OpenFile.nMaxFile.lpstrInitialDir = strInitDir.lpstrTitle = "Open File".Flags = 0
End With
    
X = GetOpenFileName(OpenFile)
If X = 0 ThenmstrFileName = "none"mblnStatus = False
ElsemstrFileName = Trim(OpenFile.lpstrFile)mblnStatus = True
End If
End Function

Public Function SaveFileDialog(lngFormHwnd As Long, _lngAppInstance As Long, strInitDir As String, _
	strFileFilter As String) As Long

Dim SaveFile As OPENFILENAME
Dim X As Long
        
With SaveFile.lStructSize = Len(SaveFile).hwndOwner = lngFormHwnd.hInstance = lngAppInstance.lpstrFilter = _
	strFileFilter.nFilterIndex = _
 1.lpstrFile = String(257, 0).nMaxFile = Len(SaveFile.lpstrFile) - 1.lpstrFileTitle = SaveFile.lpstrFile.nMaxFileTitle = _
 SaveFile.nMaxFile.lpstrInitialDir = strInitDir.lpstrTitle = "Save File".Flags = 0
End With
    
X = GetSaveFileName(SaveFile)
If X = 0 ThenmstrFileName = "none"mblnStatus = False
ElsemstrFileName = Trim(SaveFile.lpstrFile)mblnStatus = True
End If
End Function

Test the form

To test the results of our procedures, return to your form and switch to Form view. Then, click the Open File button. The dialog box automatically displays files from the C:\My Documents folder (assuming your system has one). Note that the only choice in the Files Of Type dropdown list is for Access files, which are the only ones visible in the dialog box. Select a file as you normally would—our procedure captures the filename and displays it in a message box. Click the Export Data button to examine how our Save File dialog box differs.

Dialog boxes with class

In this article, we've shown you how to use two API functions to display the Open and Save As dialog boxes. These two functions have been encapsulated in the CommonDialogAPI class so that you can use them in any application you develop simply by including the class in the project. As you incorporate the class into your own applications, you might consider adding different properties to the class in order to provide more options, such as when setting the Flags element in the OPENFILENAME structure. For examples of how to use this element, see the article "Use constants for greater control over dialog boxes."

Copyright © 2001 Element K Content LLC. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of Element K Content LLC is prohibited. Element K is a service mark of Element K LLC.