Microsoft training courses | Wise Owl - home page

Phone (01457) 858877 or email

WRITE A MACRO IN VBA TO FILL IN AN IE FORM (INTERNET EXPLORER)

You can use VBA to talk to Internet Explorer, providing that you understand the structure of the web page you're talking to. This blog shows how to do this!

This is part of our full Excel VBA tutorial - Wise Owl also run courses in Excel, as well as classroom training in VBA programming.

Posted by Andy Brown on 02 November 2012 | no comments

Using VBA to Link to Internet Explorer

I've shamelessly stolen my colleague Andrew's idea to create this blog, showing how to fill in an Internet Explorer (IE) form from within VBA:

Completing IE form

We'll create a macro in VBA to fill in the search form on the Wise Owl site.

A little knowledge is a dangerous thing, and this blog will prove this.  To link from VBA to Internet Explorer you really need to understand how HTML tags work.

Understanding the Target Page

The first thing to do is to know what the elements on the target website page are called.  To do this, load the website in IE and view its source HTML:

Viewing source HTML in IE

In Internet Explorer 9, this is how you view source HTML using the right mouse button menu.

 

If you press CTRL + F to find some known text (eg Search for this page), you should be able - eventually - to track down the part of the form of interest:

The HTML for the search button

The HTML for the search button on the Wise Owl website.

This can be difficult.  I've just tried doing it for Google and the BBC website, and couldn't locate the search button for either.  Perhaps the moral is to stick to simple (ish) websites!

From the above we can see that the search form with id search contains two controls which a user can interact with:

  1. A textbox called SearchBox; and
  2. A button called submit2.

Now that we know what we're trying to look to in the Document Object Model, we can start writing our VBA.

Referencing the Microsoft Internet Controls

The next thing to do is to make sure that we can get VBA to talk to Internet Explorer.  To do this, reference the object library called Microsoft Internet Controls (if you're not sure what references are, read this first).

First, in VBA create a reference:

Tools / References menu

Choose the option shown from the VBA code editor (this example is for Excel).

Select Microsoft Internet Controls (you may find it helpful to type an M in first, to go down to the object libraries starting with M):

The Microsoft Internet Controls object library

Tick the Microsoft Internet Controls object library reference

Select OK.  If you choose the menu option again, you'll see this reference has moved to near the top of the list:

References being used in VBA

The libraries referenced always include Excel. The one you just chose appears near the top of the list.

 

The Code to Link to IE

You can now write the code to link to Internet Explorer - here's a suggestion:

Sub UseInternetExplorer()

'Make sure you've set a reference to the

'Microsoft Internet Controls object library first

 

'create a variable to refer to an IE application, and

'start up a new copy of IE (you could use GetObject

'to access an existing copy of you already had one open)

Dim ieApp As New SHDocVw.InternetExplorer

 

'make sure you can see this new copy of IE!

ieApp.Visible = True

 

'go to the website of interest

ieApp.Navigate "Wise Owl or other website address goes here"

 

'wait for page to finish loading

Do While ieApp.busy

Loop

 

'get a reference to the search form, by finding its id in the

'web page's document object model

Dim ieElement As Object

Set ieElement = ieApp.document.getElementByID("search")

 

'search box is composed of text box (item 0) and button (item 1)

'set value of text box to what we're searching for

ieElement(0).Value = "Excel VBA courses"

 

'click the button!

ieElement(1).Click

 

End Sub

If you run this macro, it should search the Wise Owl site for information on Excel VBA courses!

Comments on this blog

This blog currently has no comments.

All content copyright Wise Owl Business Solutions Ltd 2014. All rights reserved.