General
- Over 500 unique processes were running daily, weekly, monthly 6 servers in a secured room, end-user desktops
- Processes ran Monday through Saturday, except during weekday nightly batch
- Processing usually between 5:00 AM and 11:00 PM
Processes
- Extract reports from mainframe applications
- Extract jobs created trigger files
- Job scheduler would launch a process using batch files triggered by extract jobs
- Macros created by end-users from the business were typically Excel recorded macros
- Job scheduling typically in series, meaning Job1 launches Job2, which launches Job3, etc
- Office 97
- Networked files left open, moved, renamed or deleted
- End user files modified without prior notification to IS
- 3rd party files not updated, missing, renamed or modified without prior notification to IS
- Files open, locked or missing
- Recorded VBA lacked error handling, comments, written inconsistently
- Mainframe interface macros recorded by advanced users working in multiple applications simultaneously
- Troubleshooting process hangs for online mainframe applications took well over a year to gain any competency
Accomplishments
Error Handling
- Incorporated error handling in all routines
- File exists, file locked, file empty, file date-time unchanged
- Email alerts to IS and end users on error conditions
- Jobs would close and create an error condition flag (100_NOT_COMPLETE.txt)
- Access routines would record processing start and stop times and conditions internally
- Code was in generic VBA modules that worked in Access, Excel and Word
Job Status Updates
- Job Status emailed to all end-users indicating completed, on-time and any late jobs
- Completed jobs would be have a green background, late jobs would have a red background
- Multi-user Access tools would have specific emails indicating the tool was available for use
- Messages would have links to open the tool locally and links for new users to install
- Within the tools themselves, several would only open once updated, or would show that the data within the tool was out of date
- Used group email addresses managed by each department where possible
Standardized Attachmate Code
- Implemented logon and logoff functions for all online applications
- IS automated routines given unique user ID
- Implemented BusyClock, SceenCheck, CursorCheck and Connection functions
- BusyClock handled screen update latency issues related to internet and network
- ScreenCheck used to confirm the unique screen number of each application page
- CursorCheck used to confirm the x-y screen coordinates
- CheckConnection used to detect loss of connection to the mainframe
- Removed shortcut key recorded code
- All functions start and return to main application screen
- Single and multi-application screens
Job Scheduling in Parallel
- Jobs launched 2 hours before business shift start
- Jobs continue to cycle in a wait state until trigger file detected
- Jobs with multiple triggers cycle until all triggers detected
- Non-License Specific applications (WinShuttle) could run on any of 9 desktop computers
- Scheduling data for each computer backed up routinely
- Remote management of all machines implemented, including VPN from home
VBA Code Management
- Standard modules stored in a secured network folder
- VBA code for common functions
- File I/O
- Email (CDO, SMTP)
- Error Handling
- Office application specific code
- Batch commands
- Wait function for use when controlling other applications
End User Tools