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 VBAEditor, the concept of macro and the concept of macro security andwill get acquainted with topics e.g. the workbook object, worksheet object, cells and ranges. You will learn how to record a VBAmacro. You will understand the VBA Project structure and composition, the Worksheet Module Code, the Workbook andWorksheet 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 WorksheetModules andVariable 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.VBADictionary: the students in this section will understand the VBAdictionary which is a moderndata 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 toverify and populate a Dictionary object, how to create a Dictionary as a Scripting object and as a generic object, how to add/removeItems and assign values to keysto a VBA Dictionary. We will discuss about how tocheck if a key exists and how to count the number of items in a dictionary. We will go throughsome case studiessee 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 VBAcollection 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 howto create a collection and do operations on collection items (remove/remove all/add/get). we will then see how to verify ifa 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 toimport several text files and CSV filesinto Excel Query Tables using VBA, how to append data from different text file to a Query Table. Finally we will see how to retrievedata From a website using a QueryTable.Exchange data with MS Access using VBA: we will have again an overview of how to export data from an ExcelQuery Tables to an Access databaseusingVBA: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 VBAobjects to manage XML documents e.g. MSXML,XMLDOM and XMLMap. We will see also how to importan XML file into an Excel range with ADODB and how to generate an XSD file with VBA. We will understand theMSXML 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: howto invoke a SOAP web service from Excel (do SOAP requests, WSDL, …). In this section also: how tosend HTTP requests with VBA from Excel with WinHttp, how to make REST call with VBA in Excel using theWinHttp object, how toget Http Request In Excel Vba. You will also have an overview of theIXMLHTTPRequest 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 aboutODBC Queries,OLE DB Queries, how to import and export data from/to text files, how to do Web queries, how to manage VBAOLEObjects, how to parse HTML code in Excel with VBA and how to send automaticemails 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 Exceldata structures.VBA is not dead with the evolution of the web applications, instead is evolving to offer more than we think.