Flash Sale: Get Upto 75% off on all courses. Browse the courses below to see available discounts and offers. Valid till:

VBA WEB - Connect MS Office to the Web

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

Instructed by Daniele Protti

Access all courses with Premium Subscription

  • Monthly
  • Yearly

Monthly

$ 29/mo
Billed Monthly
  • All Courses Access
  • New Courses Instant Access
  • Learning paths Access
  • Course completion certificates
  • Skills Assessment
  • Instructor Support
  • Exercise files & Quizzes
  • Resume & Play
  • Mobile and TV apps
  • Offline viewing
  • Cancel Anytime
Subscribe Now

Yearly

$ 299/yr
Billed Anually
  • One Year Unlimited Access
  • New Courses Instant Access
  • Learning paths Access
  • Course completion certificates
  • Skills Assessment
  • Instructor Support
  • Exercise files & Quizzes
  • Resume & Play
  • Mobile and TV apps
  • Offline viewing
  • Cancel Anytime
Subscribe Now
  • 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
View More...

Section 1 : Introduction

  • Lecture 1 :
  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • Lecture 1 :
  • Show RSS Feeds in Excel with VBA
  • Lecture 2 :
  • Reading RSS feeds from VBA