Databases Reference
In-Depth Information
FIGURE 2.3
A bug in Excel VBA will cause this error without corrective action.
The name of the Workbook selected by the user will then be printed to the immediate (debug)
window. Notice that the filters desired are specified in the first passed parameter as a string with
the filter suffixes listed together one after another without spaces. The next two parameters are the
default directory and dialog box title, respectively.
Notice that the user is given two opportunities to choose a valid file before the subroutine will
abort. There is one bug within the Excel VBA system that is particularly troublesome in this regard.
The line of code:
If vFileName = "False" Then GoTo FileOpenErr
should not be required. It was necessary to add this line of code because if the user presses the
“CANCEL” button on the open file dialog box twice, the error will not be caught and the second
time the Cancel button is pressed (for reasons unknown), and Excel will attempt to open a file named
“False.xls” instead of triggering an error. This will result in an error occurring as shown in Figure 2.3.
2.3
USING THE STANDARD SAVE AS DIALOG BOX TO SAVE A FILE
Setting up a generic function to save a Workbook utilizing the standard save as dialog box is
slightly more complicated than the previous instance using the standard open file dialog box
because there are a few additional considerations to keep in mind when saving files (Figure 2.4).
As in the previous instance, it is still necessary to pick which filters will be available, the default
directory the dialog box opens with, and a title for dialog box. It is also desirable to have the
function return a string that is the name of the Workbook the save as dialog box just saved. Two
additional considerations are the following. The Workbook to be saved should be specified, in
the event it is not the active Workbook. Also, it would be desirable to specify if the Workbook
should be closed or left open immediately after it is saved. The next function allows all of these
options to be set utilizing passed parameters.
Function SaveWithDialog(ByVal Workbook, ByVal filters$, ByVal
defaultdir$, ByVal WindowCaption$, ByVal CloseAfterSave As
Boolean) As String
'Function to Save a Workbook as a Particular Type
' With a User Defined FileName
'The First Filter in the string filters$ will be the default
filter
Dim vFileName As String
Dim nochoice As Integer
Dim fillen As Integer
