ROSA: Excel VBA Medical Transport Management App with PostgreSQL

Overview

Proximité Santé tracked patient transport in a homemade Excel file and asked for something sturdier: a UserForm to encode new trips, protected data sheets so nobody could edit records by hand, a filter and search system to analyse trips, and code restructured in object-oriented programming with class modules. The result is ROSA — a genuine transport management application written in VBA, not a spreadsheet. Data lives in Excel tables across seven worksheets: Transport (Table68, around 2,477 trips) as the core dataset, Client (793 clients), Lieu (827 locations), Favoris, Liste (insurance providers, transport types, PMR categories, drivers, vehicles), the ROSA control interface and a hidden champs sheet. Every entry goes through VBA forms that write to the tables via a repository layer, and multi-user mode was later added by connecting the application to a PostgreSQL database.

The challenge

• Trips, clients and locations were typed straight into cells, so a single mis-click could corrupt years of transport history.
• There was no reliable way to filter or search roughly 2,500 trips by client, date, driver, vehicle or insurance provider.
• The original macro code was procedural and duplicated, making every new field or rule risky to add.
• Reference data — insurers, transport types, PMR categories, drivers, vehicles — lived in loose lists with no validation.
• The tool eventually had to be used by several people at once, which a single shared Excel file cannot do safely.

What I built

• A full VBA application layer: frmTrip, frmClient, frmManageTrips and supporting UserForms for every create, edit and delete operation.
• A CRepository data-access layer so forms never touch cells directly — all reads and writes go through one typed, testable entry point.
• Object-oriented restructuring with class modules for trips, clients, locations and lists, replacing scattered procedural macros.
• Locked and protected data sheets (Transport, Client, Lieu, Favoris, Liste, hidden champs) so the database can only change through the forms.
• A filter and search engine over the Transport table: by client, location, date range, driver, vehicle, transport type, PMR category and insurer.
• The ROSA interface sheet: a button-driven control panel that hides the spreadsheet mechanics behind a real application UI.
• A PostgreSQL backend wired to the same repository layer, enabling true multi-user access while keeping the familiar Excel front end.

Results

• About 2,477 trips, 793 clients and 827 locations managed through forms instead of raw cell editing.
• Accidental data corruption effectively eliminated — the sheets are protected and writes are centralised.
• Trip lookup went from manual scrolling and ad-hoc filters to instant multi-criteria search.
• Clean OOP structure means new fields, rules and reports can be added without rewriting the app.
• PostgreSQL migration unlocked concurrent multi-user work with no change to the user-facing Excel interface.

Tech stack