| This section walks you through the process of populating a worksheet with data returned from a database using a parameterized query. Setting the Layout Requirements The layout and appearance of the worksheet are important for the room scheduling purposes described earlier. The data must be put in specific cell ranges. That's the reason that the code resorts to recordsets: It's insufficient to merely return records from the database by choosing, for example, Import External Data from the Data menu. That approach imports data as a list, but the layout that's required is as shown in Figure 11.1. Figure 11.1. Names of meeting rooms are shown in column A, and times are shown in row 1 in 15-minute increments. 
The worksheet shown in Figure 11.1 has several aspects that the code must attend to: The caption on the worksheet tab is the date for the meetings shown on the worksheet. Each meeting occupies multiple columns and usually just one row. It spans as many columns as needed to capture its start time and stop time, and it occupies the row that represents a particular meeting room. (In one case shown in Figure 11.1, a meeting occupies rooms 1 and 2, which appear in rows 2 and 3.) The purpose of a meeting is shown in each cell that represents the meeting. So, YOGA appears in cells T17:W17. Before a meeting, a crew must set the room up: see to tables and chairs, set out refreshments, and so on. After a meeting, a crew must clean up: empty wastebaskets, remove coffee urns, and so on. It usually takes half an hour for setup and cleanup, so there is a period before and after each meeting time reserved for setup and cleanup. These periods are shown in a different color on the worksheet. Examples in Figure 11.1 are cells V13:W13 (setup for the CASE MGMT TRAINING meeting) and X17:Y17 (cleanup after YOGA). Notice cells V13 and Y17 in Figure 11.1. V13 has a dark black border on its left edge and Y17 has one on its right edge. These borders help distinguish the cleanup period for one meeting from the setup period for another, should the two periods abut one another.
To get the room reservations for a particular date onto the worksheet, the user chooses Jump to a Date from the custom Calendar menu. He selects a date and that date is written to the worksheet tab's caption. Then the following subroutine is called: Option Base 1 Option Explicit
The two option statements, placed at the top of the module, apply to all procedures located in the module. Option Explicit is included as good programming practice. Option Base 1 is included because it simplifies the array handling, discussed later in this section. Sub GetSingleDayFromDB() Dim dbReservation As DAO.Database Dim qdfRetrieveCurrent As DAO.QueryDef Dim rsRecordsToRetrieve As DAO.Recordset
A reference to the DAO library has been set for the subroutine's module. With that reference set, it's possible to declare three object variables: one to represent the database that contains information on room reservations, one to represent a query definition in that database, and one to represent a recordset that will contain the records returned by the query. Dim StartCol As Integer, StopCol As Integer, _ WhichRow As Integer Dim ReservationRange As Range
The StartCol, StopCol, and WhichRow variables are used to locate the column in which a reservation begins, the column where it ends, and its row. Together, these are used to define a worksheet range, ReservationRange. Dim SetupPeriods As Integer, CleanupPeriods As Integer
As mentioned earlier, a reservation needs to allow time to set the room up before the meeting, and to clean it up afterward. The variable SetupPeriods stores the number of 15-minute periods needed for setup and CleanupPeriods does the same for cleanup. Dim TimeAsText As String
Microsoft Office applications store time as a serial number, with the integer portion representing a date and the decimal portion representing time. Excel has various ways to display date and time information. It can show time as hours and minutes, as hours, minutes, and seconds, on a 24-hour clock or using AM/PM notation, and so on. It's possible to store time information as text. For example, the time that's represented numerically as .4688 can be displayed, using a time format, as 11:15 AM. This is the format, h:mm AM/PM, used in the first row on the worksheet. In order to compare a reservation time with those row 1 values, it's useful to store the time as a text value instead of as a serial number. The variable TimeAsText is used for that purpose. Dim TimeArray(71) As String, RoomArray() As String
Two arrays are declared. TimeArray will store all the times in row 1 of the worksheet. That array has exactly 71 elements. The 15-minute periods are on the worksheet in the range B1:BT1. They range from 6:30 a.m. through 12:00 a.m., inclusive, and that's 71 periods. The array RoomArray is declared as a dynamic array, one whose number of elements can be changed in the code as it runs. From time to time, the number of rooms available for meetings changes. By making the array dynamic instead of static, the code can count the number of rooms on the worksheet and redimension RoomArray accordingly. Dim i As Integer Dim ResourceCount As Integer
Two integer variables are declared. The variable i is used as a counter in a For-Next loop, and ResourceCount is used both to redimension RoomArray and to help define a range on the worksheet. Application.ScreenUpdating = False DatabaseName = ThisWorkbook.Sheets("UserNames").Cells(1, 3)
To speed up processing because the active worksheet will be completely refreshed, screen updating is turned off (this "freezes" the monitor momentarily). The path to and name of the database that stores reservation information is kept in cell C1 of a worksheet named UserNames. Storing it there makes it unnecessary to store it somewhere in the VBA code, where it can be hard to locate, given that the project includes seven modules. The value in that cell is assigned to DatabaseName. Set dbReservation = OpenDatabase _ (DatabaseName, False, False, "MS Access")
The database is opened and assigned to the dbReservation object variable. Set qdfRetrieveCurrent = _ dbReservation.QueryDefs("RetrieveSingleDay")
The object variable qdfRetrieveCurrent is set to the query, found in the Resources database, named RetrieveSingleDay. qdfRetrieveCurrent.Parameters("ThisDate") = ActiveSheet.Name
The query named RetrieveSingleDay, which is represented in this code by the object variable qdfRetrieveCurrent, has a parameter. The DAO and ADO libraries both include this object. It's a type of criterion, one that you reset each time the query executes. See Figure 11.2, which shows the query in design view. Figure 11.2. Notice that the name of the parameter is given in square brackets. 
It's by way of the RetrieveSingleDay query and its parameter, ThisDate, that the code obtains data on reservations that have been made for a particular day. If you were to open the query directly, from within Access, you would be prompted for a value for the parameter. Then Access would respond by displaying all records that have the value you supplied on the field ReservationDate. NOTE In addition to the date parameter, the RetrieveSingleDay query uses another criterion. Notice in Figure 11.2 that the field named Cancelled has the criterion False. The query returns no record that has the value True on the Cancelled field. By using this field, reservations can be prevented from showing up on the worksheet without physically deleting them from the database. The Cancelled field is used later in this chapter, in "Deleting Records from the Database and from the Worksheet," where the RemoveReservation() subroutine is discussed.
Instead, the code gets the caption shown on the active worksheet's tab and passes that value as a parameter to the query. The query treats that value exactly as it does a value that you supply when you open the query directly. TIP Both ADO and DAO have an object named Parameter, and you can assign a value to it. But Excel also has an object named Parameter. If you declare an object variable to represent a parameter, be sure to qualify it with the object library you want. For example, Dim prmTheDate As Parameter Set prmTheDate = qdfRetrieveCurrent.Parameters("ThisDate")
results in a type mismatch because VBA assumes that prmTheDate is an Excel parameter, not a DAO or ADO parameter. Instead, be sure to use Dim prmTheDate As DAO.Parameter
or Dim prmTheDate As ADO.Parameter
Now the code executes the query and puts the results into a recordset that will be returned to the Excel worksheet. Again, the reason to put the results of the query into a recordset, instead of returning them directly to the worksheet, is that the latter approach brings the records back laid out as a list, instead of in the layout shown in Figure 11.1. The recordset is opened as forward only. This is an efficient type of recordset, and it's appropriate because the code will not scroll back and forth through the records and will not edit them. Set rsRecordsToRetrieve = _ qdfRetrieveCurrent.OpenRecordset(dbOpenForwardOnly)
Clearing the Worksheet Now it's time to pick up information about the worksheet. The code begins by counting the resources listed in column A and using that to dimension RoomArray. ResourceCount = ActiveSheet.Cells(600, 1).End(xlUp).Row - 1 ReDim RoomArray(ResourceCount)
The code assumes that there are no entries in column A farther down than row 600. It uses the End(xlUp) method to find the final entry in column A, and notes its row (in Figure 11.1 that's row 19). It subtracts 1 from that row number to account for the empty cell A1. So, using the layout shown in Figure 11.1, ResourceCount equals 18, and ResourceArray is redimensioned to contain 18 elements. ActiveSheet.Range(Cells(2, 2), _ Cells(ResourceCount + 1, 72)).Clear
The code clears all cells in the worksheet that might contain data, removing information about reservations for the date that the worksheet currently represents. The range beginning with cell B2 and extending to the BT19 (19 because ResourceCount equals 18 in this example) is cleared of its contents, thus removing the cell values that identify the purpose of a reservation. The Clear method also removes cell comments and the cell colors that identify reservations, setup periods, and cleanup periods. Populating the Memory Arrays With the worksheet cleared of reservation data, the code picks up the names of the available resources, and times of day, and places them in memory arrays. These arrays will be useful in identifying the exact location on the worksheet where the information about each reservation should be placed. First, though, it's best to check to see whether the date that the user has requested has any reservations established. If Not rsRecordsToRetrieve.BOF Then
The code tests to see whether the query has returned an empty recordset. This can come about if the user specified a date that has no reservations in the database. In that event, subsequent code that makes reference to putative records will cause runtime errors; therefore, the subroutine executes that code only if there's at least one record in the recordset. One way to test for an empty recordset is to check whether the current position, immediately after the recordset has been populated, is BOF, or Beginning Of File. If that is not the current position, the recordset has at least one record and the code can proceed normally. For i = 1 To 71 TimeArray(i) = Application.Text _ (ActiveSheet.Cells(1, i + 1), "h:mm AM/PM") Next i
Populate the memory array TimeArray with the values found in cells B1:BT1 on the worksheet. Although they're displayed in h:mm AM/PM format, the cells' actual contents are date/time serial numbers. So, the code uses Excel's Text function to store the time values in TimeArray as strings, and using the same format the worksheet uses. The reason for this is that the code will subsequently search the array for time values in string format to determine which column it should use to enter reservation information. For i = 1 To ResourceCount RoomArray(i) = ActiveSheet.Cells(i + 1, 1) Next i
Similarly, the RoomArray array is populated with the names of the rooms found in column A. Note the use of ResourceCount as the final value used by the loop's counter. Also note that RoomArray's elements run from 1 through 18 because of the Option Explicit statement at the start of the module. In its absence, the array's elements would run from 0 through 17. Finding the Reservation's Location Next, a With block is established. Subsequent statements that refer to objects, properties, or methods qualified only with a dot (for example, .Fields) are deemed to belong to the With block's object here, the recordset. With rsRecordsToRetrieve
With the With block established, the code enters a loop that steps through the recordset, record by record. It terminates when the recordset's EOF (its End Of File) has been reached. Do Until .EOF
Within the Do loop, each reservation record is checked to determine its start time, its stop time, and the room that it uses. This is done by getting the value in the record's StartTime field, its StopTime field, and its ResourceName field. These values are compared with the values in TimeArray and RoomArray to locate their positions within the arrays. The results of those comparisons inform the code which worksheet columns to use as the reservation's start and end times, and which worksheet row to use as the reservation's meeting room. Begin by getting the value of StartTime and converting it to h:mm AM/PM format, once again using Excel's Text function. TimeAsText = Application.WorksheetFunction.Text _ (.Fields("StartTime"), "h:mm AM/PM")
Then use Excel's Match function to locate the position of the reservation's start time within TimeArray. Recall from Chapter 2, "Excel's Data Management Features," that when used on a worksheet, the Match function returns the position of a value within a range of cells. Used in VBA code, it can as here return the position of a value within an array. So, if the time stored in the StartTime field were (in serial number format) 0.3125, the Text function would convert it to 7:30 AM. The Match function, searching TimeArray for 7:30 AM would return 5, which is the fifth value in the array. Because that's how the array was populated, 7:30 AM is also the fifth time shown in row 1 of the worksheet. But because the available times begin in column B, not column A, 1 is added to the result of the Match function. StartCol = Application.Match _ (TimeAsText, TimeArray, 0) + 1
Similar logic is used to obtain the column of the reservation's stop time: TimeAsText = Application.WorksheetFunction.Text _ (.Fields("StopTime"), "h:mm AM/PM") StopCol = Application.Match(TimeAsText, TimeArray, 0)
But this time, the number 1 is not added to the result of the Match function. Suppose that a reservation's stop time is 12:00 p.m., as is the case with the YOGA reservation in row 17 of Figure 11.1. Then cell W17, corresponding to the 15-minute period beginning at 11:45 a.m., should be the reservation's final cell. By the time that 12:00 p.m. rolls around, the meeting is ending and is into its cleanup period. Therefore, 1 is not added, as it is to the result of the start time Match. WhichRow = Application.Match(.Fields("ResourceName"), _ RoomArray, 0) + 1
The row that the reservation occupies on the worksheet is found in a similar way. The ResourceName field in the recordset contains the name of the reserved room. That value is matched to RoomArray to find its position in the array, corresponding (almost) to the worksheet row. That "almost" is handled by adding 1 to the result, accounting for the blank worksheet cell A1. Set ReservationRange = ActiveSheet.Range _ (Cells(WhichRow, StartCol), Cells(WhichRow, StopCol))
The object variable ReservationRange is set to represent the columns beginning with StartCol and ending with StopCol, and occupying the row whose number is WhichRow. Now it's possible to refer repeatedly and conveniently to that range in subsequent statements. Putting the Data on the Worksheet Now that the code knows which cells the reservation should occupy, it puts the necessary data in those cells and provides the right formatting. ReservationRange = UCase(.Fields("Purpose"))
Each cell in ReservationRange is filled with the value stored in the record's Purpose field. The VBA function Ucase is used to convert lowercase letters in the field to uppercase (for example, Yoga becomes YOGA). If .Fields("ReserveHold") = "Reserve" Then ReservationRange.Interior.ColorIndex = 3 Else ReservationRange.Interior.ColorIndex = 6 End If
The application allows the user to fill a range of cells temporarily, not with a firm reservation but by holding a room until plans become more firm. If the record represents a firm reservation, the cells in the range are colored red (their ColorIndex is 3). Otherwise, the record represents a tentative hold and the cells are colored yellow (their ColorIndex is 6). SetupPeriods = .Fields("SetupPeriods") CleanupPeriods = .Fields("CleanupPeriods")
The recordset has two fields, SetupPeriods and CleanupPeriods, which store the number of 15-minute periods that should be reserved for setting a room up before the meeting and cleaning up afterward. These integer values (for example, two setup periods to allow 30 minutes for preparation) are assigned to two variables for later use. If SetupPeriods > 0 Then ReservationRange.Offset(0, SetupPeriods) _ .Resize(1, SetupPeriods).Interior.ColorIndex = 48 End If
The cells that represent the meeting's setup period are colored gray. This is done by using Excel's Offset function. Offset's usage on the worksheet is described in Chapter 2. Used here, it identifies a range that is offset from ReservationRange by zero rows and by as many columns as the value of SetupPeriods. Notice the minus sign before SetupPeriods as an argument to Offset. This means that if SetupPeriods contains 2, the range is offset from ReservationRange two columns to the left of the start of ReservationRange. Additionally, the Resize method is used to make the offset range 1 row high and SetupPeriods columns wide. Then its ColorIndex is set to 48, or gray. With ReservationRange.Offset(0, SetupPeriods) _ .Resize(1, 1).Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThick .ColorIndex = 1 End With
The first cell in the range that represents the setup periods is given a black border on its left edge. This cell is found in the same way that the setup range itself is found: by means of an offset by a negative number of setup periods. The sole difference is that the Resize method is used to specify a single cell, the first one in the range of setup periods. If CleanupPeriods > 0 Then ReservationRange.Offset(0, ReservationRange.Columns _ .Count).Resize(1, CleanupPeriods).Interior. _ ColorIndex = 48 End If With ReservationRange.Offset(0, ReservationRange.Columns. _ Count + CleanupPeriods - 1).Resize(1, 1) _ .Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThick .ColorIndex = 1 End With
The same procedure is used to establish a range of cleanup periods and a black border. The differences from specifying the setup periods are as follows: The offset to ReservationRange takes the number of columns in the range into account so that the setup periods begin to the right of the final column in the range of reserved cells. The Resize method uses the positive number in CleanupPeriods to make the range extend to the right from ReservationRange instead of to the left, as is done with SetupPeriods. The right edge of the final setup period cell is given a black border, instead of its left edge.
A cell comment is added to the first cell in ReservationRange. That comment shows the name of the user who made the reservation, the name of the person for whom the reservation was made, and the date on which information about the reservation was most recently modified. These values are obtained from their respective fields in the recordset and concatenated into a comment, along with labels and carriage return characters. The code does this by invoking the AddComment method on the first cell of ReservationRange. ReservationRange.Resize(1, 1).AddComment _ ("Reserved By: " & .Fields("ReserverName") & _ Chr(10) & "Reserved For: " & .Fields("ReservedFor") _ & Chr(10) & "Last Modified: " & Format _ (.Fields("MostRecentlyModified"), "m/d/yy"))
Chr(10) identifies a carriage return. Suppose that the person who made the reservation is named Joe, the person for whom the reservation was made is named Mary, and that the date that the reservation was last modified is 9/1/2004. In that case, the cell comment appears as follows: Reserved By: Joe Reserved For: Mary Last Modified: 9/1/2004 If .Fields("Participants") = "External" Then _ ReservationRange.Font.Bold = True End If
The final chore for the current record in the recordset is to format ReservationRange in boldface font if the meeting participants are external to the organization, and to leave the font normal otherwise. .MoveNext Loop
Continuing Through the Recordset The code moves to the next record in the recordset. (It can't move back, even if to do so fit the code's logic, because the recordset was opened as forward only.) The end of the loop is reached and control returns to the top of the loop, where the test for the recordset's EOF is made. If it's now at EOF, execution continues with the following End With statement, which terminates the With rsRecordsToRetrieve block. End With
Then the If block is terminated. This If tested whether or not the recordset was at BOF at the outset, which would have indicated an empty recordset. End If
Finally, the code releases the object variables by setting them equal to Nothing and the subroutine itself terminates. Set qdfRetrieveCurrent = Nothing Set rsRecordsToRetrieve = Nothing Set ReservationRange = Nothing End Sub
| 