Explore

VBA WEB - Connect Excel to the WEB with VBA

19.5 hours on-demand video

$ 12.99

Save Course

Go to Course

Brief Introduction

Create VBA Web Scraping Tools to exchange information and data between Excel and the Web

Description

VBA was considered a dead language until new functionalities and objects have been made available to enable data exchange between MS Office products and the Web.

Today VBA offers functions to send HTTP requests, receive HTTP responses and parse the final results using technologies and objects e.g. DOM, XML parsing, JSON parsing, XML XPath, VBA Regular Expressions, ADODB connections to SQL Server and mySQL Databases, generate Web Browsers to embed into VBA forms and much more.

Before into details I have included some lectures for those that are new to understand and get to know Excel, Visual Basic for Applications (VBA) and HTML.

The students will learn the following in this course:

HTML DOM
HTML editing tools
Open a Web page in VBA
Create HTML Tables with from Excel Worksheets with VBA
Create HTML Files with a Table of Contents based on Cell Data
Download File from Website Using Excel
Scraping data from website using vba
Get data in an HTML table and display in an Excel file
Get all the data in an HTML table and display in an Excel file
VBA Code – To extract data – From website to Excel Macro with ServerXMLHTTP
Scraping a website HTML in VBA using the HTMLDocument object
Read data from a website using VBA web query
Import Website data to Excel
Embed a browser in an Excel VBA form
Identify information on a Web Page and display it in human readable way
Processing human friendly messages
Generate machine-friendly messages
Import JSON to Excel
Import an XML file into Excel
Get data from the web
Use the InternetExplorer Object to get Data from the Web
Use the InternetExplorer object to get elements of an HTML table
Retrieve single HTML elements using the InternetExplorer object
Parse and submit an HTML form using the InternetExplorer object
Trigger events of elements using the InternetExplorer object
Click on a button and trigger the associated event using the InternetExplorer object
Enter data dynamically into an HTML form using the InternetExplorer object
Use XMLHttp to send HTTP requests, receive HTTP responses and parse the content
Use ServerXMLHttp to send HTTP requests, receive HTTP responses and parse the content
Using ServerXMLHTTP to GET XML results from a Web page
Using ServerXMLHTTP to POST XML to a Web page
Providing authentication info via msxml2.ServerXMLHTTP (use JustGiving API)
Scrape html by element id, name after response using msxml2.ServerXMLHTTP
Save MSXML2.ServerXMLHTTP Response text is an HTML Page
Extracting data from the response of a serverXMLHTTP request
Extract a table of data from a website using a VBA query
Pull data into Microsoft Excel with Web queries
Import HTML content in Excel with Query Tables
Import HTML table with Web Query
Invoke a SOAP Web Service from custom VBA Code
Do a SOAP Request in VBA
Use VBA to send XML to WSDL
Create a SOAP Request/Response using XML from VBA
Get Http Request In Excel Vba
Build a VBA App from calling an API
Make REST call with VBA in Excel: WinHttp
Send HTTP requests with VBA from Excel with WinHttp
Get Http Request In Excel Vba with WinHttp
Get JSON Response with WinHTTP in VBA
Install and configure SQL Express Server and the Management tool for testing
Connect to a remote SQL Server DB With VBA & ADODB
Use Excel VBA to Query a SQL Server Database
Install and configure a mySQL Database for testing
Connect to remote MySQL Database
Connect to MySQL database in Excel with VBA and get the data
Send HTML Email from Excel using VBA
Show RSS Feeds in Excel
Read RSS feeds from VBA
create an rss feed with Excel VBA
Get Data from Bing Search and display the results using DOM
Display google maps on a UserForm
Calculate durations and distances of itineraries from Google Maps with VBA
Retrieve Address Geocoding with VBA and Google Maps
Parse strings from HTML code via VBA code using RegEx

...

Requirements

Requirements
Basic knowledge of Microsoft Excel
Basic knowledge of Excel VBA
Basic knowledge of Web and HTML

Knowledge

Their knowledge in Excel VBA will improve
Create Web Applications with VBA in Excel
Screp content from Web pages and import into an Excel file
Do HTTP requests and receive and parse HTTP response
Submit HTML forms from Excel
Import HTML tables into Excel
Export Excel tables to web pages
Embed functional web browsers objects int VBA forms
Call Google maps api and retrive geodetic information
Retrieve a Json file from a HTTP response
Retrieve an XML file from a HTTP response
load an HTML or XML file into a DOM Document object
Parse information into an HTML or XML DOM
Get access to single HTML elements
Access HTML elements using XPath
Accessing and parsing HTML elements with VBA Regular Expressions (RegEx)
Use and understand the InternetExplorer Object
Use and understand the VBA MSXML2 objects: XMLHHTP, ServerXMLHTTP, DOM DOcument
User and understand VBA WinHTTP
Connect to a remote SQL Server and mySQL Database via VBA and ADODB
Download and upload files using VBA and FTP
Generate a browser simulator with the WebBrowser object
Use Excel Web Queries to load content from the web
Retrieve data from webservices and API with VBA
Send HTML emails with VBA
Generate CSS from Excel format settings

Recommended for you

Up & Running with Tableau Desktop - Tableau for Beginners

Learn data analysis w/ Tableau and prepare for a BI career or Tableau certification (10+ hr, Tableau Server not covered) "Some of the BEST courses I've ever taken....

Save Course

The Ultimate Microsoft Project 2013 & 2016 Mastery Course

Learn to use Microsoft Project and ensure your next project is successful. Get 4 courses, 37 hours, and over 200 videos....

Save Course

Complete Web Automation with Excel VBA

Fill Out Web Forms Dynamically, Navigate Web Pages Intuitively, And Extract & Manipulate Data To Increase Efficiency! Introducing “Complete Web Automation with Excel VBA” course....

Save Course