Databases Reference
In-Depth Information
triggered, and the error handling mechanism within the subroutine will automatically add the suffix
“.xls” to the Workbook name. This will allow Worksheets to be activated trouble-free from any
computer regardless of the “Hide File Extensions for known file types” setting on a particular
machine.
This is also a good opportunity to mention that the Workbook name in the Workbooks Activate
sequence need not be a literal string. A variable string can also be utilized. This provides great
flexibility in programming. For instance:
Workbooks("Book3.xls").Activate
and
WorkbookName$ ="Book3.xls"
Workbooks(WorkbookName$).Activate
will accomplish the same thing.
It is also possible to structure a loop to iterate through the number of existing Workbooks. This
is an extremely handy feature because, oftentimes, at the end of an analysis, it is desirable to close
Workbooks that meet certain criteria. Perhaps, some Workbooks may be utilized for intermediate
calculations so as not to clutter the “official” report Workbook. Others may contain no data for one
reason or another and should be removed from the workspace. The next subroutine will print out
the names of all the Workbooks open in Excel to the debug window. Users can select if they wish to
have the hidden (or invisible) Workbooks in Excel displayed by means of the
includeInvisible
parameter.
Sub CountWorkbooks(ByVal includeInvisible As Boolean)
Dim bookCounter As Workbook
For Each bookCounter In Application.Workbooks
If Windows(bookCounter.Name).Visible = True Then Debug.Print
bookCounter.Name
If Windows(bookCounter.Name).Visible = False And
includeInvisible = True Then
Debug.Print bookCounter.Name
End If
Next bookCounter
End Sub
One last comment on Workbooks. It is not possible to change the name of the Workbook by
setting a string to equal the (.Name) property of the Workbook. This will result in an error. This
is because the (.Name) property is used by Excel to facilitate loading and saving of Workbooks in
the windows file system. Therefore, a statement like:
ActiveWorkbook.Name = "SpecialReport"
will result in an error. The only way to change the name of a Workbook is to save the Workbook
under a different filename. This process is covered in Chapter 2.
1.3
THE WORKSHEET
Every Workbook must contain at least one visible Worksheet. When a new Workbook is created, three
Worksheets are included within it by default. The user can set the number of Worksheets to be created
at startup by going to the menu and selecting Tools->Options, and under the “General” tab there is a
spin button that allows the user to set the number of default sheets in a new Workbook. (see Figure 1.3.)
