Excel Reporting While You Sleep 😴
Automate excel reporting while you sleep. Work smart not hard. 😴
This post details my step by step process for automating sales and labor reports (Kronos & Blackboard / Transact) in Excel shared with my team in OneDrive. It all starts with reports scheduled to be emailed overnight. 📅
Tools: Excel / Workbook Links, Outlook, OneDrive, VBA (Visual Basic for Applications), and Python (openpyxl / datetime).
Blackboard and Kronos reports scheduled to send overnight. Python script scheduled to run after receiving reports.
Outlook rules / VBA script - email moved to folder in outlook. Report attachments downloaded via VBA script
VBA script then converts file extensions from .CSV to .XLSX (to work with Openpyxl in Python) and renames the file to include the previous day’s date for historical purposes. Ex: “businessname_01-01-2024”
Saved to shared OneDrive folder
Python script runs.
Data is then scraped from reports and added to final Excel workbook report within OneDrive shared folder for team. I use the Openpyxl library and DateTime module in python for this. The data within the final report is then dispersed to other reports using workbook links within Excel.
Sure this took some time to set up, but it has saved me countless hours manually running reports and copying the data to numerous spreadsheets.
Contact me if you need help setting something up to work for you. This stuff is fun.