Excel VBA Basic to Advance Part3 ( 12 hours )

Name: Excel VBA Basic to Advance Part3 ( 12 hours )
Rating: 4.3 (5 reviews)

In-depth study of VBA Charts,VBA Pivots and Powerpoint Application Connectivity ,Database Connectivity -Advance Level

Created byajay parmar

Last updated 8/2024

English

English [Auto],

What you'll learn

You will be able to learn how to create Charts using VBA
You will under Charts classes to work with existing and new Charts
How to work with more than 1 chart and create multiple charts and how to access charts across sheets or few ones
We are learning how to work with Pivots using VBA. How to automate your pivot reports
Everything that exists in VBA about Pivots is discussed in this series from working with existing pivots to creating fresh new pivots
Collection loops used in Pivots are explained
How to connect database with VBA using SQL command . Del, modify , change and insert , retrieving the information is so easy.

Course content

4 sections • 34 lectures • 11h 48m total length

What are you going to learn?4:37
Master chart automation in Excel VBA, including creating, updating, and deleting charts across sheets, handling existing charts, and pivot automation with slicers, fields, and error handling.
Introduction to Charts - How to Create21:35
Master automating charts in Excel with VBA by recording creation, naming charts, setting data sources, and positioning chart objects through macros.
Errors Solutions in VBA - Charts are easy if you understand fundamentals6:34
Learn fundamentals of managing charts in VBA, including creating, deleting, and updating chart objects, handling chart names like chart one, and using on error resume next for robust automation.
How to Create multiple Charts17:24
Learn to automate creating multiple charts across worksheets with a macro, using a glossary sheet to specify data ranges and chart types (pie, line, bar) and place charts dynamically.
Alignment in Charts - Resizing and dimensioning14:29
Explore aligning and resizing charts with Excel VBA, building modular macros, managing active chart objects, and applying consistent chart styles and dimensions across sheets.
Collection Loop role in Charts15:34
Learn how collection loops handle charts in Excel VBA, deleting all charts across sheets, manipulating chart objects, and applying width, height, and chart type changes efficiently.

Create Pivots in VBA - Begin the show22:43
Create and modify pivots in VBA by recording macros, defining pivot cache and pivot table sources, and building single or multiple pivots on any sheet.
How to add Pivot Fields13:24
Learn to build and customize pivot tables with VBA by defining the pivot cache, adding fields to rows, columns, and filters, applying tabular layout, and configuring refresh on file open.
Collection Loops in Pivot Fields8:26
Learn to access pivot fields in an existing Excel pivot table using VBA with a collection loop, including understanding pivot caches and the difference between pivot fields and items.
How to retrieve fields3:47
Explore how to use a collection loop to retrieve items from a pivot field, specifically the zone field, and capture pivot item names to automate Excel VBA tasks.
Control Pivot fields from excel cells28:52
Control pivot fields from Excel cells using VBA, building and updating pivot tables with macros, for dynamic visibility of pivot items via data validation and user input.
Project for you - Gain more confidence5:32
Create and manage pivots in Excel VBA by defining dynamic ranges and placing pivots at G2 and G10, using templates and user inputs for sheet and range.
Work with Pivots across Worksheets15:11
Learn to update every pivot across all worksheets in excel using vba, changing sum to average and aligning captions and fields, with practical debugging tips for pivot properties.
Loop Slicers in Pivots23:40
Explore how to work with slicers in Excel VBA by looping slicer caches and listing slicer items. Reference slicer names to manage pivot data efficiently.
How to control Slicers and then Pivots - SuperPowerful24:57
learn to control multiple pivots with one slicer by looping through all pivot tables and filtering each by a shared route number field.

How to connect PPT with Excel VBA8:38
Understand the PowerPoint object hierarchy and connect PowerPoint with Excel VBA using the PowerPoint 16.0 object library, early binding, and dot operator to export charts or ranges.
How to add a presentation and a slide5:49
Create a PowerPoint presentation from VBA, add slides, and apply layouts to place charts and text.
Add multiple Slides7:19
Learn how to automate PowerPoint with VBA to add and manage slides, create presentations, and select specific slides by index.
How to use a Loop to add 100 Slides in a second4:25
Learn to automate PowerPoint slide creation with Excel VBA by looping from 1 to 50 to add slides, manage slide indices, and save the presentation.
Open a existing PPT5:04
Learn to open and work with an existing PowerPoint in VBA by using PowerPoint.Application, making it visible, and opening the presentation to edit slides and charts.
How Count total current Slides - Useful in projects4:18
learn to count the number of slides in an existing powerpoint presentation using vba by referencing the active presentation’s slides.count and displaying the result with a message box.
Delete, or Select a specific Slide with How to add Charts - New PPT30:00
Master deleting or selecting a specific slide in PowerPoint with VBA, then insert and position charts copied from Excel as bitmap on the slide.
Paste Multiple Charts from multiple Sheets23:31
Export charts from multiple Excel sheets to PowerPoint using the PowerPoint object hierarchy, early binding, and collection loops to create slides and paste charts.
Add Charts in any current Slide in a existing PPT24:10
Use Excel VBA to copy charts from worksheets to an existing PowerPoint, using the active presentation and creating new slides for each chart.
Collection Loops for SLIDES & SHAPES - Most Powerful48:00
Explore collection loops in PowerPoint VBA to iterate slides and shapes, delete charts and text boxes safely, and master for each and reverse-index strategies.
Project Part1 Begins - What are we going to do?5:01
Shows how to connect Excel and PowerPoint with a VBA macro that copies a range from Excel and pastes it into slide nine, with prompts for file, sheet, and range.
Project Part2 - Using File Dialogs to Open Excel & PPTs11:46
Implement two file dialogs in excel vba to select and open PowerPoint and Excel files, with filters for pptx and xlsx/xlsm, and handle the selected items.
Pick up Ranges and Slides of your Choice - Part339:26
Learn to automate PowerPoint from Excel using VBA to pick slides and ranges, activate presentations, select and delete shapes, copy ranges, paste and resize, and position new content.
New Big Project - Created for my client - Super Advance level57:26
Develop an advanced Excel VBA project that dynamically copies charts and ranges from Excel to an existing PowerPoint using file dialogs, data validation, and linked updates.

What are we going to do?6:57
Explore integrating Excel with Access using Excel VBA and the Edo Library ActiveX Data Object Library, covering select, delete, and update queries, connections, and practical data practice with downloadable projects.
Import ms access table in excel38:03
Learn to pull data from Access into Excel with VBA and SQL, building select queries and where criteria for numbers and text, including proper quotes.
Update records using VBA UserForms1:12:15
Learn to update access tables from Excel VBA using update queries, including syntax, criteria, and text handling, and apply these with a user form interface.
Delete & Change the database tables using VBA56:54
Learn to write and execute delete queries in Access VBA, using where clauses to remove specific records, or delete star to clear entire tables with SQL syntax.
Insert Data32:22
Learn to insert data into Access tables from Excel VBA using the insert into syntax, including building a form, mapping fields to values, and handling quotes and data types.

Requirements

You should already know how to create Charts and Pivots in Excel. This is a VBA Course so here we not learning what are charts and pivots.
We are learning how to automate them using VBA

Description

In first Section, we are learning about Chart automation. How to create new charts iusing VBA. We can fully automate the making of charts using VBA.
Learn the Chart class called Chart and see how easy it is to create the new charts.
The role of macro recordings plays a very important role here as most of the code we can record and later customize as per the requirement.
How to work with existing charts in excel. How to go to each of them one by one and do modifications. Any type of modification like changing chart design or editing labels or chart titles etc
Understand the Loops importance in charts. You can loop through infinite charts using one simple collection loop.
Collection loops of charts - to work with multiple charts either to delete them or to edit their design, colors. You can do whatever. Even change the axis's.
Use of other Collection loops with chart collection loops. Loops in Loops
In section2, you will become master in Pivots. From making new pivots to working in existing pivots and modifying them
Collection loops in PIvots - how to access pivots . How to loop pivot field items and fully control it.
We will learn the classes we use in Pivots to handle new pivots or existing pivots like Pivot-table and Pivot cache classes.
We will take a deep dive into knowing other classes as well which helps us in controlling the pivot fields and field items like pivot item class and pivot-Field class.
How to mix worksheet collection loops with pivot collection loops in real time projects and what are the benefits of same
What is the role of macro recording in making pivots and how to edit those recording if requirement changes.
How to work with row or column of filter pivot fields. Line by line explanation.

In Section3 , we are learning how to connect Powerpoint with Excel VBA.
Discussing about the connectivity of Excel with PowerPoint in this advance series using Excel VBA.
How to copy table data from excel and paste into PowerPoint application slides.
How to export charts from excel sheets to PowerPoint and paste them in one slide or more slides one by one.
Taking a deep dive into power-point classes like Presentation class, Slide class and shapes class. Understanding the power-point object hierarchy.
How every class is responsible for other class using the dot hierarchy.
How to work with already created PowerPoint applications and also how to create new PowerPoint applications and work with them.
How to resize the charts or tables images in slides and move them to the top or left or right of the slides.
Control which chart should go in PowerPoint using the excel sheet cells.
How to loop through all sides and decide which one to be deleted or used for data presentation.
Collection loops for slides as well as shapes. This is amazing source of learning.
Real time projects discussion with you to give you live exposure of working with Power-point.
Understanding the concept of Early Binding and Late Binding methods or techniques
Several highly advance level Projects for you - Fully automated and very very knowledgeable practical projects.

In Section4 , we are learning how to connect with databases like ms access using SQL commands in Excel VBA.
How to retrieve information from a database using SQL in vba
How to delete/ update and insert the information in database like ms access using VBA and also using Userforms

Who this course is for:

Data Analysts or Data scientist who work on excel and want to produce pivot and charts in one click

Excel VBA Basic to Advance Part3 ( 12 hours )

What you'll learn

Explore related topics

Course content

VBA CHARTS6 lectures • 1hr 20min

VBA PIVOTS9 lectures • 2hr 27min

Connectivity with PowerPoint- Powerful automation14 lectures • 4hr 35min

Connecting Databases with Excel using VBA5 lectures • 3hr 27min

Requirements

Description

Who this course is for: