Profile: Craig Hatmaker
Here is an example of an update spreadsheet. We will use it in our tutorial. It updates the target stocking level (“Target Lvl”) for Products in the Northwind database. It could just as easily, with different configuration, update an item master in SQL Server, iSeries DB2, Oracle, or just about any other relational database.
Normally, update spreadsheets have three buttons, “New”, “Load”, and “Post”. I’ve trimmed this example to just two buttons for now. We’ll add the “New” button later.
When the user first opens the spreadsheet, it is blank except for the buttons. The user can click “Load” and select which products to change, or they can load all products as I have done in this example. As followers of this blog might guess, the “Load” button acts just like our reports except for one formatting difference – the yellow cells.
Yellow cells are the only cells that can be changed. We use equivalent functionality to Excel’s “Format cell” > “Protection” > “Locked” option, along with the ActiveSheet.Protect Contents:=True to block users from changing anything outside the yellow areas. We also use these cell qualities to guide keyboard navigation (cursor keys) to the next open cell.
Another change you might have noticed is the “ACD” column. “ACD” stands for “Add, Change, Delete”. An “X” indicates that a record already exists in the database. When the user changes the “Target Lvl” column, the appropriate “ACD” cell changes to “C”, indicating the record changed. When the user clicks the “Post” button, our spreadsheet finds rows with an “ACD” set to “C” and executes an SQL Update statement for us. Fortunately, we can use the Fields Table to help take the drudgery out of constructing SQL statements. Here is what the expanded “Fields Table” looks like.
You can click the graphic to display a larger version of it. The next post will explain the new fields.