Dark Area
Dark Area Dark Area
Dark Area
Home Technology About Us Examples FAQ Links Location Contact Us Change Header: Prev Next (17)
Dark Area
Dark Area
Dark Area

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 on itís 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. All formatting 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

Phone: 863.676.3248
Fax: 863.679.9254
Email: info@true-north-usa.com
true north usa LLC
225 East Stuart Avenue
Lake Wales, FL 33853-3712
Dark Area
Dark Area
...web design by F3 Computerized Solutions