Example below tries to give web automation using IE (internet explorer). VBA macro drives internet explorer using its DOM properties and methods. The macro allows to interact with web page controls (text fields and buttons). The example opens web site www.excely.com and fill a search form to find 'excel vba' text.
A very common problem people encounter when working with IE in VBA is VBA attempting to run code before Internet Explorer has fully loaded. By using this code, you tell VBA to repeat a loop until IE is ready (IE.ReadyState – 4). Set in the options – Microsoft Internet Controls for the InternetExplorer object and Microsoft HTML Object Library for HTMLDocument object. Of course You can go with the late binding. Setting everything as Object variable is good, but it is easier to begin with early binding. The Zoom to Selection / Fit to Selection feature can be controlled with VBA, however it does require a range to be selected. The code below selects Cells A1:J15, the and zooms the window to the size of those cells. 'Zoom to selection Range ('A1:J15').Select ActiveWindow.Zoom = True. Open the VBA Editor, and click Tools References. Visual Basic Editor – Tools References. Make sure these 4 References are active by default: Visual Basic for Applications. Microsoft Excel xx.x Object Library. Microsoft Office xx.x Object Library. VBA default reference libraries. I have been working VBA to scrape website HTML code using the Microsoft HTML Object Library and the Microsoft Internet Controls Library. Now that support for Internet Explorer is being phased out, I am trying to switch my code over to scrape Google Chrome using the Selenium Type Library which is an open source download.
First we open manually web site and found html-code of search form:
To automate searching we need:
- Create Internet Explorer object
- Load web site www.excely.com
- Find 2 input tags:
- Text field
- Button
- Set searching text and click button
- Wait while IE loading
- Clean up objects
IE Autiomation source code:
This post is to give small tip related to web scraping. If you are new to web scraping please see my earlier post which explain everything from beginning. Below is the link to that post.
Web Scraping - BasicsExcel Vba Microsoft Internet Controls
In this post I will explain you what are the best VBA references you should add when you develop a Visual Basic Application to scrape data from websites. Adding these references are not mandatory. But it will make your life easier. So here are the list of useful references.
- Visula Basic for Applications
- Microsoft Excel 15.0 Object Library
- OLE Automation
- Microsoft Office 15.0 Object Library
- Microsoft Forms 2.0 Object Library
- ietag 1.0 Type Library
- iextag 1.0 Type Library
- Microsoft HTML Object Library
- Microsoft Internet Controls
Excel Vba Microsoft Internet Controls Diagram
- ietag 1.0 Type Library
- iextag 1.0 Type Library
- Microsoft HTML Object Library
- Microsoft Internet Controls
Once you add references, your references window should look like this.