VBA Web Course: Connect MS Office to the Web

VBA WEB - Connect MS Office to the Web

Create VBA Web Scraping Tools to send and read data to/from the Web

Created by : Rated :4.1 27082 views

Last Updated: 2024-11-19 06:11:01 English Course Type: Self Learning

Their knowledge in Excel, Word, PowerPoint, Access VBA will improve
Create Web Applications with VBA in Excel/Word/PowerPoint
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

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

...

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

Who already knows how to use Excel and create VBA macros
Who needs to import/export data from servers and websites to/from Excel
Who wants to create web applciations using Excel

Section 1 : Introduction 8 Lectures 00:54:47
Lecture 1 :
Introduction Preview
Lecture 2 :
Course structure
Lecture 3 :
Basic concepts you need to know
Lecture 4 :
Basic HTML and DOM
Lecture 5 :
HTML DOM - Overview
Lecture 6 :
Basic operations
Lecture 7 :
IE and Chrome Developer tools
Lecture 8 :
Important tools to use
Section 2 : Basic Excel 13 Lectures 00:49:10
Lecture 1 :
Excel - Explore Window
Lecture 2 :
Excel - Workbook
Lecture 3 :
Excel - Worksheet
Lecture 4 :
Excel - Introduction and Overview
Lecture 5 :
Excel - work with data in cells (input, insert, copy, paste, delete,â€¦)
Lecture 6 :
Excel - rows and columns (select, add, insert, delete)
Lecture 7 :
Excel - cell formatting (color, font, borders, text alignment)
Lecture 8 :
Excel - enter formulas
Lecture 9 :
Excel - Data Validation
Lecture 10 :
Excel - Conditional formatting
Lecture 11 :
Excel - tables
Lecture 12 :
Excel - filtering and sorting
Lecture 13 :
Excel - ranges (selecting, naming)
Section 3 : Basic VBA 22 Lectures 02:43:23
Lecture 1 :
VBA - Introduction and Overview
Lecture 2 :
VBA - Excel Macros
Lecture 3 :
VBA - Excel Terms
Lecture 4 :
VBA - Macro Comments and code
Lecture 5 :
VBA - Message Box
Lecture 6 :
VBA - Input Box
Lecture 7 :
VBA - Variables
Lecture 8 :
VBA - Constants
Lecture 9 :
VBA - Operators
Lecture 10 :
VBA - Decisions
Lecture 11 :
VBA - Loops -forâ€¦next
Lecture 12 :
VBA - Loops - while, until
Lecture 13 :
VBA - Strings
Lecture 14 :
VBA - Date and Time
Lecture 15 :
VBA - Arrays
Lecture 16 :
VBA - Functions
Lecture 17 :
VBA - Sub Procedure
Lecture 18 :
VBA - Events
Lecture 19 :
VBA - Error Handling
Lecture 20 :
VBA - Excel Objects
Lecture 21 :
VBA - UserForms
Lecture 22 :
VBA - Classes
Section 4 : Basic HTML 26 Lectures 02:06:08
Lecture 1 :
HTML - Introduction and Overview
Lecture 2 :
HTML - Basic Tags
Lecture 3 :
HTML - Elements
Lecture 4 :
HTML - Attributes
Lecture 5 :
HTML - Formatting
Lecture 6 :
HTML - Phrase Tags
Lecture 7 :
HTML - Meta Tags
Lecture 8 :
HTML - Comments
Lecture 9 :
HTML - Images
Lecture 10 :
HTML - Tables
Lecture 11 :
HTML - Lists
Lecture 12 :
HTML - Text Links
Lecture 13 :
HTML - Image Links
Lecture 14 :
HTML - Email Links
Lecture 15 :
HTML - Frames
Lecture 16 :
HTML - Iframes
Lecture 17 :
HTML - Blocks
Lecture 18 :
HTML - Backgrounds
Lecture 19 :
HTML - Colors
Lecture 20 :
HTML - Fonts
Lecture 21 :
HTML - Forms
Lecture 22 :
HTML - Embed Multimedia
Lecture 23 :
HTML - Header
Lecture 24 :
HTML - Style Sheet
Lecture 25 :
HTML - Javascript
Lecture 26 :
HTML - Layouts
Section 5 : Using VBA to scrap web pages 6 Lectures 00:44:06
Lecture 1 :
Overview of my VBA Web Scraping Tools
Lecture 2 :
Overview of the VBA objects for WEB scraping, accessing and parsing (I)
Lecture 3 :
Overview of the VBA objects for WEB scraping, accessing and parsing (II)
Lecture 4 :
Open a web page in VBA (I)
Lecture 5 :
Open a web page in VBA (II)
Lecture 6 :
The HTMLDocument object
Section 6 : Using VBA to export Excel data to HTML 4 Lectures 00:30:13
Lecture 1 :
Create HTML Tables with Excel VBA
Lecture 2 :
VBA to create an HTML table from the current Excel worksheet
Lecture 3 :
Create an HTML File with a Table of Contents based on Cell Data
Lecture 4 :
Export Excel data to HTML files â€“ Convert Excel to HTML and publish to the web
Section 7 : Web Data exchange with VBA 10 Lectures 01:00:42
Lecture 1 :
Introduction & Resources
Lecture 2 :
Download File from Website Using Excel
Lecture 3 :
Scraping data from website using VBA
Lecture 4 :
Get data in an HTML table and display in an Excel file
Lecture 5 :
Get all the data in an HTML table and display in an Excel file
Lecture 6 :
VBA Code to extract data from website to Excel Macro with ServerXMLHTTP
Lecture 7 :
Scraping a website HTML in VBA using the HTMLDocument object
Lecture 8 :
Read data from a website using VBA web query
Lecture 9 :
Import Web site data to Excel
Lecture 10 :
How do I embed a browser in an Excel VBA form
Section 8 : Internet Security 4 Lectures 00:33:16
Lecture 1 :
Accessing a Secure Web Site Using VBA
Lecture 2 :
VBA to Enter Data Online and Submit Form (I)
Lecture 3 :
VBA to Enter Data Online and Submit Form (II)
Lecture 4 :
VBA to Enter Data Online and Submit Form (III)
Section 9 : Using VBA to access the Net 6 Lectures 00:38:23
Lecture 1 :
Identify the information and display it in human readable way
Lecture 2 :
Processing the human friendly message
Lecture 3 :
The machine-friendly message
Lecture 4 :
Import JSON to Excel
Lecture 5 :
Import an XML file into Excel
Lecture 6 :
Get data from the web
Section 10 : The InternetExplorer Object 23 Lectures 02:10:39
Lecture 1 :
InternetExplorer in Action
Lecture 2 :
The InternetExplorer with Events
Lecture 3 :
Internet Explorer Automation using Excel VBA
Lecture 4 :
Get a web page content with VBA
Lecture 5 :
Web Scraping VBA Macro to get the elements of an HTML table (I)
Lecture 6 :
Web Scraping VBA Macro to get the elements of an HTML table (II)
Lecture 7 :
Web Scraping VBA Macro to get the elements of an HTML table (III)
Lecture 8 :
Web Scraping VBA Macro to get the elements of an HTML table (IV)
Lecture 9 :
Web Scraping VBA Macro to get the elements of an HTML table (V)
Lecture 10 :
Web Scraping VBA Macro to get the elements of an HTML table (VI)
Lecture 11 :
VBA Script to scrape info from HTML table/input values/div elements
Lecture 12 :
HTML Forms
Lecture 13 :
Retrieve and manage Events in HTML tables with VBA
Lecture 14 :
Submit an HTML form with VBA
Lecture 15 :
Extract HTML tags with VBA
Lecture 16 :
Using VBA to open web page, find text, and click on text's URL link
Lecture 17 :
Using VBA to enter data into an HTML form
Lecture 18 :
Return the entire HTML document
Lecture 19 :
What to do to avoid to run code before Internet Explorer has fully loaded
Lecture 20 :
Open URL and Enter Data in Form Using VBA
Lecture 21 :
Sendkeys to Internet Explorer
Lecture 22 :
How to constantly keep the focus back onto the IE window
Lecture 23 :
Get value from web document input element with VBA
Section 11 : XMLHttp in VBA 3 Lectures 00:07:39
Lecture 1 :
The VBA XMLHttp object
Lecture 2 :
Key XMLHttp properties and methods
Lecture 3 :
Using XMLHttp to retrieve data in a synchronous
Section 12 : HTML and Excel VBA with HTMLDocument and MSXML2.XMLHTTP 10 Lectures 00:49:12
Lecture 1 :
Working with the Internet with VBA and XML HTTP
Lecture 2 :
XmlHttpRequest â€“ Http requests in Excel VBA
Lecture 3 :
HTML table to Excel worksheet with XMLHTTP and VBA (I)
Lecture 4 :
HTML table to Excel worksheet with XMLHTTP and VBA (II)
Lecture 5 :
HTML table to Excel worksheet with XMLHTTP and VBA (III)
Lecture 6 :
Parsing HTML Table within an iframe to Excel in VBA
Lecture 7 :
Parse HTML in Excel VBA
Lecture 8 :
Xpath
Lecture 9 :
Basic Authentication in VBA
Lecture 10 :
Save Internet File using the VBA XMLHTTP Object
Section 13 : VBA ServerXMLHTTP 6 Lectures 00:18:06
Lecture 1 :
Using ServerXMLHTTP to GET XML results from a Web page
Lecture 2 :
Using ServerXMLHTTP to POST XML to a Web page
Lecture 3 :
Providing authentication info via MSXML2.ServerXMLHTTP
Lecture 4 :
Scrape html by element id, name after response using MSXML2.ServerXMLHTTP
Lecture 5 :
MSXML2.ServerXMLHTTP Response text as HTML Page
Lecture 6 :
Extracting data from the response of a serverXMLHTTP request
Section 14 : Using VBA Query Tables to load web content into Excel spreadsheets 7 Lectures 00:22:08
Lecture 1 :
Extracting a table of data from a website using a VBA query
Lecture 2 :
Pull data into Microsoft Excel with Web queries
Lecture 3 :
HTML content import in Excel with Query Tables
Lecture 4 :
Import HTML table with Web Query
Lecture 5 :
Import database table data into Excel with Query Tables in VBA and ADODB
Lecture 6 :
Send an HTTP POST request to a server from Excel using VBA with QueryTables
Lecture 7 :
Using QueryTables to authenticate/login to a web site (with POST Method)
Section 15 : Web Services and HTTP requests with VBA 6 Lectures 00:28:32
Lecture 1 :
How to invoke a SOAP Web Service from custom VBA Code
Lecture 2 :
SOAP Request in VBA
Lecture 3 :
SOAP - Using VBA to send XML to WSDL
Lecture 4 :
Create a SOAP Request/Response using XML from VBA
Lecture 5 :
How to Get Http Request In Excel with VBA
Lecture 6 :
Build a VBA App from calling an API
Section 16 : Using VBA WinHTTP to access the web 5 Lectures 00:16:44
Lecture 1 :
How to make REST call with VBA in Excel and WinHttp
Lecture 2 :
Send HTTP requests with VBA from Excel with WinHttp
Lecture 3 :
Get HTTP request in Excel with VBA WinHttp
Lecture 4 :
VBA Web Requests with WinHttp
Lecture 5 :
Get JSON Response with WinHTTP in VBA
Section 17 : Connect to remote databases with VBA 10 Lectures 01:00:05
Lecture 1 :
ADO in Excel VBA â€“ Connecting to database using SQL
Lecture 2 :
SQL Server - Install and configure SQL Database for testing
Lecture 3 :
SQL Server - SQL Connection using Excel Macros
Lecture 4 :
SQL Server - Connect to a remote SQL Server DB With VBA & ADODB
Lecture 5 :
SQL Server - Using Excel VBA to Query a SQL Server Database
Lecture 6 :
mySQL - Install and configure a mySQL Database for testing
Lecture 7 :
mySQL - Excel VBA connecting to remote mySQL Database
Lecture 8 :
mySQL - How to connect to mMySQL database in Excel with VBA and get the data
Lecture 9 :
mySQL - VBA interaction between MS Excel and mySQL
Lecture 10 :
mySQL - Remote connection to mySQL and insert data to database table
Section 18 : Send HTML Email from Excel using VBA 2 Lectures 00:08:37
Lecture 1 :
Send HTML Email from Excel using VBA
Lecture 2 :
Outlook Email with Excel VBA body as HTML Table format
Section 19 : VBA and RSS feeds 4 Lectures 00:20:28
Lecture 1 :
Show RSS Feeds in Excel with VBA
Lecture 2 :
Reading RSS feeds from VBA
Lecture 3 :
Create an rss feed with Excel
Lecture 4 :
Get Data from Bing Search and display the results using DOM
Section 20 : Excel VBA and Google Maps 5 Lectures 00:23:01
Lecture 1 :
Excel VBA and Google Maps
Lecture 2 :
VBA and Google Maps
Lecture 3 :
Display Google Maps on a UserForm in VBA
Lecture 4 :
Using Excel VBA to calculthe distance between two addresses or coordinates
Lecture 5 :
Address Geocoding with VBA
Section 21 : VBA, HTML and Regex 10 Lectures 00:46:33
Lecture 1 :
RegEx and limitations for HTML parsing
Lecture 2 :
How to Remove HTML tags from a String in VBA
Lecture 3 :
Scraping HTML by Regular expression
Lecture 4 :
Regex VBA Match: Regex get only numeric values from string
Lecture 5 :
Get links from HTML using VBA RegEx
Lecture 6 :
Get Web content from HTML tables using VBA RegEx
Lecture 7 :
Parsing strings from HTML code between attributes of elements in VBA
Lecture 8 :
How to properly parse XML in VBA using RegEx
Lecture 9 :
Get element using VBA RegEx
Lecture 10 :
How to remove all HTML tags from strings in Excel with VBA RegEx
Section 22 : VBA Webbrowser 11 Lectures 00:39:22
Lecture 1 :
Excel VBA create an embedded WebBrowser
Lecture 2 :
Integrating WebBrowser Control with VBA in a Form
Lecture 3 :
Build a VBA form embedding a Webbrowser object
Lecture 4 :
Build a search functionaliy for web content on a Webbrowser component
Lecture 5 :
Add a Webbrowser component to a Userform dynamically
Lecture 6 :
Opening a popup window with a VBA App
Lecture 7 :
Build Browser functions for a Webbrowser component
Lecture 8 :
Advanced Browser Functions
Lecture 9 :
Changing Font size on a Webbrowser in a VBA Form
Lecture 10 :
Disabling functions appropriately (Back/Forward) in a Webbrowser component
Lecture 11 :
Disabling functions appropriately (page setup/print preview/print setup)
Section 23 : VBA Load and read CSS and Javascript 7 Lectures 00:52:25
Lecture 1 :
Retrieve CSS styles with VBA (I)
Lecture 2 :
Retrieve CSS styles with VBA (II)
Lecture 3 :
Export Excel range formats to HTML or CSS file or HTML file
Lecture 4 :
Load external Javascript in WebBrowser Object with VBA
Lecture 5 :
Creating HTML using a Builder Pattern from Excel Table in VBA
Lecture 6 :
Using Excel Cell StyleElements to Style HTML Document
Lecture 7 :
Describing the CSS class to define dynamically CSS for an HTML document
Section 24 : File Download from / upload to Server using VBA 6 Lectures 00:18:24
Lecture 1 :
VBA Download / Upload File using VBA FTP
Lecture 2 :
Asynchronous File Downloads using VBA
Lecture 3 :
Alternative methods to Upload file via FTP from Excel VBA
Lecture 4 :
FTP a text file to a server using VBA in Excel
Lecture 5 :
Use VBA to export CSV from webpage
Lecture 6 :
SSH Client using VBA
Section 25 : Miscellaneus 9 Lectures 00:36:20
Lecture 1 :
The Application.Wait function
Lecture 2 :
Build an SEO report with VBA
Lecture 3 :
Pull Web Page content Into Excel Worksheet in form of list
Lecture 4 :
Excel to HTML with indent space
Lecture 5 :
Understanding and Using Windows API Calls for Excel Programming
Lecture 6 :
Search with regular expressions
Lecture 7 :
How to encrypt and decrypt authentication data in VBA (Base64)
Lecture 8 :
The VBA WEB Class on Github
Lecture 9 :
How to get Support from Internet Communities

How do i access the course after purchase?
It's simple. When you sign up, you'll immediately have unlimited viewing of thousands of expert courses, paths to guide your learning, tools to measure your skills and hands-on resources like exercise files. There’s no limit on what you can learn and you can cancel at any time.
Are these video based online self-learning courses?
Yes. All of the courses comes with online video based lectures created by certified instructors. Instructors have crafted these courses with a blend of high quality interactive videos, lectures, quizzes & real world projects to give you an indepth knowledge about the topic.
Can i play & pause the course as per my convenience?
Yes absolutely & thats one of the advantage of self-paced courses. You can anytime pause or resume the course & come back & forth from one lecture to another lecture, play the videos mulitple times & so on.
How do i contact the instructor for any doubts or questions?
Most of these courses have general questions & answers already covered within the course lectures. However, if you need any further help from the instructor, you can use the inbuilt Chat with Instructor option to send a message to an instructor & they will reply you within 24 hours. You can ask as many questions as you want.
Do i need a pc to access the course or can i do it on mobile & tablet as well?
Brilliant question? Isn't it? You can access the courses on any device like PC, Mobile, Tablet & even on a smart tv. For mobile & a tablet you can download the Learnfly android or an iOS app. If mobile app is not available in your country, you can access the course directly by visting our website, its fully mobile friendly.
Do i get any certificate for the courses?
Yes. Once you complete any course on our platform along with provided assessments by the instructor, you will be eligble to get certificate of course completion.
For how long can i access my course on the platform?
You require an active subscription to access courses on our platform. If your subscription is active, you can access any course on our platform with no restrictions.
Is there any free trial?
Currently, we do not offer any free trial.
Can i cancel anytime?
Yes, you can cancel your subscription at any time. Your subscription will auto-renew until you cancel, but why would you want to?

Daniele Protti,

100125 Course Views

2 Courses

I have 20+ years experience in IT project management, software development and software architecture. I have led several teams of software developers as project manager, quality manager and as team leader in different companies and different countries in sectors e.g. telecommunication, research & development, travel business, Internet marketing and Internet business, consultancy and services. I have been responsible to collect the requirements of projects, applications and processes, design the databases and specify the final objects. During my career I have accumulated a lot of experiences dealing with processes of any kind and the need to define the tools to manage them and collect the information. Most of my tools have been written in VB, VBA, C++, C#, Java, PHP, Javascript using databases e.g. Informix, Postgres, Oracle and MySql. I have organized all my experiences in courses which are not only a list of functions and methods but contain also my practical experiences and considerations about how to cope with the different situations and which solutions to suggest.