Using Excel as a database with VBA
3.8 (69 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
690 students enrolled

Using Excel as a database with VBA

Connect Excel to external data using VBA and turn it into a database or application to manage data from different source
3.8 (69 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
690 students enrolled
Created by Daniele Protti
Last updated 7/2019
English
English [Auto]
Current price: $139.99 Original price: $199.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 14.5 hours on-demand video
  • 9 articles
  • 28 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
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
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
Course content
Expand all 191 lectures 15:18:56
+ Introduction
25 lectures 01:41:39
Documentation and resources
00:15
VBE: the VBA editor
07:54
What is a macro?
02:56
Record a macro
06:29
Macro security
05:37
The workbook
04:19
The Worksheet
03:35
Manage cells and ranges with VBA
05:12
The VBA project structure and composition
03:29
The Worksheet Module Code
03:27
The Worksheet Properties
05:03
The Worksheet Methods
02:44
The General variables and subroutines
05:23
The Workbook Methods
03:00
Variables and Constants
02:30
Subroutines and Functions
03:28
User Defined Functions
01:38
VBA Modules
03:06
The Worksheet Module
03:03
Public and Private Subroutines and Functions
08:13
Public and Private Variables
05:46
Variable Types and Declarations
03:29
+ Manage Excel Tables with VBA
30 lectures 02:05:08
Prepare a table
05:45
What Is a Table?
02:16
Format and manage a table
02:30
Retrieve an Excel table information with VBA
04:29
Select the header of an excel table with VBA
01:53
Select the body data of an Excel table with VBA
02:00
working with the columns of an Excel table with VBA
04:53
Select ranges in an Excel table with VBA
04:42
Automate style options of Excel tables with VBA
06:23
Insert Rows and Columns into an Excel Table
03:01
Delete parts of an Excel Table
04:59
Delete an entire Excel Table
00:53
Delete data in an Excel table
05:12
Loop through the columns of an Excel table
03:21
Loop through the rows of an Excel table
08:13
Store Excel Table Data Into an Array
06:00
Store Excel Table Data Into an Array: reference to a specific column
04:21
Resize an Excel Table
07:04
Resize an Excel Table by rows and/or columns
04:48
Clear Out an Excel Table: delete the elements
03:34
Clear out an Excel Table: clear the values
01:04
Get the list of tables in an Excel workbook
03:52
Get The Active Table in an Excel workbook
04:39
Insert a sum into an Excel table
04:25
Change formulas in total row in an Excel table
03:03
Create Tables in Excel with VBA
08:38
Set an Excel table back to a range
04:03
Sort Excel Tables with VBA
05:13
Filter Excel Tables with VBA
02:22
Clear Toggle Excel Table Filters with VBA
01:32
+ VBA Dictionary
23 lectures 01:34:52
Introduction
02:03
What is the VBA Dictionary?
04:59
Define a dictionary as native object
01:46
An example to understand what is VBA Dictionary: start from the VLOOKUP function
02:11
Verify and populate a Dictionary
06:46
Create a Dictionary as a Scripting object and as a generic object
04:12
Early versus Late Binding
02:40
Add Items to a VBA Dictionary
03:57
Assign a Value to a key in a VBA Dictionary
02:13
Check if a Key Exists
02:24
Remove items from a VBA Dictionary
05:43
The count property of a VBA Dictionary
02:00
The VBA Dictionary Keys and Case Sensitivity (I)
05:54
The VBA Dictionary Keys and Case Sensitivity (II)
04:01
Read the elements of a VBA Dictionary
06:45
Sort a VBA Dictionary by keys
10:25
Sort a VBA Dictionary by values
07:14
Case study: dictionaries with arrays (Part I)
02:42
Case study: dictionaries with arrays (Part II)
04:52
Case study: dictionaries with arrays (Part III)
05:24
Case study: dictionaries with arrays (Part IV)
03:44
Case study: dictionaries with arrays (Part V)
01:31
When to use a VBA Dictionary
01:26
+ VBA Collections
15 lectures 01:05:34
Introduction
03:44
What is a Collection?
04:13
Explain a collaction
02:31
Collections Vs Arrays?
08:17
Advantages and disadvantages of Collections
01:51
How to Create a Collection
01:43
Remove All items from a Collection
04:54
Remove All – An Alternative Method
02:24
Add items to a Collection
06:20
Get Items of a Collection
01:43
Add Items to a collection using a key
04:36
Verify if a Key exists in a Collection
03:36
Loops to Accessing all items in a Collection
06:34
VBA Arrays
07:57
Convert a collection to an Array
05:10
+ Manage Excel Pivot tables with VBA
21 lectures 01:18:03
Create A Pivot Table
13:36
Delete a specific Pivot Table
02:24
Delete All Pivot Tables
03:36
Retrieve the luist of pivot fields
03:47
Add Pivot Fields
06:27
Add Calculated Pivot Fields
02:19
Add Values Fields
01:42
Remove Pivot Fields
01:28
Remove All Pivot Fields
02:50
Remove Calculated Pivot Fields
02:11
Report Filter On A Single Item
02:54
Report Filter On Multiple Items
02:31
Clear Report Filter
01:35
Refresh Pivot Table(s)
02:11
Refresh all Pivot Tables in a workbook
01:19
Change Pivot Table Data Source Range
12:26
Grand Totals
01:52
Report Layout
01:45
Formatting A Pivot Table's Data
03:08
Formatting A Pivot Field's Data
02:03
Referencing Pivot Table Ranges in VBA
05:59
+ Excel QueryTables and VBA
11 lectures 01:08:21
Introduction
02:52
QueryTables in VBA
08:20
QueryTables and Querytables collections
05:43
The parameters needed to define a QueryTable
01:30
Import Access data into an Excel QueryTable using VBA (II)
07: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)
05:07
import several text files
04:01
import csv file into an Excel QueryTable in VBA
05:28
Append data from different text file to a QueryTable
09:48
Retrieve data from a uebsite and import into an Excel QueryTable with VBA
05:34
+ Excel: Exchange data with MS Access using VBA
9 lectures 01:08:06
Introduction
01:21
Import Access data into an Excel QueryTable using VBA (I)
07:40
Export Excel data to Access in VBA
09:30
Insert an Excel range of data into Access with ADODB VBA and SQL commands
11:01
Insert an Excel range of data into Access with ADODB VBA with recordset
09:47
Import an Excel table (listobject) into Access with VBA
10:29
Import Excel table into Access with VBA looping in the table data using SQL
04:44
Import Excel table into Access looping in the table data with ADO recordset
03:14
Update Access with Excel data with VBA
10:20
+ Working with XML files in VBA
29 lectures 02:47:01
Introduction
04:17
Import XML into QueryTable
04:38
Loading XML file in VBA
07:05
Loading XML structure in VBA
04:59
XML DOM nodes in VBA: DOMDocument, nodes and childnodes
03:35
XML DOM nodes in VBA: Childnodes items and length
04:14
XML DOM nodes in VBA: nodelists
01:29
XML DOM nodes in VBA: firstchild and children
00:46
XML DOM nodes in VBA: node text
02:01
XML DOM nodes in VBA: node attributes
00:43
XML DOM nodes in VBA: populate a range with XML nodes and their attributes
04:02
XPath in VBA
07:41
XML Attributes and tags in VBA
08:26
Parse XML in VBA and get single nodes
11:08
Simple routine to read into an XML file in VBA
08:56
Get data from the web
05:27
Searching through highest node level in XML Document
06:33
Export Excel data to XML file with VBA and XMLDOM
12:08
Export Excel data to XML file with VBA
11:35
Create an XML-file from a named Excel range
08:52
Import an XML file into an Excel range with ADODB
05:20
Excel XMLMAp import to worksheet in VBA
10:20
Generate an XSD file with VBA
03:07
Import XML data in an ordered list in Excel
06:13
MSXML node types
06:12
Load an XSD file in a XML MAP using OpenXML
05:25
Open XML directly and load it into an Excel Workbook
02:24
Manage XML errors
06:50
XML async attribute
02:35
+ Excel VBA and Web services
11 lectures 01:04:49
Introduction
11:43
Invoke a SOAP webservice from Excel
10:29
SOAP Request via VBA in Excel
02:39
SOAP - Using VBA to send XML to WSDL
04:55
Create a SOAP Request/Response using XML from VBA
03:36
Send HTTP requests with VBA from Excel with WinHttp
03:14
How to make REST call with VBA in Excel: WinHttp
04:22
How Do I Get Http Request In Excel Vba
02:28
Add a POST example
08:45
Http requests in Excel VBA
06:30
responseXML Property (IXMLHTTPRequest)
06:08
+ Working with JSON files in VBA
7 lectures 17:31
Introduction
00:22
The JSON libraries: do not rewrite the code
03:37
Import Json to excel
04:05
Read JSON from a file
02:44
Export excel to Json
03:12
Export excel to Json file
01:33
Export Excel to Nested JSON
01:58