Complete Web Automation with Excel VBA

Complete Web Automation with Excel VBA
Complete Web Automation with Excel VBA
English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 8.5 Hours | 5.05 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).

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 & Installing Selenium
3 Installation Troubleshooting (skip if you don’t need this!)

Selenium Basics
4 Opening a Web Page
5 Looking At Attributes – More Than Just TEXT!
6 Strategies – PartialLinkText
7 Strategies – CSS Selectors Intro
8 How to Install a Chrome Extension
9 CSS Selector Helper for Chrome
10 CSS Selector Tool – ChroPath and Technical Demonstration
11 BONUS – Set Value versus SendKeys To TextBox
12 Set Attributes With JavaScript Easily!
13 Web Elements 101 – Type in Textbox and Click Search Button (ID,Name Selectors)
14 HTML Overview – Popular Tags and Attributes
15 Strategies – Tag
16 Awful-Valentine Site Expired, But We Downloaded It!
17 Strategies – Class
18 Exercise 02a – Question
19 Exercise 02a – Answer
20 Strategies – LinkText

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

Working With Multiple Elements At Once
35 Working With a Group Of Elements (FindElements)
36 Looping Through a List of Attributes for a Set of Elements
37 Exercise 04a – Question
38 Exercise 04a – Answer
39 Finding Elements within an Element
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!

Element Properties, Methods and Useful Information
48 Find the Active Element
49 Scrolling Element Into View
50 Regular Expression Patterns!
51 Return Text Based on Regular Expression (RegEx)
52 Automatically Download File to Custom Folders with SetPreference()
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

Built In Comparison Operators
61 Assert vs Verify
62 Equals Comparison Operator
63 Not Equals
64 Contains
65 Custom Error Messages
66 Matches & NotMatches
67 Fail, False and True
68 Selenium VBA Code Snippets – Chapter 4-6

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

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

Browser Window Control, Navigation and Focus
93 Alerts – Using Multi Options (OK and Cancel, OK Only & Text) with An Alert
94 Exercise 09a – Answer
95 Keep a Permanent WebDriver that Doesn’t Close On Its Own
96 Frames and Frame Nagivation
97 Window, Tab Manipulation by Title, Index, Previous and Next
98 Accessing Window By Name, Handle
99 Close All Tabs Except Main Tab
100 Several Ways To Open a New Tab In Current Session
101 Window Sizing and Position
102 Selenium VBA Code Snippets – Chapter 9
103 SetCredentials Troubleshooting Tips and Authorization Dialog Popups
104 Custom Function Testing Whether Dialog Popup Is Present
105 Basic Navigation – Back, Forward, Reload, Close Driver, Close Single page, Url
106 Setting A Custom Profile For WebDriver Session
107 Add Special Arguments to Customize Browser Options – Incognito or Hidden Window
108 AddArguments Link and Examples
109 Grab Html Page Source and Text From Parent Elements
110 Exercise 09a – Question

Image and Clipboard Manipulation
111 Save a Screenshot of Your Browser, Dispose Image in System Memory
112 Save Web Element as Image
113 Save Entire Desktop Screen as Image
114 Copy Image Directly to Excel OR To Clipboard
115 Image Dimensions and Resizing
116 Compare Two Images with CompareTo and DiffCount
117 Put Web Image Into Worksheet ActiveX Image Control
118 Load Image Into Userform Image

Working With Tables
119 Converting a Web Table to TableElement Object and Inserting into Worksheet
120 Manipulating TableElement Data as Array onto Worksheet
121 Loop Through Elements in Specific Column Only
122 Loop Through Multiple Specific Columns Using OR Operator
123 Put Specific Columns into List Object, To Excel Easily
124 Exercise 11a – Question
125 Exercise 11a – Answer

Selecting Options from Dropdowns and Listboxes
126 Declare SelectElement, Read and Write Dropdown Control
127 Exercise 12a – Answer
128 Select Using the Index Number
129 Select Using Internal Option Value
130 All About The Options Collection
131 Controlling Multiple Select ListBoxes and Gathering Selected Options
132 Using The Mouse To Select Multiple Options
133 Deselecting All Options or Specific Options
134 Selenium VBA Code Snippets – Chapter 10-12
135 Exercise 12a – Question

BONUS – Extra Troubleshooting Tips
136 Error Loading Extension Help
137 Error Loading Extension Help – Source Code
138 BONUS LECTURE