How to Use Internet Explorer Application with VBA in 3 Minutes (Excel)

Written by Kasper Langmann

The Internet Explorer application and Visual Basic for Applications (VBA) are powerful tools that, when combined, can automate and streamline your tasks in Excel. This guide will walk you through the process of using these tools together, even if you only have a few minutes to spare.

Understanding Internet Explorer and VBA

Before we delve into the process, it’s important to understand what Internet Explorer and VBA are and what they can do. Internet Explorer is a web browser developed by Microsoft. It allows users to view web pages on the internet. On the other hand, VBA is a programming language developed by Microsoft that is used to automate tasks in Microsoft Office applications, including Excel.

By using Internet Explorer with VBA in Excel, you can automate tasks such as navigating to a website, filling out forms, and extracting data from web pages. This can save you a significant amount of time and effort, especially if you frequently perform these tasks.

Internet Explorer

Internet Explorer, often abbreviated as IE, has been around since 1995. Although it has been replaced by Microsoft Edge as the default browser in Windows 10, it is still widely used, especially in corporate environments. It supports a variety of web standards and has many features that can be useful for automation, such as the ability to interact with HTML elements on a web page.

One of the key features of Internet Explorer that makes it suitable for automation is its COM (Component Object Model) interface. This allows other applications, such as Excel, to interact with it programmatically. For example, you can use VBA to instruct Internet Explorer to navigate to a specific URL, click a button on a web page, or extract data from a web page.

VBA in Excel

VBA, or Visual Basic for Applications, is a programming language that is built into Microsoft Office applications. It allows you to automate tasks in these applications by writing macros, which are sequences of commands that perform a specific task. In Excel, you can use VBA to automate tasks such as formatting cells, creating charts, and manipulating data.

One of the key features of VBA in Excel is its ability to interact with other applications, including Internet Explorer. This is done through the use of objects, which are instances of classes that represent things like workbooks, worksheets, cells, and, in the case of Internet Explorer, web pages and their elements.

Setting Up Your Environment

Before you can start using Internet Explorer with VBA in Excel, you need to set up your environment. This involves enabling the Developer tab in Excel, which gives you access to the VBA editor, and setting a reference to the Internet Explorer object library, which allows you to interact with Internet Explorer from VBA.

To enable the Developer tab in Excel, right-click anywhere on the ribbon, select Customize the Ribbon, check the Developer box in the right-hand list, and click OK. The Developer tab should now be visible on the ribbon.

Accessing the VBA Editor

To access the VBA editor, click on the Developer tab and then click on the Visual Basic button. This will open the VBA editor, where you can write and run your VBA code.

Alternatively, you can use the keyboard shortcut Alt + F11 to open the VBA editor. This can be a quicker and more convenient way to access the VBA editor, especially if you use it frequently.

Setting a Reference to the Internet Explorer Object Library

To set a reference to the Internet Explorer object library, go to the VBA editor, click on Tools, then References, and check the box next to Microsoft Internet Controls. This will allow you to use the InternetExplorer object in your VBA code, which represents an instance of Internet Explorer.

Once you have set a reference to the Internet Explorer object library, you can start using Internet Explorer with VBA in Excel. The next section will show you how to do this.

Using Internet Explorer with VBA in Excel

Now that you have set up your environment, you can start using Internet Explorer with VBA in Excel. The following steps will guide you through the process of creating a simple VBA macro that uses Internet Explorer to navigate to a website and extract data from a web page.

Navigating to a Website

The first step in using Internet Explorer with VBA in Excel is to navigate to a website. This can be done using the Navigate method of the InternetExplorer object.

The Navigate method takes a URL as a parameter and instructs Internet Explorer to navigate to that URL. The following code shows how to use the Navigate method to navigate to Google’s homepage:

Dim ie As InternetExplorer
Set ie = New InternetExplorer
ie.Visible = True
ie.Navigate "https://www.google.com"

Extracting Data from a Web Page

Once you have navigated to a website, you can start extracting data from the web page. This can be done using the Document property of the InternetExplorer object, which represents the HTML document of the web page.

The Document property allows you to access the HTML elements of the web page, such as links, images, and text. You can use the GetElementById, GetElementsByName, and GetElementsByTagName methods of the Document object to access these elements.

The following code shows how to use the Document property and the GetElementById method to extract the text of the first headline on Google’s homepage:

Dim headline As String
headline = ie.Document.GetElementById("hplogo").innerText

This code assigns the text of the element with the ID “hplogo” to the variable headline. You can then use this variable in your Excel workbook, for example, to display the headline in a cell.

Conclusion

Using Internet Explorer with VBA in Excel can be a powerful way to automate tasks and extract data from the web. This guide has shown you how to set up your environment, navigate to a website, and extract data from a web page. With these skills, you can start automating your tasks in Excel and save yourself a significant amount of time and effort.