Course Contents:

• MS Excel VBA
• MS Access
• SQL Server
• Power Apps
• Power Automate
• SharePoint

Module 1: Excel VBA – Automating Excel Workflows

Learning Objectives:
Master the VBA language to automate repetitive tasks
Build professional-grade tools with forms, controls, and efficient data structures
Integrate Excel with other Microsoft apps and external data sources

Topics Covered:

VBA Fundamentals & Macros

• Setting up Developer Tab and VBA Editor (VBE)
• Recording, editing, and improving macros
• VBA syntax: variables, data types, constants
• Procedures and Functions: Sub vs. Function
• Conditional logic (If, Select Case) and looping (For, For Each, Do While)

Working with Excel Objects

• The object hierarchy: Application, Workbook, Worksheet, Range
• Selecting, activating, and referencing cells programmatically
• Dynamic referencing and named ranges
• Working with tables (ListObjects)
• Formatting, sorting, filtering data via code
• Printing automation: page setup, print area, PDF export

Arrays, Dictionary, and Collections

• Arrays:
  • Declaring fixed-size and dynamic arrays
  • ReDim and Preserve
  • Using arrays for faster bulk data processing
  • Sorting and filtering data in arrays
• Collections:
  • Creating and managing collections
  • Looping through collections
  • Use cases: storing objects, de-duplicating, dynamic lists
• Dictionary (via Scripting.Dictionary):
  • Creating key-value pairs
  • Checking for existence with .Exists
  • Removing duplicates from lists
  • Aggregating and grouping data dynamically
• Comparison between Arrays, Collection, and Dictionary
• Real-world examples:
  • De-duplicating email addresses
  • Grouping sales by region
  • Creating lookup-like structures in memory

UserForms and UI Automation

• Designing user-friendly forms with controls (TextBox, ComboBox, Buttons)
• Validating user inputs and error handling
• Multi-page forms and conditional visibility
• Storing form data into Excel or Access
• Progress bars and status indicators

File & Folder Automation

• Reading/writing text and CSV files
• Batch renaming files in a folder
• Importing multiple workbooks or worksheets
• Consolidating data from multiple files
• Logging and audit trails via text file

Interacting with Office Apps

• Automating Outlook: send emails, attach files, loop through mail items
• Creating Word documents from Excel templates
• Connecting Excel to Access: import/export records via DAO/ADO
• Triggering Access queries or reports from Excel

Advanced Techniques

• Event-driven macros: Workbook_Open, Worksheet_Change, SelectionChange
• Named ranges and dynamic charting
• Using class modules for object-oriented VBA
• Error trapping: On Error Resume Next, error handlers
• Modular and reusable code practices

Projects:

• Order Management System in Excel: handle orders, generate invoices, manage stock levels
• Duplicate Remover using Dictionary to cleanse email/contact lists
• Custom Search Form with UserForm and collection-based history tracking
• Client Report Generator that builds and sends Word/PDF reports via Outlook
• Folder Scanner Tool that renames and logs hundreds of files into Excel

Module 2: Microsoft Access – Building Smart Databases

Learning Objectives:

• Design and implement relational databases to store and manage structured data
• Use forms and queries for dynamic data input, filtering, and reporting
• Automate database operations using macros and VBA
• Integrate Access with Excel and Outlook for end-to-end office automation

Topics Covered:

Introduction to Microsoft Access

• Understanding relational databases and database normalization
• Access interface overview: navigation pane, object types (tables, queries, forms, reports)
• Best practices for database planning and design
• Use cases: contact management, inventory control, transaction logging, CRM systems

Table Design and Relationships

• Creating tables with appropriate data types (text, number, currency, date/time, attachment)
• Defining primary keys and setting up foreign key relationships
• Enforcing referential integrity and cascade options
• Indexing fields for performance
• Lookup fields and value lists
• Creating audit fields (created/modified by/date)

Form Development (Front-End Interface)

• Creating simple and complex data entry forms
• Designing user-friendly layouts with controls: text boxes, combo boxes, list boxes, check boxes
• Main form / subform relationships for one-to-many views
• Conditional formatting and calculated controls
• Navigation forms and switchboards for menu-based navigation
• Using macros to automate form behavior (e.g., open related forms, validations)
• Handling form events and customizing logic with VBA

Query Design and Data Analysis

• Creating Select queries to filter, sort, and search data
• Using criteria and expressions for dynamic results
• Parameter queries for interactive prompts
• Action queries: Append, Delete, Update, Make-Table
• Join queries: inner, left, right, and self joins
• Aggregate and Totals queries (SUM, COUNT, AVG, etc.)
• Crosstab queries for pivot-style reporting
• Subqueries and nested queries for advanced use cases
• Calculated fields and IIF statements for logic in queries

Report Building and Printing

• Designing professional reports from queries or tables
• Grouping and sorting data with headers/footers
• Creating summaries (e.g., totals per category/region)
• Adding calculated controls and expressions
• Conditional formatting for better readability
• Print layout setup: margins, orientation, page breaks
• Exporting reports as PDF, Excel, or sending via email
• Report snapshots and batch printing

Macros and Automation

• Creating embedded and standalone macros
• Automating form/report actions (open, close, filter)
• Auto-execution macros: AutoExec and startup forms
• Conditional macros with logic and variables
• Error handling in macros
• Linking macros to buttons, combo boxes, and events

Access VBA (Intermediate Automation)

• Writing basic procedures in the VBA editor
• Automating data entry validation and auto-fill fields
• Creating dynamic form responses (e.g., show/hide controls)
• Running queries with VBA
• Sending emails using VBA and Outlook integration
• Using recordsets to read and write data dynamically
• Automation between Excel and Access (e.g., data import/export)

External Data & Integration

• Linking to external sources: Excel, SQL Server, SharePoint lists, CSV
• Importing and exporting data
• Creating linked tables and refreshing connections
• Automation triggers based on imported data
• Working with Access Split Databases (backend/frontend model for multi-user environments)
• Deploying Access apps with runtime installation

Security and Backup

• Securing forms and queries with user-level access
• Password-protecting Access files
• Creating regular backups via macro or script
• Compact and repair functionality for performance and stability

Hands-On Projects:

• Inventory Management System: barcode-enabled stock entry, reorder alerts, printable reports
• Customer Service Ticketing Tool: auto-number tickets, searchable issue history, status tracking
• Sales Quotation Generator: select products, apply discounts, generate a PDF quote
• Event Registration Database: attendee forms, capacity limits, confirmation emails
• Task Tracker: daily/weekly task list, overdue flags, summary dashboards

Capstone Example:
Design an integrated database system that tracks employee details, project assignments, leave records, and generates monthly HR reports. Build user forms for easy data entry, use queries for reporting, automate workflows via macros/VBA, and export results to Excel and PDF formats.

Module 3: SQL Server for Office Automation

Learning Objectives:

• Understand relational databases and optimize analytical queries
• Create reusable data objects for BI pipelines
• Clean and prepare data for reporting and modeling

Expanded Topics Covered:

• Installing and navigating SQL Server Management Studio (SSMS)
• Understanding tables, views, indexes, and primary/foreign keys
• Data types, constraints, and normalization principles (1NF–3NF)
• Writing optimized SELECT, WHERE, GROUP BY, ORDER BY queries
• JOIN types: INNER, OUTER, CROSS, SELF joins in real-world context
• Advanced filtering with subqueries and EXISTS/IN clauses
• CTEs, temporary tables, and table variables
• Analytical/window functions for ranking, running totals, lag/lead comparisons
• Stored Procedures and Functions for parameterized analytics
• Indexing and Query Optimization Basics
• Exporting query results to Excel or Power BI

Module 4: Power Apps Course Contents

Introduction to Power Apps

• What is Power Apps?
• Benefits and use cases
• Overview of Power Platform (Power BI, Power Automate, Power Apps, Power Virtual Agents)
• Power Apps types: Canvas Apps, Model-driven Apps, Portal Apps
• Setting up your Power Apps environment
• Power Apps Studio interface walkthrough

Getting Started with Canvas Apps

• Creating your first Canvas app from template and blank
• Understanding screens, controls, and layout
• Working with common controls: buttons, text inputs, labels, galleries, forms
• Data sources and connectors overview
• Connecting Power Apps to SharePoint, Excel, Dataverse, SQL Server, and others
• Managing app variables and collections

Power Apps Formulas and Functions

• Introduction to Power Fx language
• Common functions: Text, Date & Time, Logical, Math, Table, Filter, Search, Sort
• Using If, Switch, and nested conditions
• Working with context variables and global variables
• Delegation concept and limitations

Working with Data in Power Apps

• Understanding Dataverse and its architecture
• Creating and managing Dataverse tables and columns
• Working with relationships and lookups
• Integrating Power Apps with SharePoint lists
• Using CDS (Common Data Service) connectors
• Data validation techniques in forms

Advanced Canvas App Features

• Customizing app themes and styles
• Using media controls: images, audio, video
• Managing app navigation and transitions
• Using timers and animation effects
• Error handling and debugging techniques
• Implementing role-based access control

Model-Driven Apps Basics

• Introduction to Model-driven apps
• Understanding entities, views, and forms
• Business process flows and automation
• Customizing dashboards and charts
• Security roles and permissions

Power Automate Integration

• Overview of Power Automate
• Creating flows to automate tasks from Power Apps
• Triggering flows from Power Apps buttons
• Working with approvals and notifications
• Connecting Power Apps with external APIs via Power Automate

Power Apps Portals Overview

• What are Power Apps portals?
• Creating and customizing portals for external users
• Managing portal security and authentication
• Using Web APIs with portals

Testing, Publishing, and Sharing Apps

• App testing best practices
• Publishing and versioning apps
• Sharing apps with users and managing permissions
• Monitoring app usage and analytics

Free Projects for Practice

1. Employee Leave Request App
2. Expense Tracker
3. Event Registration App
4. Inventory Management System
5. Customer Feedback Portal

Module 5: Power Automate – Streamlining Workflows Across Apps

Learning Objectives:

• Understand flow architecture and build automated business processes
• Connect Microsoft 365 apps like Excel, Outlook, SharePoint, Teams, and more
• Replace manual workflows with scheduled, event-driven, or on-demand automation
• Use approvals, dynamic content, and conditional logic to create intelligent flows
• Deploy end-to-end automation solutions integrated with Excel VBA and MS Access

Topics Covered:

Getting Started with Power Automate

• Understanding cloud flows vs. desktop flows
• Accessing Power Automate from Office 365 portal
• Exploring prebuilt templates
• Navigating the Flow designer: triggers, actions, conditions
• Licensing overview: free, standard, premium connectors
• Key use cases and automation examples across departments

Types of Flows and When to Use Them

• Automated flows: trigger from events (e.g., form submitted, email received)
• Scheduled flows: run at specific intervals or times
• Instant flows: trigger manually from Power Apps, mobile, or browser button
• Desktop flows (Power Automate Desktop): automate desktop apps and legacy systems
• Business process flows (overview for structured multi-step approval or CRM logic)

Core Flow Components

• Triggers: detecting events in Outlook, SharePoint, Forms, Excel, etc.
• Actions: send email, update list, post to Teams, create files, update Excel rows
• Variables and dynamic content: creating and using data in steps
• Conditions: branching logic (If-Else, Switch, Parallel branches)
• Loops: apply to each, do until
• Expressions and functions: concat, substring, utcNow(), addDays(), etc.
• Using environment variables and connections for portability

Working with Microsoft 365 Connectors

• Outlook:
  • Auto-send replies or reminders
  • Monitor inbox for keywords
  • Save attachments to OneDrive or SharePoint
• Excel Online (Business):
  • Add rows from Forms
  • Trigger from Excel tables
  • Read data and write back dynamically
  • Create task logs
• SharePoint:
  • Trigger on item created/modified
  • Create folders/files
  • Update metadata or statuses
• Teams:
  • Post messages from a flow
  • Create channels or send notifications
• Microsoft Forms:
  • Trigger flows on form submission
  • Save responses to Excel or SharePoint
  • Send thank-you or conditional follow-up emails
• Planner:
  • Auto-create tasks from emails or form inputs
  • Update status when Excel task is marked complete

Power Automate with Excel and Access

• Automate Excel report distribution via email and Teams
• Move processed files from Excel into Access using bridge flows
• Update Access-linked SharePoint lists through a flow
• Monitor Access backend for changes (via SharePoint/OneDrive sync or connectors)

Document Automation & File Management

• Auto-generate invoices or letters from templates using Word Online (Business)
• Convert files to PDF and email or save
• Rename and organize files by naming convention or date
• Extract text from PDFs or scanned images (AI Builder – optional advanced)

Approval Workflows

• Create approval flows with Outlook or Teams
• Parallel or serial approvals
• Store responses in SharePoint or Excel
• Auto-reminders and escalation handling
• Notify status upon approval/rejection

Power Automate Desktop (Overview)

• When to use desktop automation
• Automating legacy systems or apps with no APIs
• Record mouse clicks and keyboard actions
• Interacting with Windows apps (Excel desktop, File Explorer, SAP, etc.)
• Use cases: form filling, batch printing, desktop-based data scraping

Monitoring and Error Handling

• Flow run history and troubleshooting tools
• Adding error-handling branches
• Retrying failed flows automatically
• Setting up notifications for failed or incomplete flows

Best Practices

• Naming conventions and documentation
• Using child flows and reusable templates
• Environment and connection management
• Securing flow access and data sharing
• Data loss prevention policies

Hands-On Projects:

• Automated Daily Report: Pull data from Excel table, generate a PDF, and email to team
• Leave Approval Workflow: Start from Microsoft Form, route to manager, send confirmation email
• Email-to-Task Automation: Convert flagged Outlook emails into Planner tasks and alert user in Teams
• Sales Lead Tracker: New Form submission → create SharePoint entry + notify sales team on Teams
• Invoice Processor: Save PDF invoices from email, rename, move to SharePoint folder, extract data
• Recurring Flow: Weekly extract of data from Excel → filtered summary sent to managers

Project: Smart Office Automation System
Build a complete business process automation scenario combining Excel, SharePoint, Outlook, and Teams:
Trigger flow on a new client entry in Excel → validate data → create a folder in SharePoint → send welcome email → notify account manager on Teams → auto-generate onboarding checklist in Planner

Objective:
Build a comprehensive system that integrates all three tools to manage and automate key business operations.

Features:

• Use Access as the backend to store employee or client records
• Design Excel VBA front-end to input data and generate formatted reports
• Use Power Automate to:
  • Send automated emails upon new entries
  • Notify stakeholders when inventory is low
  • Upload documents to OneDrive or SharePoint and alert via Teams
• Integrate with Outlook for task reminders
• Generate periodic PDFs and distribute to the team automatically

---

Bonus Add-ons

• Using SharePoint Lists with Power Automate and Access
• Connecting to SQL Server for backend scalability
• Securing Access databases with user logins and permissions
• Deploying Access + VBA applications in multi-user environments
• Creating Power Apps mini-forms that integrate with Access via Power Automate

Module 6: SharePoint Course Contents

Introduction to SharePoint:

• What is SharePoint?
• Key features and benefits
• SharePoint Online vs On-Premises overview

SharePoint Sites and Structure:

• Understanding SharePoint sites, subsites, and site collections
• Creating and managing sites
• Site templates and usage

Document Libraries and Lists:

• Working with document libraries
• Uploading, editing, and managing documents
• Creating and customizing lists
• Using views and filters

Permissions and Security:

• Understanding SharePoint permissions and groups
• Assigning and managing access
• Sharing documents and sites securely

Web Parts and Pages:

• Creating and customizing SharePoint pages
• Using web parts effectively
• Page layout and design tips

Collaboration Features:

• Co-authoring documents
• Versioning and version history
• Alerts and notifications

Integration and Automation:

• Integrating SharePoint with Microsoft Teams and Outlook
• Introduction to Power Automate with SharePoint
• Basic workflows in SharePoint

Search and Navigation:

• Using SharePoint search efficiently
• Managing site navigation

Administration Basics:

• Site settings overview
• Managing storage and quotas
• Monitoring site usage

Practical Projects:

• Team Collaboration Site setup
• Document Management System
• Project Tracking List