London Buses 'Notice Of Event' Logging System

From my experience as a London Bus Service Controller using the iBus equipment, I was continually frustrated with having to seach for these notices to keep my drivers informed of what was happening on the Routes under my supervision.
When I started assisiting the Compliance department with Mileage processing and other associated admin work, I adapted the system for use by the Mileage team for printing auditable documents detailing all NOE relevant to the routes we operate.

This is a system designed and written by me and instigated by the Compliance Manager that utilises Microsoft Outlook and Microsoft Excel to download, save and process PDF documents sent out by London Buses for upcoming roadworks/closures.

NOE Log Detail View

A 2 step semi-automated process and written in Visual Basic code, Outlook downloads PDF attachments to a dedicated folder and sets the messages to read once complete and moves them to archive.
Then, an Excel spreadsheet reads the files, opening them sequentially and processing the text contained producing a log of each events data including a hyperlink to the actual PDF document.

NOE Log Full View

If a Tower Transit route is affected, the system also inserts the NOE detail and the attachment as an appointment in the shared MS Office calendar system.

 NOE Log Outlook Calendar View

 

For the techies out there, the processing system is a 2 step manual start process.
1. Outlook uses a routine written in Visual Basic to set all Inbox messages to Un-Read, download message attachments, set processed messages to Read and moves them to archive once downloaded.
2. Excel uses a routine to open each PDF file sequentially and uses the "Send Keys" function to open the PDF, copy the text, close the file and then paste the data. This information is then processed, checking the affected Routes and setting a flag if any Tower Transit routes are found. This flag is used to trigger an insert into calendar function for the Outlook shared Calendar.

The NOE Log itself can filter a single day or a date period where NOE's are active. The system also checks for amended NOE's and automatically excludes them if the dates are the same.