| You have in an Excel worksheet a list of vendors and the amounts that you have paid each one during the prior fiscal year. In the process of closing the books for that year, you want to store the total amount paid to each vendor in an external database. Your data is laid out as shown in Figure 7.6. Figure 7.6. Data sets that have varying numbers of records in different groups, as here, often call for Do While loops. 
You would like to get a total dollar amount for each vendor shown in Figure 7.6. Notice that different vendors have different numbers of records, and that the records have already been sorted by vendor name. You'd rather not get a count of records for each vendor which would be necessary if you were to use a For-Next loop to total the purchase amounts from a vendor. If you didn't intend to move the information to a database, this would be an obvious and ideal setup for a pivot table. You could cause the PivotTable Wizard to use the data in columns A and B as its source, and construct a table with Vendor as its row field and Sum of Amount as its data field. But because you want to automatically update a database with vendor totals, a pivot table won't help you: A pivot table can import external data but it can't export it. Instead, you use the following code in preparation to update the database. You begin by setting a reference to the DAO object library, as discussed earlier in this chapter in "Using Other Applications' Object Models." You then declare the subroutine and the variables it needs: Sub TotalVendorPurchases() Dim InputRow As Long, OutputRow As Long, FinalRow As Long Dim PurchasesFromVendor As Currency Dim CurrentVendor As String Dim VendorBuys As DAO.Recordset Dim Vendors2004 As DAO.Database
Excel worksheets can have as many as 65,536 rows. The variables InputRow, OutputRow, and FinalRow will each contain a row number and it is conceivable that the row numbers will be greater than 32,767 the maximum legal value for an integer variable. Therefore, you declare the variables as Long a numeric type that is an integer but can take on values (much) larger than 65,536. You also declare two object variables, one to represent the database and one to represent a set of records, or recordset, in the database. Your code then sets the two object variables. It sets the Vendors2004 object variable equal to an existing Access database, found in the same path as your workbook. The code then sets the VendorBuys object variable equal to a table, TotalByVendor, in that database. Set Vendors2004 = OpenDatabase(ThisWorkbook.Path & "\2004 Finals.mdb") Set VendorBuys = Vendors2004.OpenRecordset("TotalByVendor", dbOpenDynaset)
Next, you need to find out how far down into the worksheet your data extends: FinalRow = Worksheets("Purchases").Cells(65536, 1).End(xlUp).Row
The End(xlUp) usage is a valuable one. You use it here to determine how far down the worksheet the code should look in its search for more records. Suppose that you activate cell A65536 (the final cell in Column A), that it is an empty cell, and that you hold down the Ctrl key and then press the Up Arrow button. Excel would make the active cell the first one above A65536 that is not empty. That's what this statement does: It starts in Cells(65536,1) and travels to the end, going up until it locates a non-empty cell. Whatever that end cell is, its row number is assigned to the FinalRow variable. (You can also use xlDown, xlToRight, and xlToLeft, depending on what you want to accomplish.) OutputRow = 1 InputRow = 2
The output will begin in row 1 and the input begins in row 2, because of the row headers Vendor and Amount. CurrentVendor = Worksheets("Purchases").Cells(InputRow, 1)
You store the current value of the vendor name in the variable CurrentVendor. As the code proceeds through the rows of input, the vendor name changes. When that occurs, the total purchases for that vendor are written to an output sheet and to the database, and variables are re-initialized. Do While InputRow <= FinalRow
The outer loop will run as long as the row from which input is taken is less than or equal to the final row of input data. Do While CurrentVendor = Worksheets("Purchases").Cells(InputRow, 1)
The inner loop runs as long as the vendor name in the current row of input equals the value of CurrentVendor. PurchasesFromVendor = PurchasesFromVendor + _ Worksheets("Purchases").Cells(InputRow, 2)
Until the inner loop terminates (when the vendor name changes) continue to increment the total purchases for that vendor by the value found in the current input row, second column. InputRow = InputRow + 1
And increment the value of InputRow, so that the next time through the loop will look at the next row down the worksheet. Loop
The Loop statement marks the end of the inner loop. When the vendor name changes, the inner loop terminates and the following code executes: Worksheets("TotalByVendor").Cells(OutputRow, 1) = CurrentVendor Worksheets("TotalByVendor").Cells(OutputRow, 2) = PurchasesFromVendor With VendorBuys .AddNew .Fields("VendorName") = CurrentVendor .Fields("PurchaseTotal") = PurchasesFromVendor .Update End With OutputRow = OutputRow + 1 CurrentVendor = Worksheets("Purchases").Cells(InputRow, 1) PurchasesFromVendor = 0
In English (not in the BASIC programming language): Write the name of the current vendor to column 1 of the TotalByVendor worksheet, in the row identified by OutputRow. Write the total purchases from that vendor in column 2 of the same row. Add a new record to the database table and set that record's two fields equal to the name of the current vendor and the total of the purchases from that vendor. Establish the new record by calling the table's Update method. Increment OutputRow by 1. Get the next vendor name from the Purchases worksheet and store it in CurrentVendor. Reset PurchasesFromVendor to zero. Loop End Sub
Terminate the outer loop when InputRow has become greater than FinalRow, and terminate the subroutine. Figure 7.7 shows what the output looks like when the code has completed. Figure 7.7. These records appear the same in the database table as they do in the worksheet range. 
| 