Complete Web Automation with Excel VBA

Complete Web Automation with Excel VBA

English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 150 lectures (10h 1m) | 3.97 GB

Fill Out Web Forms Dynamically, Navigate Web Pages Intuitively, And Extract & Manipulate Data To Increase Efficiency!

Using a website has become part of everyday life in both work and play. Many of us spend hours a day scouring the web or filling out tedious forms online for work!

But what if we can automate these processes that steal our precious time away from us? Introducing “Complete Web Automation with Excel VBA” course. In this jam-packed, progressive course, I share over eight hours of secrets to using the popular website testing program called Selenium, ( previously only available using complicated web languages such as JavaScript or PHP). This free tool is now available to all Microsoft Excel users, but there’s very little documentation for how to use it unless you are a web developer.

Throughout the course, I take you through all the undocumented processes, enabling you to locate and interact with any element on a webpage using a variety of strategies, from basic text & attribute searches to Advanced strategies such as XPath, CSS Selectors, and Regular Expression (RegEx) functionalities.

You’ll learn how to fill out web forms dynamically, navigate web pages intuitively, and extract and manipulate data to increase efficiency.

Once you understand how to find an element (or group of elements), you’ll learn to evaluate whether elements are hidden, enabled or disabled, visible, selected, checked or unchecked, and then we’ll show you how to completely control mouse and keyboard commands and positioning. Then we’ll learn how and when to trigger macros with precision using “Timeouts and Waits”!

In addition to basic navigation, I’ll show you how to Open, Access and Toggle multiple tab Windows, how to resize, re-position and auto-scroll your browser window as well as use login techniques with set credentials. You’ll be able to deal with pop-ups, alerts, notifications and Frames like an expert. I’ll even show you the secrets of controlling the clipboard, saving screenshots to your Excel file or even taking all or parts of online tables into your spreadsheets automatically. Finally, I’ll take you through drop down menus and working with list boxes to complete your WebDriver mastery.

The course comes loaded with several examples in each section, including several engaging and challenging exercises to help you solidify what you’ve learned (with solution videos immediately following).

You’ll receive 100% of the source code, downloadable materials and course workbooks instantly, yours to keep FOREVER. In addition, you’ll receive LIFETIME ACCESS to the course videos, including updates, BONUS videos and lectures as they come out and any future source code and workbooks – Yours to keep – FREE.

All students will receive LIFETIME ACCESS to ask me any questions about the materials in the FORUM section – as well as chat with other students and help each other. As always, this is backed by a 30 day 100% No questions asked refund as well.

So Stop wasting your time with tedious online tasks when you can automate your online workflow Today!

What you’ll learn

  • Locate elements using Basic Strategies (ID, Name, Tag, Class)
  • Search for Links using Link Text or Partial Link Text!
  • Master XPath Location strategies and functions for hard to pinpoint elements
  • Deal with Popups, Alerts, Notifications and Frames like a boss!
  • Utilize Attributes to unearth embedded content
  • Pinpoint elements using Relative or Absolute Xpath
  • Learn the secret benefits of CSS Selectors
  • View and Parse inner html document information
  • Use free extensions and tools to locate best Xpath or CSS Selectors
  • Capture Groups of Elements into Arrays, Lists or Straight to Excel!
  • Sort and Remove Duplicates from a List Object
  • Locate all sub elements of a div, table, or larger parent element
  • Determine if element Exists, is Enabled, Hidden, Selected, Checked or Unchecked
  • Evaluate hidden CSS Properties, such as ‘width’, ‘background color’, etc
  • Understand Regular Expression (RegEx) patterns and using pattern matching
  • Get element Position and Size and Scroll element into View when necessary
  • Create custom error messages to evaluate comparison (Equality, Contains, NotEquals, Matches, Fail…)
  • Discern the difference between Assert and Verify operators
  • Programmatically hover, click, click and hold, release, right click and double click with the Mouse
  • Send Keyboard combination presses as well as regular word character presses to the browser
  • Drag and Drop elements or text to another area or drag files for Upload programmatically
  • Control Timeouts and Wait default settings for PageLoad, Element searches and Server timeouts
  • Delay execution until controls are Visible, Enabled, Text is displayed or changed, Checkboxes/Optionbuttons are selected
  • Wait for CSS special values change or meet conditions
  • Log In using set credentials to site
  • Test for the presence of Dialog popups
  • Navigate with Basic Web Controls – Back, Forward, Reload, Close Driver, Close Single page, Urls
  • Save and Load specific user profile for WebDriver execution
  • Enter and Exit specific embedded Frame controls and their html content
  • Open, Access and toggle multiple tabs/windows and Select by Index, Title or Window Handle
  • Resize and Position Browser window to user specifications
  • Save Screenshot of Browser, Web Element(s), or entire Desktop to Excel or to a file
  • Copy Image directly to Excel OR to Clipboard
  • Change image dimensions and resize
  • Compare multiple image files, generating an image of differences
  • Cast image elements into ActiveX Image Control on worksheet or Userform!
  • Convert Web-Table into TableElement Object and manipulate as array or directly to Excel
  • Extract specific Web-Table column(s) instead of whole table!
  • Dynamically select one or more Dropdown or Listbox options programmatically or using mouse clicks
  • Deselect all options or explore ‘Options’ collection and properties
Table of Contents

Introduction – Getting Started & Setup
1 Introduction and Course Download Files
2 Getting FAST Help and Other Freebies with Excel Ninjas Group
3 Getting & Installing Selenium
4 Installation Troubleshooting (skip if you don’t need this!)

Selenium Basics
5 Opening a Web Page with Multiple Browsers (2 Methods)
6 Web Elements 101 – Type in Textbox and Click Search Button (ID,Name Selectors)
7 HTML Overview – Popular Tags and Attributes
8 Strategies – Tag
9 Awful-Valentine Site Expired, But We Downloaded It!
10 Strategies – Class
11 Exercise 02a – Question
12 Exercise 02a – Answer
13 Strategies – LinkText
14 Looking At Attributes – More Than Just TEXT!
15 Strategies – PartialLinkText
16 Strategies – CSS Selectors Intro
17 How to Install a Chrome Extension
18 CSS Selector Helper for Chrome
19 CSS Selector Tool – ChroPath and Technical Demonstration
20 BONUS – ChroPath Serious Upgrades – Now Called SelectorsHub
21 BONUS – Set Value versus SendKeys To TextBox
22 BONUS – Create or Change Any Attribute Using Javascript
23 BONUS – New Change Attribute Custom Function and Huge Demo
24 Set Attributes With JavaScript Easily!

Advanced Find With XPath
25 Introduction to XPath
26 XPath Helper Tool
27 ChroPath with XPath for Relative and Absolute Reference Types
28 XPath Function – Contains()
29 XPath Function – Text()
30 XPath Function – AND, OR
31 XPath Function – Starts-With()
32 XPath – Axes
33 XPath Function – Last()
34 Selecting a Specific Index for XPath Query!
35 Exercise 03a – Question
36 Exercise 03a – Answer
37 More XPath Resources
38 Selenium VBA Code Snippets – Chapter 1-3

Working With Multiple Elements At Once
39 Working With a Group Of Elements (FindElements)
40 Looping Through a Set of Elements, Declaration Tips
41 Grab the Text For Each Element Into A List
42 Grabbing All Attribute Values into a List
43 Add, Remove or Change an Element from a List
44 Get Count of List Items or Web Elements
45 Remove Duplicates Elements from List
46 Sorting Your List
47 Importing Items to Excel Directly!
48 Looping Through a List of Attributes for a Set of Elements
49 Exercise 04a – Question
50 Exercise 04a – Answer
51 Finding Elements within an Element

Element Properties, Methods and Useful Information
52 Find the Active Element
53 Understanding the ‘By’ Object
54 Does The Element Exist Using ‘By’ Object
55 Does Element Exist Using WebElement Object (2 Ways!)
56 Value, Tagname, Text & Css Properties
57 Specialty Text Variations
58 Discover Whether Element is Displayed, Enabled, Present, Selected
59 Pagination – Clicking Next Until You Reach the Last Page
60 Element Position and Size
61 Scrolling Element Into View
62 Regular Expression Patterns!
63 Return Text Based on Regular Expression (RegEx)
64 Automatically Download File to Custom Folders with SetPreference()
65 SetPreference Extra example!

Built In Comparison Operators
66 Assert vs Verify
67 Equals Comparison Operator
68 Not Equals
69 Contains
70 Custom Error Messages
71 Matches & NotMatches
72 Fail, False and True
73 Selenium VBA Code Snippets – Chapter 4-6

Mouse and Keyboard Actions
74 Click, Click and Hold, Release, Right Click & Double Click
75 SendKeys and Keys Modifiers
76 KeyDown, KeyUp
77 Exercise 07a – Question
78 Exercise 07a – Answer
79 DragAndDrop – Swapping Elements, Better with JavaScript
80 DragAndDrop – Element Disappears Once Dropped
81 DragAndDrop – Dragging Text Programmatically
82 DragAndDrop – Dragging File for Upload Programmatically
83 Hover MenuItem and Click On Submenu Item
84 Clear Out Text Entry Element

Timeouts and Waits
85 TimeOuts and Wait DEFAULTS
86 Basic Wait Command
87 Exercise 08a – Question
88 Exercise 08a – Answer
89 Finding Elements With a Custom TimeOut Duration
90 Wait Until Element is Displayed
91 Wait For Specific Text or a Specific Attribute to Appear!
92 Wait For CSS Special Value to Appear or Change
93 Wait for Text To Appear or Change
94 WaitNotElement and By.Any
95 WaitEnabled or Wait Until Element is Displayed
96 Wait For Selection With Checkboxes and OptionButtons
97 Selenium VBA Code Snippets – Chapter 7-8

Browser Window Control, Navigation and Focus
98 Alerts – Using Multi Options (OK and Cancel, OK Only & Text) with An Alert
99 SetCredentials Troubleshooting Tips and Authorization Dialog Popups
100 Custom Function Testing Whether Dialog Popup Is Present
101 Basic Navigation – Back, Forward, Reload, Close Driver, Close Single page, Url
102 Setting A Custom Profile For WebDriver Session
103 How To Add A Chrome Extension To ChromeDriver Sessions
104 Restoring a Web Session Using Cookies (Saved in Object Variable)
105 Restoring a Web Session Using Cookies (Saved in Worksheet)
106 Add Special Arguments to Customize Browser Options – Incognito or Hidden Window
107 Special Arguments List
108 Using a Portable Version of Chrome – GoogleChromePortable
109 AddArguments Link and Examples
110 Grab Html Page Source and Text From Parent Elements
111 Exercise 09a – Question
112 Exercise 09a – Answer
113 Keep a Permanent WebDriver that Doesn’t Close On Its Own
114 Frames and Frame Nagivation
115 BONUS – Frame Finder Custom Function
116 BONUS – Switch To Nested Frame Custom Function
117 Window, Tab Manipulation by Title, Index, Previous and Next
118 Accessing Window By Name, Handle
119 Close All Tabs Except Main Tab
120 Several Ways To Open a New Tab In Current Session
121 Window Sizing and Position
122 Selenium VBA Code Snippets – Chapter 9

Image and Clipboard Manipulation
123 Save a Screenshot of Your Browser, Dispose Image in System Memory
124 Save Web Element as Image
125 Save Entire Desktop Screen as Image
126 Copy Image Directly to Excel OR To Clipboard
127 Image Dimensions and Resizing
128 Compare Two Images with CompareTo and DiffCount
129 Put Web Image Into Worksheet ActiveX Image Control
130 Load Image Into Userform Image

Web Scraping & Working With Tables
131 Converting a Web Table to TableElement Object and Inserting into Worksheet
132 Manipulating TableElement Data as Array onto Worksheet
133 Loop Through Elements in Specific Column Only
134 Loop Through Multiple Specific Columns Using OR Operator
135 Put Specific Columns into List Object, To Excel Easily
136 Exercise 11a – Question
137 Exercise 11a – Answer

Selecting Options from Dropdowns and Listboxes
138 Declare SelectElement, Read and Write Dropdown Control
139 Select Using the Index Number
140 Select Using Internal Option Value
141 All About The Options Collection
142 Controlling Multiple Select ListBoxes and Gathering Selected Options
143 Using The Mouse To Select Multiple Options
144 Deselecting All Options or Specific Options
145 Selenium VBA Code Snippets – Chapter 10-12
146 Exercise 12a – Question
147 Exercise 12a – Answer

BONUS – Extra Troubleshooting Tips
148 Error Loading Extension Help
149 Error Loading Extension Help – Source Code