If you use the File Open dialog and install your application on multiple computers, you'll eventually find an ugly surprise waiting for you on Windows XP/Vista. Peter Vogel looks at the two solutions available to you.
If you have an application that lets users work with files on your hard disk, the decent thing to do is to give your users the ability to browse your file system, using the standard Windows File Open and Save dialogs. For many versions of Access, if you wanted to use the dialogs you would add the CommonDialog control to your form and then call methods and properties on the control.
This works well–until you move your application to Windows XP. At that point, you may find that your perfectly good code stops working. The reason is that the interface used for some versions of the common dialog control on Windows XP is different from that used on other versions of Windows. If, for instance, your application uses version 6 of the control but the computer that you're installing the application on is using version 5, your application isn't going to run. It may be tempting to just install your version of the common dialog control along with your application, but I wouldn't rush to that solution: The more often that your application installs its own version of common controls, the more likely it is that you'll damage some other application (it's called the "common" dialog control for a reason).
You have two solutions: Bypass the file dialog control by calling the underlying Windows functions, or use the FileDialog property of Access' Application object.
The simple solution
The simplest solution is to use the FileDialog property because it allows you to continue working in an object-oriented fashion–setting properties and calling methods. To use the FileDialog property, you define a variable of type FileDialog, then set that variable to the Application object's FileDialog property, specifying what kind of dialog you want to use. You can then set the properties on your FileDialog object and call the Show method. The full pathname of the file that the user selects using the dialog can be found FileDialog's SelectedItems collection (if the user selects only a single file, the name will be in position 1 in the collection). This example uses the File Open dialog:
Dim fd As Office.FileDialog
Dim strFileName As String
Set fd = Application.FileDialog(msoFileDialogOpen)
fd.AllowMultiSelect = True
strFileName = fd.SelectedItems(1)
However, there are two problems, one very real and one potential:
• The very real problem is that the FileDialog property doesn't appear until Access 2003.
• The potential problem is that you're now counting on Windows handling the differences between the various common dialog controls. If the CommonDialog control could fail, so (presumably) could the FileDialog property.
A safer solution
As a result, your safest solution, at the cost of doing more work, is to bypass the object-oriented wrappers for the dialog control. Instead, you can call the underlying Windows functions directly. There are two things that you have to do to call these functions:
• Define the structure that holds the data to be passed to the Windows function.
• Declare the Windows function.
The two declarations that you need are included in the accompanying download. You can now set the options in the structure to indicate what you want to do and then call the function. The file name selected by the user will be returned by the function.
The following code does the same work as the previous object-oriented code. As you can see, you must set the size of the structure, set the type of file to display, set the maximum file name length, and initialize the file name field before calling the function. You must also use the flags field in the OPENFILENAME structure to set options that were handled, in the FileDialog object, by setting properties. Finally, as this code shows, extracting the resulting file name is a little more complicated since the string that's returned by the function consists of the file name terminated by a null character:
Dim OpenFileStruct As OPENFILENAME
OpenFileStruct.lStructSize = Len(OpenFileStruct)
OpenFileStruct.lpstrFilter = _
"All Files (*.*)" & Chr(0) & "*.*"
OpenFileStruct.lpstrFile = String(257, 0)
OpenFileStruct.nMaxFile = _
Len(OpenFileStruct.lpstrFile) - 1
OpenFileStruct.flags = cdlOFNExplorer Or cdlOFNLongNames
strFileName = _
InStr(1, OpenFileStruct.lpstrFile, vbNullChar) - 1)
The sample file in the download shows how to use the CommonDialog control, the FileDialog property, and the call to the Windows function.
Your download file is called 503VOGEL2.ZIP in the file SA2005-03down.zip
This can be purchased with all the other downloads on this page
Other Pages On This Site That You Might Like To Try