|The program described on this page is obsolete and has been re-written and available for download here.|
|Integrating Maintenance and Inventory Applications|
|I developed the Inventory and Maintenance Excel Workbook applications independently, but I always planned to integrate them to provide a link between maintenance functions and the parts and supplies required to complete those functions. My first attempt at this integration is the InventoryMaint Excel workbook, the opening worksheet of which is shown in the screen capture below.|
|The integrated application along with blank Inventory and Maintenance workbooks can be downloaded via the link below. This zip file also includes the SarahInventory and SarahMaintenance files used in the screen captures below as examples of the data that can be maintained in these workbooks.|
|For this integration I have moved all of the VBA (Visual Basic
for Applications) code in the
Inventory and Maintenance workbooks into this workbook. The
Inventory and Maintenance workbooks now contain only data, no VBA
code. The primary worksheet in the InventoryMaint workbook is
"Tables". This sheet contains the links to the Inventory and
Maintenance workbooks. At the top of the worksheet is the
current version of the InventoryMaint VBA code (3.04).
The table below provides links to the documentation on integrated
applications as they are added.
|When Inventory/Maintenance is started a CommandBar ("Files") is
created at the bottom of the Excel Window (red ellipse).
Clicking on the Files button is the first step in the application. It calls up the File Management user form (green arrow, below). The File Management form contains two text blocks for the names of the Inventory and Maintenance workbook files (green arrows). Initially those workbooks have not been opened and the fields are blank. To open the workbooks click on the "Open" buttons next to the text blocks.
|The Open button will call up a file dialog bock to specify the Inventory and Maintenance workbook files. In the screen capture on the left I have clicked on the Open button for the Inventory workbook.|
|I have selected the workbook "SarahInventory" and the workbook file name has been stored in the text block and the cell in the "Tables" worksheet (red arrow). The CommandBar at the bottom of the Excel window (red ellipse) has been updated to include the Inventory application menu buttons.|
|Clicking on the Excel Window menu shows that the "SarahInventory" workbook is now open.|
|When I click on the "Enter" menu button the standard Inventory data entry form is activated.|
|Similarly, when I have opened the "SarahMaintenance" workbook the "Log" button now appears in the CommandBar. When I click this button the initial Maintenance user form is activated.|
one exception, the InventoryMaint workbook does not change the
processes of the Inventory and Maintenance workbook. Those
processes are documented for each application (links below).
The one process that has been modified is the Save Form, which was originally developed for the Inventory application. With version 3.04 the Save Form has been modified to work with both applications (plus any applications that may be added in the future).
On the right is a screen capture of the Save Form as called by the Inventory application Save button.
screen capture shows the same Save Form, but this time it has been
called by the Save Maint Log button from the Maintenance
Now both applications have the identical save and backup processes.
|When the InventoryMaint workbook is closed it also closes the Inventory and Maintenance workbooks. If either of those workbooks have been modified, but not saved before the InventoryMaint workbook is closed, the Save Forms will be called so that any changes can be saved.|
|Using Removable Storage|
|A very common approach to using this application is to maintain the working files on a removable drive. This allows the application to be run on any computer that has a compatible version of MS Excel. The solid state USB drives are probably more reliable than the rotating storage of an internal disk drive. However, the use of removable storage can create a problem when the drive ID changes.|
|In the screen capture on the left I have executed the application from a USB drive on one system where the drive has an ID of "E".|
|Then I removed the drive and inserted it into another system. Now the drive ID is "G".|
|When I open the application form this drive it attemps to open the inventory file from the "E" drive, which does not exist on this system. This produces the error message shown in the screen capture.|
|The inventory file field in the "Tables" worksheet is cleared, and then a file open error message for the maintenance file is displayed.|
files must be re-opened using the "Files" commandbar button.
Now the file names are set to the "G" drive. If the USB drive
is moved back to the original computer this process will have to be
repeated for the "E" drive.
No data is lost because of the re-opening of the workbooks, but the backup date is cleared and must be reset via the "Save" functions.
It is best to maintain the application on one system, using it on other systems only for quick updates to the inventory or maintenance log.