Access Cookbook - Ken Getz [101]
To use the File Open or File Save dialogs, call the acbCommonFileOpenSave function, passing to it information indicating what you want it to do. Table 4-2 lists the options available when you call the function. None of the parameters is required; the table lists the default values the function will use if you leave off each of the parameters. As a simple example, the following function call will ask for the name of the file to which you'd like to save, suggesting FOO.TXT and returning the full path of the file you choose:
varFileName = acbCommonFileOpenSave(FileName:="FOO.TXT", OpenFile:=False)
Table 4-2. Parameters for the acbCommonFileOpenSave function (all optional)
Parameter name
Description
Default value
Flags
A combination of zero or more flags from Table 4-1 that control the operation of the dialog. Combine them using the OR operator.
0
InitialDir
The initial directory that the dialog should use.
""
Filter
A string listing the available file filters. Use acbAddFilter, as shown in the examples, to build this parameter. The format of this item is important, so make sure to use the function rather than just setting the value by hand.
""
FilterIndex
The number of the filter item to use when the dialog first opens. The first filter is numbered 1.
1
DefaultExt
A default file extension to be appended to the filename if the user doesn't supply one. Don't include a period.
""
FileName
The filename to use when the dialog is first displayed.
""
DialogTitle
The title for the dialog. Usually, you won't specify this parameter.
Open/Save As
hWnd
The window handle for the parent window of the dialog. This value controls where the dialog will be placed.
Application.hWndAccessApp
OpenFile
Whether it's the Open or Save dialog. (True = Open, False = Save).
True
TIP
Because the acbCommonFileOpenSave function accepts so many optional parameters, and you'll generally want to set only a few of them, you may find VBA's support for named parameters useful. That is, rather than depending on the exact order of the parameters you send to acbCommonFileOpenSave, use the name of the parameter, a :=, and then the value, as we've done in this example. This will make your code easier to read and far less error-prone.
If you also want to specify filter choices that show up in the "Files of type:" combo box on the dialog, call the acbAddFilterItem function. This function accepts three parameters: the string of filters to which you want to add items; the description for your filter ("Databases (*.mdb, *.mda)", for example); and the actual filter file specifications, delimited with a semicolon ("*.mda;*.mda", to match the previous example). The function returns the new filter string. You can call acbAddFilterItem as many times as you need to build up your list of filters. For example, the following example (similar to the example in basCommonFile) sets up four filter expressions. You can call TestIt from the debug window in Access to test the filters:
Function TestIt( )
Dim strFilter As String
strFilter = acbAddFilterItem(strFilter, "Access Files (*.mda, *.mdb)", _
"*.MDA;*.MDB")
strFilter = acbAddFilterItem(strFilter, "dBASE Files (*.dbf)", "*.DBF")
strFilter = acbAddFilterItem(strFilter, "Text Files (*.txt)", "*.TXT")
strFilter = acbAddFilterItem(strFilter, "All Files (*.*)", "*.*")
MsgBox "You selected: " & acbCommonFileOpenSave(InitialDir:="C:\", _
Filter:=strFilter, FilterIndex:=3, DialogTitle:="Hello! Open Me.")
End Function
You may want to specify some of the available options for controlling the common dialogs, as shown in frmTestOpenSave. You can specify any of the options shown there, and more, when you call the function. To specify your selected options, choose values from the items in Table 4-2, combine them together with the OR operator, and send this value to the acbCommonFileOpenSave function as the Flags argument. For example, the following statement will build up a Flags value