Complete Web Automation with Excel VBA

Explore web automation in Excel VBA with Selenium Basic to open a browser and navigate to Google.com, using two methods: generic WebDriver or specific Chrome/Edge drivers.

Explore using get elements by tag in excel vba to locate h1 headers, first paragraphs, and labels on a test site with a chrome bot.

Automate web tasks with Excel VBA by loading a 2018 archived snapshot of the slow Ajax Awful Valentine site from a local HTML file, bypassing online access.

Learn to locate page elements by class name in web automation, test for uniqueness, and extract text with Excel VBA using the category-title class as a practical example.

Access the exercise question by downloading the file attached to this lecture or from the master course files download folder under exercises.

Learn to fill a web form using selenium web driver strategies, targeting input fields by id, sending keys, and clicking the purchase button on a demo site.

Learn to locate and click links by exact link text using a bot, exemplified by the 'view cart' link. Then explore partial link text in the next lecture.

Learn to use the attribute property to inspect element attributes, retrieve values with a dot attribute, and compare placeholders in an if statement within a VBA web automation macro.

Explore css selectors, including class selectors with a dot, id selectors with a hash, and attribute brackets for elements. Practice with the css selector tester page and the Mozilla guide.

Learn how to find and install free Chrome extensions from the Chrome Web Store, access their settings, and use tools like XPath helper for web automation.

Learn to use the CSS selector helper for Chrome to generate robust selectors by combining id, name, and placeholder attributes, then copy the selector for use in Excel Selenium.

Explore how the Crow Path CSS selector tool highlights and tests selectors, including selecting the second H2 tag and its inner span for reliable web automation in Excel VBA.

Discover selectors hub, the free upgrade to crow path, which generates css selectors or xpath quickly with auto suggestions and element counts, and detects iframes and shadow dom.

Explore updating web page attributes with JavaScript in Excel VBA automation, using setAttribute and removeAttribute via bot.execute_script and arguments to target elements like Google's search box.

Demonstrate a custom change attribute function in a VBA web automation bot to modify attributes such as href, style, and src by passing the bot, element, and new values.

Explore the XPath helper tool from the Google Chrome store to test XPath syntax quickly, using relative paths, double slashes, and queries for body, input, and span.

Learn Crow Path and XPath to identify elements with relative and absolute references, using ID equals name, and copy XPath into Excel for web automation in VBA.

Explore why XPath outshines CSS selectors by using the contains() function to match attribute values such as href and name, including partial matches on anchors and inputs.

Explore XPath logic with and and or to search multiple conditions. Use name equal to CC or id equal to year, then narrow with type text and placeholder first last.

discover how the XPath starts-with function selects elements whose name attribute begins with a given string, illustrated by matching inputs whose name starts with n.

Learn to dynamically select an index in an XPath query by wrapping the query in parentheses and using brackets or the last function, even with Ajax-updated fields.

Download the exercise question attached to this lecture, or from the master course files download folder under exercises, to begin practicing complete web automation with Excel VBA.

Automate data entry from an Excel VBA loop that reads product names and prices from sheet and uses XPath and sendkeys to fill a website form, click add item, save.

Discover how to handle a group of web elements in Selenium with Excel VBA by declaring a web elements collection, using set to assign, and looping through them.

Create a links list by extracting the href attribute from each anchor element to capture the page URLs and view the results in the View Locals window.

Obtain counts for web elements or lists in Excel VBA by using the dot count on anchor elements and on links text, with verification in the locals window.

Call links.distinct to remove duplicates from the list of HTML URLs, reducing 309 items to 251 after checking the locals window.

Sort the list with a single command using a built-in feature, turning an unordered sequence into alphabetically ordered results. Press F8 to see the sorted outcome.

Export the deduplicated links list directly into Excel using Dot to Excel, starting at range A2. Optionally add a title to label the first entry.

Loop through a list of web elements in Excel VBA using for each and for next, iterating over the links collection and navigating with bot dot get.

Locate all links within a specific div by using find element by id to pick the div, then use find elements to collect anchor tags and retrieve their hrefs.

Explore the by object in selenium to locate elements using by dot methods like id, css, and link text, and send keys in a practical example.

Learn how to test whether an element exists on a page using the by object and the is element present method in VBA, with an id CC example.

Learn two ways to detect if a web element exists in Excel VBA: use a web element object and test for nothing, or a one-liner find by id.

Learn how to access and compare web element properties in VBA using text, value, and css value; inspect width, background color, padding, and tag name to form your own statements.

Extract numbers from a web element's text by locating the element by id, such as copyright, and printing the text as a number, with decimal character configurable as a string.

Explore how to determine a web element’s state in VBA by examining is displayed, is enabled, is present, and is selected, demonstrated with a checkbox on a W3 Schools page.

Master scrolling to a page element with the scroll into view method in Excel VBA, using XPath, align top, and element placement on screen.

Explore how to use bot.set_preference to define the download default directory, enable download directory upgrade, and disable the download prompt, then start the webdriver-driven bot to auto-download files.

Compare assertion and verification in web automation with Excel VBA: assert enforces results with an error and halts the macro, while verify returns a status string with detailed failure notes.

Explore the contains method in Excel VBA tests, learning to assert contains, verify substrings like 'I love Excel', and debug outcomes when the expected text is present or absent.

Learn to customize error messages for assert contains in Excel VBA by supplying a fail message as a string with the third parameter.

Explore how assert and verify operators handle booleans in Excel VBA, including assert.fail, assert.true, and assert.false, to control test flow and trigger debug pauses.

Explore essential mouse interactions for web automation, including single and double clicks, right clicks with context menus, and click-and-hold with release, using Selenium bot and actions for reliable waits.

Learn to use the send keys method and the Keys object to simulate typing and keyboard shortcuts with modifiers such as control, alt, or shift, including Ctrl Z and tab.

Log in to the PHP Travels demo site using only the send keys command, entering the username, tabbing to the password, and pressing enter to sign in.

Learn how to automate HTML5 drag-and-drop in a web page from Excel VBA using Selenium Basic, comparing methods and finally applying a JavaScript-based solution to swap elements a and b.

Demonstrate a drag and drop html5 interaction, dragging the element with id two into the bin so it disappears, and validate its absence with assert true and then assert false.

Learn to automate text dragging on a demo website using JavaScript, selecting the drop target, and validating results with an assertion.

Learn how to implement drag and drop for file uploads in a web page, using an image holder and drop file HTML5, with practical examples testing a dog image.

Learn how to manage timeouts and waits in selenium by using the default implicit wait of 3 seconds, and adjust page load, script, and server timeouts to suit tests.

Practice automating a purchase form in Excel VBA by filling four text boxes, waiting for the buy button to appear, clicking by ID, and waiting for the confirmation before closing.

Master strategies to wait for purchase confirmation in web automation with excel vba, using id based checks, wait text with regex, and spinner disappearance to confirm 'purchase complete' before proceeding.

Learn to wait for CSS value or wait for not CSS value to detect changes, using an element's width or color as triggers, and see an animated_div hover example.

Learn to wait for a web control to become enabled or disabled in Excel VBA automation, using by ID and switching to the correct frame with a 20-second timeout.

Learn the wait for selection method for checkboxes, waiting for a boolean true (checked) or false (unchecked) using an element id, with a 20-second timeout and optional reverse waiting.

Learn to automate web alerts in Excel VBA by handling JavaScript confirm, prompt, and alert boxes with switch to alert, accept or dismiss, and send keys for text input.

Troubleshoot set credentials and authorization dialog popups in web automation, handling not authorized errors, timeouts, and alert behavior, including basic auth with username and password in the URL.

Learn how the is dialog present function detects popups in Excel VBA web automation, returning true or false to conditionally switch to and dismiss alerts with a bot driver.

Explore essential web navigation in excel vba by using go back, go forward, and refresh. Learn to manage tabs with close versus quit while tracking current and base URLs.

Learn to restore a web session in Excel VBA by saving and reloading cookies with a session cookies object, adding cookies and refreshing to keep login and cart.

Save and load web session cookies in an Excel worksheet using VBA, with headers, clearing old cookies, and preserving login state across sessions.

Learn to run selenium with Google Chrome Portable in Excel VBA by setting the browser binary path, enabling testing without installation from usb or a portable folder.

Learn to pull a web page's HTML and text using Excel VBA: capture the page source or extract text with XPath wildcard, and place results into a worksheet cell.

Access and complete the coding exercise by downloading the question attached to this lecture or from the master course files download folder under exercises.

Extracts a targeted paragraph from a web page using XPath in Excel VBA, storing it in sheet one cells A2 and C2 while demonstrating XPath handling.

Keep a permanent selenium chrome driver open across macro runs to reuse the browser. Fetch URLs from worksheet cells like B2 and B5 and quit the driver when finished.

Demonstrate how to locate frames, including nested ones, with the get frame number for element function and switch to frames for reliable web automation in Excel VBA.

Learn a bonus switch to frames function in excel vba that switches to default content, parses a comma separated string into frame indices, and loops to validate elements by xpath.

Capture a browser screenshot with the Selenium image, then save it to a specified path as PNG or JPEG, with optional disposal to manage system memory.

Learn to capture a web element as an image with Selenium WebDriver by locating a div by ID, scrolling into view, taking a screenshot, and saving it.

Learn to obtain an image's height and width in VBA, resize it with img.resize, and copy the result to the clipboard, exploring proportional scaling with multipliers like 0.5 and 0.75.

Capture two screenshots from Google UK and Google France as image objects, then compare image a to image b to generate a diff image and report the diff count.

Load an image into a user form by adding an image control and using image.getPicture to set its picture from the worksheet image, then show the form in clip mode.