Tech & IT
Business
Coding & Developer
Finance & Accounting
Academics
Office Applications
Art & Design
Marketing
Health & Wellness
Sounds & Music
Lifestyle
Photography
Free
7 days left at this price!
This plan includes
- Limited free courses access
- Play & Pause Course Videos
- Video Recorded Lectures
- Learn on Mobile/PC/Tablet
- Quizzes and Real Projects
- Lifetime Course Certificate
- Email & Chat Support
Get Unlimited Learning Access
$29
7 days left at this price!
30-Day Money-Back Guarantee
This plan includes
- Access to 11,000+ Courses
- Ads free experience Courses
- Play & Pause Course Videos
HD Video Recorded Lectures- Learn on Mobile/PC/Tablet
- Quizzes and Real Projects
- Lifetime Course Certificate
- Instructor Direct Support
- Email & Chat Support
- Cancel Anytime
$29
$29
What you'll learn?
- 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
Course Overview
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
...
Pre-requisites
- Basic knowledge of Microsoft Excel
- Basic knowledge of Excel VBA
- Basic knowledge of Web and HTML
Target Audience
- 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
Curriculum 223 Lectures 19:38:23
-
Section 1 : Introduction
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
Our learners work at
Frequently Asked Questions
-
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?
Instructor
99952 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.
View More...
