Teach on Udemy

Turn what you know into an opportunity and reach millions around the world.

Learn More

Your cart is empty.

Keep shopping

Using Excel as a database with VBA

Name: Using Excel as a database with VBA
Rating: 4.1 (107 reviews)

Connect Excel to external data using VBA and turn it into a database or application to manage data from different source

Created byDan Prodi, Pipi Rahayu

Last updated 1/2021

English

What you'll learn

use VBA to manage Excel table
connect Excel with databases using VBA
use VBA dictionaries to manage data
manage complex Pivot tables using VBA in Excel
turn Excel into a database and application

Course content

11 sections • 191 lectures • 15h 18m total length

Course Structure2:55
Documentation and resources0:15
Introduction to VBA3:03
The Developer Ribbon5:05
VBE: the VBA editor7:54
Open the Visual Basic editor from Excel and save as macro-enabled workbook, exploring the menu, project list, properties, and code windows. Learn how VBA projects organize code within workbook objects.
What is a macro?2:56
Record a macro6:29
Macro security5:37
Explore macro security in excel's trust center, detailing the four macro settings, how to enable or disable macros with or without notification, and why these controls matter when using vba.
The workbook4:19
The Worksheet3:35
Explore how a worksheet fits into the Excel object model, access properties like name, and count worksheets within the active workbook, using the Application, Workbook, and Worksheet objects.
Manage cells and ranges with VBA5:12
The VBA project structure and composition3:29
The Worksheet Module Code3:27
The Worksheet Properties5:03
The Worksheet Methods2:44
The General variables and subroutines5:23
The Workbook Methods3:00
Variables and Constants2:30
Subroutines and Functions3:28
User Defined Functions1:38
VBA Modules3:06
Learn about the module on demand in VBA, a reusable component that holds variables, constants, functions, and subroutines, and can be exported or imported for use across workbooks.
The Worksheet Module3:03
Public and Private Subroutines and Functions8:13
Explore how public and private subroutines and functions control visibility across modules in VBA, enabling safe reuse of code, avoiding name conflicts, and protecting library code.
Public and Private Variables5:46
Variable Types and Declarations3:29
Explore common VBA variable types such as integer, long, single, double, boolean, and string, and how option explicit versus option implicit requires declaring every variable in Excel VBA.

Prepare a table5:45
Prepare a calendar-based excel table by creating and formatting a data column and generating months. Convert the range to a filterable table for database use.
What Is a Table?2:16
Format and manage a table2:30
Retrieve an Excel table information with VBA4:29
Select the header of an excel table with VBA1:53
Master how to use VBA to select parts of an Excel table, including the header, by defining an object, creating a function, and running a macro to select the header.
Select the body data of an Excel table with VBA2:00
working with the columns of an Excel table with VBA4:53
Select ranges in an Excel table with VBA4:42
Automate style options of Excel tables with VBA6:23
Learn to automate Excel table style options with VBA, toggling row and column banding, total row, first and last column, and filters via macros and recorded ribbon actions.
Insert Rows and Columns into an Excel Table3:01
Delete parts of an Excel Table4:59
Delete an entire Excel Table0:53
Delete data in an Excel table5:12
Loop through the columns of an Excel table3:21
Loop through the rows of an Excel table8:13
Store Excel Table Data Into an Array6:00
Store Excel Table Data Into an Array: reference to a specific column4:21
Resize an Excel Table7:04
Resize an Excel table with VBA by selecting the table three, setting its range in the Visual Basic Editor, and adjusting rows and columns to the desired size.
Resize an Excel Table by rows and/or columns4:48
Learn how to resize an Excel table by rows and columns using VBA, adjusting table size with row and column counts, and applying new dimensions to the existing table.
Clear Out an Excel Table: delete the elements3:34
Clear out an Excel Table: clear the values1:04
Get the list of tables in an Excel workbook3:52
Learn to retrieve all tables from an Excel workbook with VBA by looping through each worksheet, checking for tables, and printing a consolidated list of table names.
Get The Active Table in an Excel workbook4:39
Insert a sum into an Excel table4:25
Explore how to insert a sum into an Excel table by using the totals row, view totals with a list object, and automate with Visual Basic macros.
Change formulas in total row in an Excel table3:03
Create Tables in Excel with VBA8:38
Set an Excel table back to a range4:03
Sort Excel Tables with VBA5:13
Filter Excel Tables with VBA2:22
Clear Toggle Excel Table Filters with VBA1:32

Introduction2:03
What is the VBA Dictionary?4:59
Define a dictionary as native object1:46
Define a dictionary as a native VBA object, create and reference it, add, change, and remove keys, retrieve values by key, loop through entries, and support case-sensitive searches.
An example to understand what is VBA Dictionary: start from the VLOOKUP function2:11
Verify and populate a Dictionary6:46
Create a Dictionary as a Scripting object and as a generic object4:12
Early versus Late Binding2:40
Add Items to a VBA Dictionary3:57
Learn how to add items to a VBA dictionary, where keys can be any value and items include arrays or nested dictionaries; handle duplicates and multi-value keys.
Assign a Value to a key in a VBA Dictionary2:13
Learn to assign a value to a key in a VBA dictionary, adding new keys when absent and updating existing keys such as John.
Check if a Key Exists2:24
Remove items from a VBA Dictionary5:43
Learn how to remove items from a vba dictionary, including checking existence, removing specific keys, handling non-existent keys, and optionally removing all items to reset the dictionary.
The count property of a VBA Dictionary2:00
The VBA Dictionary Keys and Case Sensitivity (I)5:54
The VBA Dictionary Keys and Case Sensitivity (II)4:01
Read the elements of a VBA Dictionary6:45
Sort a VBA Dictionary by keys10:25
Sort a VBA Dictionary by values7:14
Case study: dictionaries with arrays (Part I)2:42
Case study: dictionaries with arrays (Part II)4:52
Create a VBA function to build a ranking table using a dictionary of teams and arrays for score, goals for, and goals against, printed to the sheet.
Case study: dictionaries with arrays (Part III)5:24
Case study: dictionaries with arrays (Part IV)3:44
Case study: dictionaries with arrays (Part V)1:31
When to use a VBA Dictionary1:26

Introduction3:44
What is a Collection?4:13
Explain a collaction2:31
Collections Vs Arrays?8:17
Advantages and disadvantages of Collections1:51
How to Create a Collection1:43
Learn to create a collection as a collection object, instantiate its class, and manage memory by creating or destroying the collection/connection.
Remove All items from a Collection4:54
Learn two ways to remove all items from a VBA collection: clear the items while preserving the collection, or set the collection to nothing to destroy it.
Remove All – An Alternative Method2:24
Add items to a Collection6:20
Get Items of a Collection1:43
Add Items to a collection using a key4:36
Verify if a Key exists in a Collection3:36
Loops to Accessing all items in a Collection6:34
Use for each loops to access all items in a collection, and compare with other loops to achieve cleaner and faster traversal of workbooks and worksheets in VBA.
VBA Arrays7:57
Convert a collection to an Array5:10

Create A Pivot Table13:36
Delete a specific Pivot Table2:24
Delete All Pivot Tables3:36
Loop through each worksheet and delete all pivot tables by clearing their table ranges, removing data and the table structure.
Retrieve the luist of pivot fields3:47
Add Pivot Fields6:27
Learn to add and arrange pivot fields in Excel using VBA, placing fields in rows, columns, and filters, adjusting positions, and formatting currency.
Add Calculated Pivot Fields2:19
Learn to add calculated pivot fields in Excel using VBA, add a supplier attribute to the people table, and count suppliers to reveal exact totals.
Add Values Fields1:42
Remove Pivot Fields1:28
Remove a pivot field from a people table using VBA, selecting by name or position, and verify that the field is removed.
Remove All Pivot Fields2:50
Hide and remove all pivot fields in a pivot table using VBA, reposition and redefine fields, and execute updates to clear the layout.
Remove Calculated Pivot Fields2:11
Report Filter On A Single Item2:54
Apply a filter to a single item in a field using VBA, clear existing filters, and verify the results by selecting a specific category such as fruit.
Report Filter On Multiple Items2:31
Apply a report filter for multiple items in Excel using VBA, selecting fruits and vegetables by category. Set the multiple page items property to true and view the filtered results.
Clear Report Filter1:35
Refresh Pivot Table(s)2:11
Refresh all Pivot Tables in a workbook1:19
Change Pivot Table Data Source Range12:26
Grand Totals1:52
Learn how to display or hide grand totals in the rows and columns using VBA, and observe how enabling or disabling grand totals affects what appears in your Excel tables.
Report Layout1:45
Formatting A Pivot Table's Data3:08
Formatting A Pivot Field's Data2:03
Referencing Pivot Table Ranges in VBA5:59

Introduction2:52
QueryTables in VBA8:20
Define and populate query tables in VBA by connecting to text data, specifying path and destination, importing data, and refreshing the table.
QueryTables and Querytables collections5:43
Learn how to work with the QueryTables collection in Excel using VBA, exploring reading and writing tables within worksheets, managing connections, and importing data from text files.
The parameters needed to define a QueryTable1:30
Import Access data into an Excel QueryTable using VBA (II)7:15
Import from text file into an Excel QueryTable using VBA (fixed width)12:43
Import from text file into an Excel QueryTable using VBA (delimited)5:07
import several text files4:01
import csv file into an Excel QueryTable in VBA5:28
Append data from different text file to a QueryTable9:48
Retrieve data from a uebsite and import into an Excel QueryTable with VBA5:34

Introduction1:21
Import Access data into an Excel QueryTable using VBA (I)7:40
Export Excel data to Access in VBA9:30
Insert an Excel range of data into Access with ADODB VBA and SQL commands11:01
Learn to transfer an Excel range into a Microsoft Access database using ADODB VBA and SQL. Establish a connection, select a range, build insert statements, and execute the transfer.
Insert an Excel range of data into Access with ADODB VBA with recordset9:47
Import an Excel table (listobject) into Access with VBA10:29
Import Excel table into Access with VBA looping in the table data using SQL4:44
Import Excel table into Access looping in the table data with ADO recordset3:14
Update Access with Excel data with VBA10:20

Introduction4:17
Import XML into QueryTable4:38
Loading XML file in VBA7:05
Loading XML structure in VBA4:59
XML DOM nodes in VBA: DOMDocument, nodes and childnodes3:35
XML DOM nodes in VBA: Childnodes items and length4:14
XML DOM nodes in VBA: nodelists1:29
Explore manipulating XML DOM nodes in VBA by working with nodelists, retrieving and replacing nodes, and tracking node positions to achieve consistent results.
XML DOM nodes in VBA: firstchild and children0:46
XML DOM nodes in VBA: node text2:01
XML DOM nodes in VBA: node attributes0:43
XML DOM nodes in VBA: populate a range with XML nodes and their attributes4:02
XPath in VBA7:41
XML Attributes and tags in VBA8:26
Master manipulating XML attributes and tag names in VBA by navigating catalog nodes, accessing attributes, retrieving book and author details, and exploring child nodes and node properties.
Parse XML in VBA and get single nodes11:08
Parse XML in VBA and get single nodes by using select single node, navigate through notes, worksheets, and tables, and extract data from the XML content.
Simple routine to read into an XML file in VBA8:56
Get data from the web5:27
Searching through highest node level in XML Document6:33
Export Excel data to XML file with VBA and XMLDOM12:08
Export Excel data to XML file with VBA11:35
Create an XML-file from a named Excel range8:52
Export a named Excel range to an XML file by creating a table, establishing a VBA connection and recordset, and streaming data to the XML file.
Import an XML file into an Excel range with ADODB5:20
Import an XML file into an Excel range using ADODB by establishing a connection, creating a recordset on a worksheet, and transferring data into a table or named range.
Excel XMLMAp import to worksheet in VBA10:20
Generate an XSD file with VBA3:07
Generate an xsd file with VBA from an Excel workbook, defining a book structure with fields like title and author, and integrate the schema with the workbook.
Import XML data in an ordered list in Excel6:13
MSXML node types6:12
Learn how to work with MSXML node types in VBA by inspecting element, attribute, and text nodes, reading attributes and node values, and printing data.
Load an XSD file in a XML MAP using OpenXML5:25
Open XML directly and load it into an Excel Workbook2:24
Manage XML errors6:50
XML async attribute2:35

Introduction11:43
Invoke a SOAP webservice from Excel10:29
SOAP Request via VBA in Excel2:39
Learn to send a soap request from Excel using VBA, set content type headers, receive the response, and parse and import the data into an Excel table.
SOAP - Using VBA to send XML to WSDL4:55
Create a SOAP Request/Response using XML from VBA3:36
Send HTTP requests with VBA from Excel with WinHttp3:14
How to make REST call with VBA in Excel: WinHttp4:22
How Do I Get Http Request In Excel Vba2:28
Add a POST example8:45
Http requests in Excel VBA6:30
Learn how to perform HTTP requests in Excel VBA to interact with websites or APIs, retrieve information, and handle responses using GET requests and response text.
responseXML Property (IXMLHTTPRequest)6:08

Introduction0:22
The JSON libraries: do not rewrite the code3:37
Import Json to excel4:05
Import json to excel using vba by fetching data from a web service and parsing it with a library, then map fields to worksheet headers like id, name, and city.
Read JSON from a file2:44
Export excel to Json3:12
Export excel to Json file1:33
Export Excel data to a JSON file using VBA by building dictionaries, storing them in a collection, and writing the JSON output to a workbook directory.
Export Excel to Nested JSON1:58

Requirements

Basic knowledge of PC Windows and/or MAC
Basic knowledge of Microsoft Excel
Basic knowledge of VBA macros

Description

This course has been created to teach how to turn an Excel workbook into a Database application using VBA.

Before creating the database application in the first sections I explain the main VBA data structures which are helpful to manage data to be imported to Excel and to be exported from Excel ranges to external sources e.g. databases, web services , XML files, text files and JSON files.

In details, the course includes:

Overview of VBA. Very important for those that are starting off with VBA. In this section the students will know how to access and know the Excel Developer Ribbon. the VBA Editor, the concept of macro and the concept of macro security and will get acquainted with topics e.g. the workbook object, worksheet object, cells and ranges. You will learn how to record a VBA macro. You will understand the VBA Project structure and composition, the Worksheet Module Code, the Workbook and Worksheet Properties and Methods, the General variables and subroutines, concepts e.g. Variables and Constants (Public and Private Variables), Subroutines and Functions (Public and Private Subroutines and Functions), User Defined Functions, VBA and Worksheet Modules and Variable Types and Declarations.

Manage Excel Tables with VBA. In this section you will learn what is an Excel table and how to prepare, format and manage it, how to the information contained in an Excel table, how to select areas of a table, how to insert rows,/columns/parts of a table, how to read and retrieve the elements of a table and how to manipulate and filter elements in a table.

VBA Dictionary: the students in this section will understand the VBA dictionary which is a modern data structure which can be also defined as a native object. The idea of dictionary starts from the Excel LOOKUP functions. We will see also how to verify and populate a Dictionary object, how to create a Dictionary as a Scripting object and as a generic object, how to add/remove Items and assign values to keys to a VBA Dictionary. We will discuss about how to check if a key exists and how to count the number of items in a dictionary. We will go through some case studies see how to read, sort and format dictionaries and how to manage dictionaries with arrays. Finally we will understand when to use a dictionary as a data structure.

VBA Collections: in this section we will see what a VBA collection is and what is the difference between collections and arrays. We will also see when to use collections and when to use arrays and their advantages and disadvantages. Then in details we will see how to create a collection and do operations on collection items (remove/remove all/add/get). we will then see how to verify if a key exists in a Collection and how to retrieve and count items in collections. Finally we will be able to convert a collection to an array.

Manager Pivot tables with VBA: this is an hot section where you will dominate Excel Pivot table with VBA, learning: how create/delete a pivot table, how to retrieve the list of pivot fields, how to do operations on the items of a pivot table (add/remove/filter/clear). We will get familiar with the difference between calculated and not calculated fields. We will see how to do some operations on the pivot tables: clear report filter, refresh, change data source range, layout and setup the grand totals.

Excel Query Tables and VBA: this is the section where you can learn how to import data from an Microsoft Access database, how to import from text file (fixed width/delimited), how to import several text files and CSV files into Excel Query Tables using VBA, how to append data from different text file to a Query Table. Finally we will see how to retrieve data From a website using a Query Table.

Exchange data with MS Access using VBA: we will have again an overview of how to export data from an Excel Query Tables to an Access database using VBA:
insert an Excel range of data into Access with ADODB VBA and SQL commands, insert an Excel range of data into Access with ADODB VBA with recordset, import Excel tables (listobjects) into Access with VBA, import Excel table (listobjects) into Access with VBA looping in the table data (SQL), import Excel tables (listobjects) into Access with VBA looping in the table data (recordset), update Access with Excel data with VBA.

Working with XML files in VBA: learn how to import XML into Query Table or in an Excel range, retrieve the DOM structure of an XML document and import elements, nodes and attributes into Excel files, how to breakdown an XML document and parse the single elements, how to identify nodes, elements, attributes and node lists. You will also learn to get XML elements and nodes using XPath. You will get familiar with the Excel VBA objects to manage XML documents e.g. MSXML, XMLDOM and XMLMap. We will see also how to import an XML file into an Excel range with ADODB and how to generate an XSD file with VBA. We will understand the MSXML node types and we will learn how to manage errors and attributes e.g. async.

Excel VBA and Web services: in this section the students have an overview of the SOAP and REST web services and how they are managed via VBA to send data to and from Excel files. In details: how to invoke a SOAP web service from Excel (do SOAP requests, WSDL, ...). In this section also: how to send HTTP requests with VBA from Excel with WinHttp, how to make REST call with VBA in Excel using the WinHttp object, how to get Http Request In Excel Vba. You will also have an overview of the IXMLHTTPRequest object.

Working with JSON files in VBA: JSON documents are another data structure to use for sending data over the internet through web services. In this section students will learn how to import JSON data to Excel workbooks and how to export data in Excel ranges to JSON documents. At the end students will be able also to generate nested JSON.

Miscellaneus: this is a bonus area where students will get more information about ODBC Queries, OLE DB Queries, how to import and export data from/to text files, how to do Web queries, how to manage VBA OLEObjects, how to parse HTML code in Excel with VBA and how to send automatic emails from Excel with VBA.

At the end students will be able to connect Excel with the rest of the world using the HTTP protocol, web services and database connections to import/export data using Excel data structures.

VBA is not dead with the evolution of the web applications, instead is evolving to offer more than we think.

Who this course is for:

who has to manage data in Excel
who needs to make data analysis with Excel
who wants to use VBA to manipulate Excel tables
who wants to use VBA to use dictionaries as data structure
who wants to connect Excel with external data
who needs to manage pivot tables with VBA

Using Excel as a database with VBA

What you'll learn

Explore related topics

Course content

Introduction25 lectures • 1hr 42min

Manage Excel Tables with VBA30 lectures • 2hr 5min

VBA Dictionary23 lectures • 1hr 35min

VBA Collections15 lectures • 1hr 6min

Manage Excel Pivot tables with VBA21 lectures • 1hr 18min

Excel QueryTables and VBA11 lectures • 1hr 8min

Excel: Exchange data with MS Access using VBA9 lectures • 1hr 8min

Working with XML files in VBA29 lectures • 2hr 47min

Excel VBA and Web services11 lectures • 1hr 5min

Working with JSON files in VBA7 lectures • 18min

Requirements

Description

Who this course is for: