| |
|
| |
|
|
VBA Hacker
Enhance Your Apps with Common Dialogs: Part I Mastering the Basics of the Windows Open and Save As Dialog Boxes
In this two-part series, we'll explore some of Microsoft Windows' great assets - its common dialog boxes - and put the emphasis on how to use them in your Office 2000 applications. (Sorry, the VBA code used in these articles and their associated source files contains many functions and features that aren't available in previous Office versions.)
Common dialog boxes were introduced in Windows 3.1 to help developers create applications with a consistent user interface. The most "common" common dialog box is the one that lets you open and save files.
For some strange reason, one of the software companies that often refuses to use these dialog boxes is Microsoft. Office 95, 97, and 2000 have consistently been inconsistent with the Windows interface guidelines (see FIGURE 1). As a matter of fact, it looks like it's the Office developers, and not the Windows team, that sets the trend. The Windows 2000 common dialog box (see FIGURE 1, bottom) has clearly "borrowed" the Places Bar, introduced with Office 2000, and the new Windows 2000 system font has become Tahoma, pioneered by the makers of Office 95. (Wasn't it Bill Gates who once claimed that there was a brick wall between the system and applications groups?)
What Are Your Options? If you write VBA applications that require a user interface to open or save a file, you have several options. The most obvious one is to use the Open or Save As dialog box of the host application. In Word VBA, for example, you can access the Dialogs collection and retrieve a file name with the following code:
Sub GetFileFromWord() With Dialogs(wdDialogFileOpen) If .Display Then MsgBox .Name End With End Sub
It's a simple and straightforward technique. The Display method (unlike the Show method) allows you to display the dialog box and obtain a file name without actually opening the file. Unfortunately, it's unreliable and therefore unusable. Just try and run the previous code and select more than one file in the dialog box. What you get is error 5174 (see FIGURE 2). Clearly, Word's Open dialog box can't cope with multiple file selections - and never could. This bug has been around since Word 95 because nobody in Redmond ever bothered fixing it.
You're much better off in Excel 2000. You can't use the Dialog object (which only has a Show method, so it doesn't let you display the dialog box without actually opening a file), but you can use the GetOpenFilename or GetSaveFilename method instead. GetOpenFilename copes well with multiple file selections:
Sub GetFilesFromExcel() Filename = Application.GetOpenFilename(FileFilter:= _ "All Files (*.*),*.*,Text Files (*.txt), *.txt", _ FilterIndex:=1, Title:="Open a File", _ MultiSelect:= True) For i = LBound(Filename) To UBound(Filename) MsgBox Filename(i) Next End Sub
Alas, the creators of Word and PowerPoint seem to have decided that you don't want this functionality, so you can't use the GetOpenFilename method across Office applications. A missed opportunity!
An alternative technique is to create a reference in your project to the Common Dialog ActiveX Control (COMDLG32.OCX). Unfortunately, this file isn't automatically installed with all versions of Windows or Office. You need to get Visual Basic or Visual FoxPro (or a distribution license from Microsoft) if you want to use it. However, if you do have the file, and it's properly registered, it's easy to use. The Visual Basic documentation claims that you must add the CommonDialog control to a form before you can set its properties, but that's not true. The code in FIGURE 3 doesn't need a form at all and works just fine. (As a matter of fact, this code doesn't even require a project reference to COMDLG32.OCX because it creates its own MSComDlg.CommonDialog object.)
Sub GetFileFromOCX() Dim CDLG As Object Set CDLG = CreateObject("MSComDlg.CommonDialog") With CDLG .DialogTitle = "Get me a File!" .Filter = _ "Documents|*.doc|Templates|*.dot|Text Files|*.txt" .ShowOpen MsgBox .FileName End With Set CDLG = Nothing End Sub FIGURE 3: Adding the CommonDialog control to a form before setting its properties isn't necessary with this code.
Finally, there's a fourth technique: using the Windows API to create your own common dialog box objects. This isn't only a convenient way to avoid licensing problems and have a solution that works for all Office applications, but it also opens the door to some exciting customizations that aren't available with any of the other options. We'll discover that in Part II; in this article, we'll keep it simple.
What's Involved Manipulating and displaying Windows common dialog boxes requires just two API functions: GetOpenFileName and GetSaveFileName, both of which are exposed by the COMDLG32.DLL file, installed in everybody's Windows\System folder. In a VBA module, you declare these functions as follows:
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
Both functions require a single parameter pointing to an OPENFILENAME structure. In VBA, you format Windows structures as a user-defined type (UDT). FIGURE 4 shows how to create this data type in VBA.
Private Type OPENFILENAME lStructSize As Long hwndOwner As Long hInstance As Long lpstrFilter As String lpstrCustomFilter As String nMaxCustFilter As Long nFilterIndex As Long lpstrFile As String nMaxFile As Long lpstrFileTitle As String nMaxFileTitle As Long lpstrInitialDir As String lpstrTitle As String Flags As Long nFileOffset As Integer nFileExtension As Integer lpstrDefExt As String lCustData As Long lpfnHook As Long lpTemplateName As String End Type FIGURE 4: The OPENFILENAME structure disguised as a user-defined type (UDT).
The OPENFILENAME structure contains information that the operating system uses to initialize the Open or Save As common dialog box. After the user closes the dialog box, the system returns information about the selected file(s) in this structure. FIGURE 5 gives a brief explanation of the values you can provide and expect in return.
FIGURE 5: The 20 members of the OPENFILENAME structure.
Although most OPENFILENAME members are optional, a few (lStructSize, nMaxFile, and lpstrFile) are required. The minimal listing in FIGURE 6 demonstrates how they're used.
Sub GetFileFromAPI () Dim OFN As OPENFILENAME With OFN .lStructSize = Len(OFN) ' Size of structure. .nMaxFile = 260 ' Size of buffer. ' Create buffer. .lpstrFile = String(.nMaxFile - 1, 0) Ret = GetOpenFileName(OFN) ' Call function. If Ret <> 0 Then ' Non-zero is success. ' Find first null char. n = InStr(.lpstrFile, vbNullChar) ' Return what's before it. MsgBox Left(.lpstrFile, n - 1) End If End With End Sub FIGURE 6: Code showing how OPENFILENAME structures are used.
This simple macro displays the most basic common dialog box. Since no value is specified for lpstrTitle, the dialog box defaults to the string "Open," and the lack of a value for lpstrFilter results in an empty "Files of type" list.
There are many ways to personalize common dialog boxes. One of the most powerful customization features is the Flags member of the OPENFILENAME structure. By specifying one or more of the flag values shown in FIGURE 7, you can alter the way the dialog box behaves.
FIGURE 7: OFN Flags. This list isn't comprehensive, but it contains all flags used in this project.
When you specify flags, you can simply add the constant names or use the Or operand. For example, to display the Save As dialog box, you probably don't want to show the read-only checkbox, but you do want to ensure the user specifies a valid path, and you certainly want to display a warning if the user selects the name of an existing file. The following instructions do just that:
OFN.Flags = OFN_HIDEREADONLY Or OFN_PATHMUSTEXIST Or _ OFN_OVERWRITEPROMPT
Introducing the Common Dialog Box Class Listing One contains a class module that encapsulates all the nitty-gritty of common dialog boxes, and automates a lot of parameters. Let's walk through the relevant elements of the class and see which properties are used to make things happen. While we do that, you may also get an insight into some exciting new features of VBA 6.0 that you may not yet have discovered.
AllowMultiSelect property. Boolean. Default value is False. If set to True, the common dialog box allows multiple file selections. Ignored if the Save As dialog box is displayed.
CheckBoxSelected property. Read/write Boolean. Default value is False. If set to True, the common dialog box displays and selects the read-only checkbox. On return, the True/False value of this property indicates whether the user selected or unselected the checkbox.
CheckBoxVisible property. Boolean. Default value is False. If set to True, the common dialog box displays the read-only checkbox. If the CheckBoxSelected property is set to True, CheckBoxVisible is also automatically set to True.
DialogTitle property. String. Specifies the title bar text. Especially useful if you don't want to use the dialog box to open or save a file, but need a file name (or a list of file names) for another purpose. If omitted, Windows uses the default caption ("Open" or "Save As").
Directory property. Read/write String. Specifies the initial path in the dialog box. If omitted, the initial path is determined by the operating system. (See also FileName property.) On return, this property contains the path in which the user has selected the file(s).
ExistFlags property. Long. Use any combination of the following enumeration variables: PathMustExist, FileMustExist, and PromptToCreateFile. These values correspond to the constants OFN_PATHMUSTEXIST, OFN_FILEMUSTEXIST, and OFN_CREATEPROMPT, shown in FIGURE 7. PathMustExist is automatically set if the Save As dialog box is displayed. (See also the "Enumerations" section.)
Extension property. Read/write String. Specifies the extension appended to the file name if the user fails to type an extension. This string can be any length, but only the first three characters are appended. If this property isn't specified and the user doesn't type an extension, no extension is appended. On return, this property contains the extension of the selected file. If multiple files are selected, the return value is an empty string.
FileName property. Read/write String. Specifies the file name used to initialize the "File name" edit control. If the specified file name contains a path name, and no value is specified for the Directory property, Windows 98 and earlier versions use the path component of FileName to initialize the dialog box. Windows 2000 uses this path to initialize the dialog box, even if a value is specified for the Directory property. On return, this property contains the name (including the path) of the selected file. If multiple files are selected, this property contains an empty string; use the FileNames property instead.
FileNames property. Read-only Collection. On return, contains the names (including the path) of all selected files. Use FileNames.Count to obtain the number of selected files. See the first example for its implementation. If the Save As dialog box is displayed, this collection contains a single file name.
FileTitle property. Read-only String. On return, this property contains the name (without the path) of the selected file. If multiple files are selected, this property contains an empty string; use the FileTitles property instead.
FileTitles property. Read-only Collection. On return, contains the names (without the path) of all selected files. Use FileTitles.Count to obtain the number of selected files. If the Save As dialog box is displayed, this collection contains a single file title. Returning file titles is especially useful if you have a form with a listbox that you want to fill with file names.
Filter property. Read/write Variant. Specifies the filters in the "Files of type" list. Can be a single string, or an array of strings. Filters are pairs of strings, separated by a pipe symbol (|). The first string in each pair is a string that describes the filter (e.g. "Documents"), and the second string specifies the filter pattern (e.g. "*.doc"). To specify multiple filter patterns for a single display string, use a semicolon to separate the patterns (e.g. "*.doc;*.dot;*.wiz"). A pattern string can be a combination of valid file name characters and the asterisk (*) or question mark wildcard character (e.g. "*.do?"). Don't include spaces in the pattern string. See the examples for its implementation. On return, this property contains the filter selected by the user.
FilterIndex property. Read/write Long. Specifies the initial index of the selected filter in the "Files of type" list. The first filter has an index value of 1, the second filter 2, etc. On return, this property contains the index of the filter selected by the user.
RestoreCurDir property. Boolean. Default value is False. If set to True, the common dialog box restores the current directory to its original value if the user changed the directory while searching for files.
ShowOpen method. Displays the Open dialog box. Returns True if one or more files were selected and False if the user clicked the Cancel button.
ShowSave method. Displays the Save As dialog box. Returns True if a file was selected and False if the user clicked the Cancel button.
Join, Split, and Replace The class module uses a number of features that didn't exist before VBA 6.0. If you haven't played with them yet, have a look at the Property procedures in FIGURE 8.
Property Let Filter(vFilter) If IsArray(vFilter) Then _ vFilter = Join(vFilter, vbNullChar) OFN.lpstrFilter = Replace(vFilter, sPipe, vbNullChar) End Property
Property Get Filter() With OFN If .nFilterIndex Then Dim sTemp() sTemp = Split(.lpstrFilter, vbNullChar) Filter = sTemp(.nFilterIndex * 2 - 2) & _ sPipe & sTemp(.nFilterIndex * 2 - 1) End If End With End Property FIGURE 8: Property procedures new to VBA 6.0.
Recall that the Filter property of the common dialog box class can be specified as a single string or an array. So, for example, you can use a string assignment such as this:
OFN.Filter = _ "Documents|*.doc|Templates|*.dot|Text Files|*.txt"
or you can assign an array:
Dim f(2) As String f(0) = "Documents|*.doc" f(1) = "Templates|*.dot" f(2) = "Text Files|*.txt" OFN.Filter = f
Windows doesn't like either format, and wants its filter strings separated by null characters. The Property Let Filter procedure converts the value it receives (a Variant) into the required format. First, the procedure checks to see if it received an array by using the IsArray function. If this function returns True, the Join function is called to create a single string from the individual array elements. In this case, the elements are joined with null characters. This results in a single string:
"Documents|*.doc" & Chr(0) & "Templates|*.dot" & _ Chr(0) & "Text Files|*.txt"
The second instruction in the procedure then uses the Replace function to convert all remaining pipe characters (|) into null characters, and adds a null character at the end. This results in a string that Windows can handle:
"Documents" & Chr(0) & "*.doc" & Chr(0) & _ "Templates" & Chr(0) & "*.dot" & Chr(0) & _ "Text Files" & Chr(0) & "*.txt" & Chr(0)
The Property Get Filter procedure (which returns the filter selected by the user) reverses the process. The Split function is used to break the string into array elements (using the null character as the separator), which creates an array such as this:
sTemp(0) = "Documents" sTemp(1) = "*.doc" sTemp(2) = "Templates" sTemp(3) = "*.dot" sTemp(4) = "Text Files" sTemp(5) = "*.txt"
Depending on the value of the filter index (which is 1-based), the two matching array elements are then joined, delimited by the pipe symbol.
Enumerations Enumerations were also introduced with VBA 6.0. Enumeration variables are variables declared with an Enum type. The elements of the Enum type are initialized to constant values within the Enum statement. The assigned values can't be modified at run time, and can include both positive and negative numbers. The class module contains the following enumeration:
Public Enum XFlags PathMustExist = OFN_PATHMUSTEXIST FileMustExist = OFN_FILEMUSTEXIST PromptToCreateFile = OFN_CREATEPROMPT End Enum
The Property Let ExistFlags procedure takes this enumeration as its parameter:
Property Let ExistFlags(nFlags As XFlags) OFN.Flags = OFN.Flags Or nFlags End Property
The great advantage of enumeration variables is that they facilitate the input of values. FIGURE 9 demonstrates how Microsoft's IntelliSense technology helps you provide the correct parameters.
Examples If you want to use common dialog boxes in your applications, you must add the ComDlg.cls module to your project. The following examples demonstrate how you can use the ComDlg objects in your application.
Example 1 The code in FIGURE 10 displays the Open dialog box with a user-defined caption. The initial directory is Word's default document path. The filter is provided as an array, and the last filter is displayed as the default. The read-only checkbox is pre-selected, multiple file selection is enabled, and flags are set to make sure that only existing file paths are returned. If the Open button in the dialog box is clicked, the files are extracted from the FileNames collection, and opened in Word. If the read-only checkbox was selected when the dialog box closed, the files are opened as read-only.
Sub GetWordFiles() Dim CDLG As ComDlg Set CDLG = New ComDlg With CDLG .DialogTitle = "Open a Word file" .Directory = Options.DefaultFilePath(wdDocumentsPath) Dim f(3) As String f(0) = "Documents (*.doc)|*.doc" f(1) = "Templates (*.dot)|*.dot" f(2) = "Wizards (*.wiz)|*.wiz" f(3) = "All Files (*.*)|*.*" .Filter = f .FilterIndex = 4 .CheckBoxSelected = True .AllowMultiSelect = True .ExistFlags = FileMustExist + PathMustExist If .ShowOpen Then For i = 1 To .FileNames.Count Documents.Open FileName:=.FileNames(i), _ ReadOnly:=.CheckBoxSelected Next End If End With Set CDLG = Nothing End Sub FIGURE 10: The routine that displays the Open dialog box.
FIGURE 11 shows the dialog box created with the routine shown in FIGURE 10.
Example 2 The macro in FIGURE 12 displays the Save As dialog box using a non-standard caption. No value is specified for the Directory property, so the dialog box defaults to the path of the active workbook. The current name of the workbook is displayed in the File name box. A default extension is specified, so the file will have the .xls extension, even if the user doesn't type an extension. The Filter property is specified as a single string. No FilterIndex property is specified, so the first filter becomes the default. If the Save button is clicked, a copy of the workbook is saved to a file, without modifying the open workbook in memory.
Sub SaveWorkbookCopy() With New ComDlg .DialogTitle = "Save Copy As" .FileName = ActiveWorkbook.FullName .Extension = "xls" .Filter = "Workbook (*.xls)|*.xls " & _ "|Web Page (*.htm)|*.htm|Template (*.xlt)|*.xlt" If .ShowSave Then _ ActiveWorkbook.SaveCopyAs .FileName End With End Sub FIGURE 12: The routine to display the Save As dialog box.
FIGURE 13 shows the dialog box created with the routine in FIGURE 12.
Conclusion The purpose of this series is to demonstrate that there's a lot more to VBA programming than you may think. With a bit of hacking, you can greatly enhance your Office applications.
In this first installment of a two-part series, I showed you how you can implement Windows common dialog boxes in your projects. In the next installment, we'll discover some tricks that enhance the common dialog boxes beyond your wildest dreams. You'll be able to rename or hide any existing dialog box control, add your own functionality to the read-only checkbox, display a status bar and instant file information, and even add your own buttons. Watch this space for more VBA hacks!
The VBA source referenced in this article is available for download.
Dutchman Romke Soldaat was hired by Microsoft in 1988 to co-found the Microsoft International Product Group in Dublin, Ireland. That same year he started working with the prototypes of WinWord, writing his first macros long before the rest of the world. In 1992, he left Microsoft and created a number of successful add-ons for Office. Living in Italy, he divides his time between writing articles for this magazine, enjoying the Mediterranean climate, and steering his Landrover through the world's most deserted areas. Romke can be contacted at mailto:romke@soldaat.com.
Begin Listing One - ComDlg.cls Option Explicit
DefStr S DefLng N DefBool B DefVar V
' OFN constants. Const OFN_ALLOWMULTISELECT As Long = &H200 Const OFN_CREATEPROMPT As Long = &H2000 Const OFN_EXPLORER As Long = &H80000 Const OFN_EXTENSIONDIFFERENT As Long = &H400 Const OFN_FILEMUSTEXIST As Long = &H1000 Const OFN_HIDEREADONLY As Long = &H4 Const OFN_LONGNAMES As Long = &H200000 Const OFN_NOCHANGEDIR As Long = &H8 Const OFN_NODEREFERENCELINKS As Long = &H100000 Const OFN_OVERWRITEPROMPT As Long = &H2 Const OFN_PATHMUSTEXIST As Long = &H800 Const OFN_READONLY As Long = &H1
' The maximum length of a single file path. Const MAX_PATH As Long = 260 ' This MAX_BUFFER value allows you to select approx. ' 500 files with an average length of 25 characters. ' Change this value as needed. Const MAX_BUFFER As Long = 50 * MAX_PATH ' String constants: Const sBackSlash As String = "\" Const sPipe As String = "|"
' API functions to use the Windows common dialog boxes. 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 Declare Function GetActiveWindow _ Lib "user32" ()As Long
' Type declaration, used by GetOpenFileName and ' GetSaveFileName. Private Type OPENFILENAME lStructSize As Long hwndOwner As Long hInstance As Long lpstrFilter As String lpstrCustomFilter As String nMaxCustFilter As Long nFilterIndex As Long lpstrFile As String nMaxFile As Long lpstrFileTitle As String nMaxFileTitle As Long lpstrInitialDir As String lpstrTitle As String Flags As Long nFileOffset As Integer nFileExtension As Integer lpstrDefExt As String lCustData As Long lpfnHook As Long lpTemplateName As String ' Can also be a Long. End Type
' Private variables. Private OFN As OPENFILENAME Private colFileTitles As New Collection Private colFileNames As New Collection Private sFullName Private sFileTitle Private sPath Private sExtension
' Public enumeration variable. Public Enum XFlags PathMustExist = OFN_PATHMUSTEXIST FileMustExist = OFN_FILEMUSTEXIST PromptToCreateFile = OFN_CREATEPROMPT End Enum
Property Let AllowMultiSelect(bFlag) SetFlag OFN_ALLOWMULTISELECT, bFlag End Property
Property Let DialogTitle(sCaption) OFN.lpstrTitle = sCaption End Property |
.gif){kind=small}
