Using Excel as a database with VBA

About This Course

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

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.

• use VBA to manage Excel table

• connect Excel with databases using VBA

• use VBA dictionaries to manage data