| One of the advantages VBA is that has most of the facilities
of full Visual Basic. But that advantage can also be a bain as we shall
shortly see in the case of providing an OpenFileDialog. In effect, we
have too many choices of how to implement the open file dialog. And in
order to be careful that you are making the right choice you have to spend
some time deciding which is the best way to implement the OpenFileDialog.
This tutorial is based on a real world development for a client and some
of the trade offs that had to be decided in implementing some VBA programs.
It all started in the case where I was developing some VBA code using the
Figure 1 - References in VBA
|Tidestone Formula 1 spreadsheet component (see the Reference in Figure 1, fifth from the top) . This commercial
ActiveX component allows users to create Excel-like
spreadsheets. Now you might ask, "why use a Tidestone
Spreadsheet component when Excel's VBA exposes all the power of Excel's spreadsheets directly ?". Well, the
problem is that the customer is actually using Visio on all their desktops but not Microsoft Office. To further
complicate things, the version of Office available on each desktop varies from Office 95 to Office 2000. So to
avoid the problem of not having the right libraries loaded on each machine the client wanted to use the Tidestone
spreadsheet control which they already had done some development work with.
The dilemma arose when we discovered that Formula 1 had a special OpenFileDialog called SSOpenFileDlg. At first
glance it appeared to provide all the functionality of OpenFileDialog. I had been using the Microsoft Common File
Dialog control but that added an additional 149KB to the distributable file; so the client suggested I work with
the Formula 1's SSOpenFileDlg.
So I setup a test routine to tryout SSOpenFileDlg in comparison with OpenFileDialog (see Figure 2 below). At first
it looked like we had a winner - there was no problem getting the Formula 1 Open File dialog to appear and for
users to either select a file or cancel the selection. And since it looked and worked like the "real thing"
our suspicion was that Tidestone just called the Windows API function directly.
However, we ran into a problem. The routine needed to identify the dialog with a special title, "Enter
Definition File", and it also had to apply some file filters - *.def for Definition files and *.* for all
files in the directory just in case the user wanted to check that the file had not been renamed or moved from the
directory being searched. However, to our surprise, Tidestone did not supply any way to set these properties of
the standard OpenFileDialog (and as it turned out 4-5 other properties were omitted as well).
So one of the members of the team suggested following the Tidestone lead and just use the Windows kernel function
directly by adding a reference to it in the .bas module we already were using for other file operations. The declaration
' WinAPI Declarations
Private Declare Function GetOpenFileName% Lib "COMDLG32" _
Alias "GetOpenFileNameA" ( OPENFILENAME As OPENFILENAME )
And the resulting code (triggered by the ReadFileRAW button in Figure 2) is a bit more complex because for filters
you have to separate each portion of the filter string with the null string and be sure to terminate the whole
thing with two Nulls - Chr(0) & Chr(0) as shown just below.
Opend.CustomFilter = "Def file" & Chr(0) & "*.def" & Chr(0) & _
"Text files" & Chr(0) & "*.txt" & Chr(0) & "All files" & Chr(0)
& "*.*" & Chr(0) & Chr(0)
Opend.DefaultExt = "*.def"
Opend.InitialDir = App.Path
Opend.Title = "Open using RAW Win API"
filname = modFileDialog.WinFileDialog(Opend, 1)
Well as it turn out, although using the WinAPI is free (there is no additional overhead- the functions are
always available); our coders were making enough mistakes using the API functions to cause fits. Twice coders forgot
to put the double Nulls at the end of a filter string. The result was some serious debug time.
|So we decided to try one more option, using the FSO-FileSystemObject of the Microsoft Scripting Runtime (see Figure
1 where it is highlighted in the References dialog). We checked the size of the SCRUN.DLL and at 137KB it
turned out to be marginally smaller than the Common File Dialog Control. So the ReadfileFSO command button
triggers the FSO routine. However, we discovered a problem - although FSO allows for extensive file manipulation
(open file, parse filename, move a file, find a file, delete a file, and many others); it does not support an open
file dialog. Back to square one.
Figure 2 - Test UserForm for VBA OpenFile.Dialog
|As it turned out, during our testing an ever expanding UI-User Interface required us to also save a file and print
results. These UI needs are matched by the Common File Dialog control which supports the standard Windows Open,
Save, Print, Font and Color dialogs. In addition, the team felt that the Common Dialog offered/exposed the best
set of control properties for the Open and Save dialogs as listed below:
CancelError - signals that the user clicked the dialog's Cancel button
DefaultExt - sets the Default file extension, *.def in our case
DialogTitle - sets what will appear in the Dialog's title bar
Filename - sets what default filename to be used, if any
Filter - set the file filters to be applied
Font... - allows a whole range of font styling to be set
Help... - allows several Help file settings to be initialized
InitDir - sets which directory the Open or Save dialog starts in
Position... - parameters allow setting the exact start up position of the dialog
DialogTitle, Filter, and InitDir turned out to be the most often used properties.
So we came full circle back to using the Common File Dialog control. Its extra size of 149KB was outweighed by
the additional properties and dialog types it supported. This is a cautionary tale. When we begin to examine database
connections in VB and VBA the number of options increases from 2 or 3 to nearly a dozen different access methods
where there are even more complicated trade-offs to be made. Already we have covered one of the options - the popular
and easy to use DAO access method. So this is the strength and challenge of VB and VBA, inevitably there is a say
to get just about any Windows task programmed; and many times there are several choices - the problem arises in
choosing the best way.
References: Wrox Press Peter Wright's Beginning VB6 discusses the Open File Dialog and other common controls on
pages 335-357 while the Coriolis Press VB6 Black Book covers the same topics with a more programmatic view point
from pages 342-368.