What you would learn in Using Excel as a database with VBA course?
This course teaches you how to convert an Excel spreadsheet into a Database application with VBA.
In the first section, I will explain VBA data structures that are useful to manage Excel data. Databases, web services, XML, text, and JSON files.
The course includes the following:
VBA Overview. This is a crucial section for anyone who is just starting to use VBA. This section will teach students how to access the Excel Developer Ribbon. The VBA Editor, macro security, and topics, e.g., The workbook object, worksheet object, and cells, are all available. Learn how to create a VBA macro. The VBA Project structure, composition, Worksheet Module Code, Workbook Properties and Methods, General Variables and Subroutines, concepts, e.g., Variables & Constants (Public & Private Variables), Subroutines & Functions (Public / Private Subroutines e Functions), Workbook and Worksheet Properties and Methods, the General Variables a User Defined Functions.
Use VBA to manage Excel tables. This section will explain what an Excel table is and how it can be prepared, formatted, and organized. It also explains how to select areas from an Excel table, insert rows/columns/parts, read and retrieve elements from a table, and manipulate or filter them.
VBA Dictionary - This section teaches students how to use VBA dictionary, a modern data structure that can also be used as a native object. The Excel LOOKUP functions are the foundation of the idea of a dictionary. We'll also learn how to populate and verify a Dictionary object. We'll discuss how to verify that a key exists and count how many items are in a dictionary. We'll look at some cases and learn how to format, sort, and read dictionaries. We will also know when to use a dictionary for data structures.
VBA collections: This section will explain what a VBA set is and the differences between arrays and groups. We'll also discuss the advantages and disadvantages of displays and how to use them. In the next section, we'll show you how to create a Collection and perform operations (remove/remove ALL/add/get). We will also learn how to check if there is a key in a collection and retrieve or count items within collections. Finally, we will be able to convert a group into an array.
Manager Pivot tables with VBA: this is a hot section where you will dominate Excel Pivot table with VBA, learning: how to 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 learn the differences between calculated and uncalculated fields. We'll learn to perform certain operations on pivot tables: clear filter, refresh, alter data source range, layout, and set up the grand totals.
Excel Query Tables & VBA: This section teaches you how to import data into Microsoft Access databases, how to read text files (fixed-width/delimited), how many text files can be imported into Excel Query Tables with VBA, and how to add data from different text files to a Query Table. We will also learn how to retrieve data from a website using the Query Table.
Use VBA to exchange data with MS Access: We will again show you how to export data to Access from Excel Query Tables using VBA.
Working in XML files with VBA: Learn how to import XML in Query Table or into an Excel range, retrieve XML structure from an XML file, and import elements, nodes, and attributes into Excel files. Also, learn how to break down an XML text and identify nodes, elements, and attributes. You'll also learn how to obtain XML nodes and elements using XPath. To manage XML documents, you will be familiar with Excel VBA objects. MSXML, XMLDOM, and XMLMap will be covered. We'll also learn how to import an XML document into an Excel range using ADODB and create an XSD file using VBA. We will understand the MSXML node types and how to manage errors or attributes (e.g., async.
Excel VBA & Web Services: This section gives students an overview of SOAP and REST web services and how they can be managed via VBA to send data from and to Excel files. Details: How to invoke a SOAP Web service from Excel (do SOAP request, WSDL and ...).). This section also covers how to send HTTP requests from Excel using WinHtp. How to make a REST call in Excel with VBA using WinHtp. Also, how to access HTTP Request In Excel VBA. An overview of IXMLHTTPRequest will be provided.
Working in JSON files with VBA: JSON data structures can send data over the internet via web services. This section will teach students how to import JSON data into Excel workbooks and export Excel ranges data to JSON documents. Students will also be able to create nested JSON at the end.
Miscellaneous -This bonus area will give students more information about ODBC Queries and OLE DB Queries. It also teaches how to import/export data from/to text files. Web queries are how to use Web queries. How to manage VBA OLEObjects. How to parse HTML in Excel with VBA. How to send automated emails from Excel using VBA.
Students will be able to connect Excel to the rest of the globe using the HTTP protocol, web service, and database connections.
VBA isn't dead with the evolution of web applications. Instead, VBA is evolving to offer more than what we think.
- Use VBA to manage Excel tables
- VBA allows Excel to be connected with other databases
- Use VBA dictionaries for data management
- Excel VBA will enable you to manage complex Pivot Tables
- Excel can be transformed into a database or application