EX2 - A Simple, But Highly Automated Spreadsheet Exchanging Data With An ODBC Database
The example below is an extensively automated spreadsheet. It was designed to show products
received, unloaded, loaded, or shipped from a single distribution point. The user can ask for
shipments for any day and any range of days, all materials or a single material. Other parts of
the workbook (not shown here) actually create the goods receipt and shipping records and there is
a Pivot Table on an adjacent sheet which is focused on the data you see. It will slice/dice/crosstab
the logistics data.
Single Monitor Screen Print: Logistics Reporting and Editing Tool - Receipts, Unloaded, Loaded, Shipped
The data on this spreadsheet is all retrieved by automation. There are no formulas in any of these cells.
It appears like someone just typed a value in each cell. The data comes from an ODBC compliant database
like Access, SQLSvr, Oracle, Dbase, etc. In this particular case it was communicating with an Access database over
a local area network.
The SQL statements used here are actually calculated real time as a text strings by Excel and passed
to the database engine using ADO. In this way, Excel can ask for a very diverse array of queries based
status, and/or the values in any of itís cells. This is not necessarily the fastest way to go, but in this
particular case it is a good choice and it returns the data almost instantly. Although Excel can retrieve
information from ODBC databases without automation via MS Query, it is very limited in itís flexibility
and ability to pass parameters and change the entire SQL based on cell values or user requests.
If the user has proper authorization, which Excel checks against an IT database, he/she can edit or
delete records directly from Excel. This authority check is done via automation. Once the user edits
the records in the spreadsheet Excel will then update the database with the changes.
All of the user
edits are checked by Excel for errors and value reasonableness before any update. All edited records
show the user who edited them, and the date/time stamp of the edit. Records are not actually deleted
or changed in the database. Excel marks the old record as history and writes a new current record. Each
record is assigned a version and status. Therefore, the complete history of any record can be reviewed
and changes can be reversed by the administrator.
In this spreadsheet, Excel assists the user in many
ways during the edit process, including automated editing of many records requiring the same change.
The screen print below shows what the spreadsheet looks like once the user enters the edit mode.
is being controlled by automation. Not all fields are allowed for editing by this user, Excel highlights
in green the ones he/she can edit. Wherever the user changes a cell, the cell is outlined in red. If Excel
changes another field automatically based on a user edit, the cell is highlighted in blue.
Grayed out lines are records marked by the user for deletion. At the users request, Excel marked all of the selected
records to be "pending" via a single user key stroke.
Single Monitor Screen Print: Logistics Reporting and Editing Tool - Edit Mode For Unloaded Materials
Automated autofilter allows the user to ďfilterĒ the displayed records based on a value, or a value
range of any field or multiple fields and criteria. Filtering hides and unhides rows based on the criteria.
Advanced functions (Standard In Excel) summarize quantities for the visible records only.
The speadsheet has an upper custom toolbar which morphs depending on which sheet the user is in. Toolbars
are created and managed by automation. They are designed to give the user control over the
automation features of the application. Although custom toolbars can be created by a user without automation,
they have many, many shortcomings in the current versions of Excel. Automation can solve all those drawbacks.
Custom pop-up toolbars can only be created by automation. The menu items can be configured to be unique
depending on which cell, range of cells, columns, range of columns, etc. the userís mouse is in when he/she
hits the right mouse button. Pop-upís can be activated by a large number of events other than right mouse
clicks. Just selecting a single cell can cause a unique pop-up menu to appear.
This spreadsheet has considerable custom formatting giving the data area almost a 3 dimensional feel.
Many times what looks great on the screen doesnít look so great when you print. Automation can allow
you to have both. Excel will actually freeze the screen before it prints. It will go in and make any changes in
formats required for a nice print job. It will then print the modified sheet and immediately undo all
its format changes before it updates the screen and returns control to the user. You will never know anything
changed. This can include hiding columns you really want to see on the screen but donít want on the printout.
Where To Go From Here....
Links To Other Sections Of This Site
true north usa LLC
225 East Stuart Avenue
Lake Wales, FL 33853-3712