Display Open and Save As Dialog Boxes in Access with API FunctionsThis 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 Functionsby 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
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 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
OPENFILENAME structure when the dialog box is closed. Your application
can then process this information as desired. Although we won't work with all
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.
GetOpenFileName function returns a
True value if a user selects a file. If
the user clicks the Cancel or Close buttons, the
False value. A
False value is also returned if 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
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
GetSaveFileName functions we'll encapsulate them all in a
custom class called
The CommonDialogAPI class
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
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
"*.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
SaveFileDialog functions. The first will be
GetName, which will store the name of the file the user selects.
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
GetSaveFileName. These will be used to set up custom properties for our
|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
GetStatus, are accessible through the usual
syntax, as in the statement:
If cdlg.GetStatus = True Then MsgBox "You selected file: " & _ cdlg.GetName
cdlg is an instance of the
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 wouldour 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
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
Flags element in the
OPENFILENAME structure. For examples of how to use
this element, see the article "Use constants for greater control over dialog
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.