Lab Schedule Automation Tool
VBA-powered Excel automation for ICET lab schedule processing
Project Overview
View on GitHub
How This System Works
The Lab Schedule Automation Tool is a VBA-powered Excel macro-enabled workbook designed to streamline the processing of ICET lab schedules. This system converts official lab schedules into multiple usable formats for integration with the Daily Dashboard and ICET Lab Checksheet systems.
Key Features & Capabilities
Upload & Parse
Load official ICET lab schedules and automatically extract lab events
Daily Processing
Generate per-day schedules (Monday-Saturday) for operational use
Multi-Format Export
Generate CSV for Google Calendar and JSON for web integration
Error Prevention
Built-in checks for sheet naming, date ranges, and formatting
Core VBA Modules
The system consists of several key VBA modules:
- InitializeGlobals: Sets references and resets variables
- browseFile: File picker dialog for schedule upload
- getSched: Main parsing routine for schedule data
- summarize: Builds summary sheet with aggregated data
- lastClass: Populates end-of-day (EOD) sheet
- combineSheets: Merges summary and EOD into master dataset
- saveFile: Exports master.json for web integration
- ExportToGoogleCalendarCSV: Creates per-lab CSV files
Technical Implementation
Strict Sheet Naming
- • Parsing logic expects A[building][floor][room] format
- • Prevents runtime errors and ensures correct lab mapping
- • Critical for JSON and CSV output accuracy
Color Code Parsing
- • Reads cell fill colors to classify events
- • Distinguishes course types without text labels
- • Handles inconsistent formatting gracefully
Output Formats
Google Calendar Integration
- • Generates CSV files for each lab
- • Color-coded events for visual distinction
- • Compatible with Magic Mirror modules
- • Real-time display on Daily Dashboard
Web Integration
- • Exports master.json for ICET Lab Checksheet
- • Direct MongoDB insertion capability
- • Real-time end-of-day tracking
- • API-compatible data structure
Workbook Structure
Fixed Worksheet Names:
- •
how to use- User interface - •
mon-sat- Daily schedules - •
summary- Aggregated data - •
eod- End-of-day times - •
master- Combined dataset
⚠️ Important Notes:
- • Do not rename worksheets
- • Clean source files before processing
- • Exports overwrite existing files
- • Desktop path resolution for easy access
Business Benefits
Time Savings
Eliminates manual data entry and reduces processing time
Data Accuracy
Prevents human error and ensures synchronized schedules
System Integration
Seamless integration with Daily Dashboard and Lab Checksheet
🔗 Project Repository
This project is open source and available on GitHub. The repository includes the complete VBA code, documentation, and system architecture diagrams. The tool is actively used for ICET lab schedule management.