VBA Corner (vba_corner) wrote,

Working with Internet Explorer Using VBA

Ok, Excel probably isn't the first thing that comes to mind when needing to deal with web pages. But sometimes it's necessary to access them from your Excel Application (or Word or any other MS Office Application). And it's easier than you probably think.

To make the following code work, you'll need to include the "Microsoft Internet Controls" library in your VBA references first.
Go to your Visual Basic Editor, Menu Tools -> References... and select the entry "Microsoft Internet Controls". If you can't find it in the list of available references, search for a file named shdocvw.dll, usually to be found in your Windows directory, subfolder System32.

Add reference to 'Microsoft Internet Controls' library

Alternatively, you can skip the referencing and use late binding, defining the pointer to the Internet Explorer instance as Object instead of SHDocVw.InternetExplorer in your VBA code. But then you'll miss out on a lot of goodies like intellisense, full access to the object model via the object browser, access to the application's built-in constants, and a popup listing all the supported constants upon typing...

Before you can do anything with the Internet Explorer, of course you'll need one, and you'll need something to address it. The following function achieves exactly this by creating a new instance of the Internet Explorer and returning a pointer to it.

'returns new instance of Internet Explorer
Function GetNewIE() As SHDocVw.InternetExplorer
  'create new IE instance
  Set GetNewIE = New SHDocVw.InternetExplorer
  'start with a blank page
  GetNewIE.Navigate2 "about:Blank"
End Function

The next function loads a webpage:

'loads a web page and returns True or False depending on
'whether the page could be loaded or not
Function LoadWebPage(i_IE As SHDocVw.InternetExplorer, _
                     i_URL As String) As Boolean
  With i_IE
    'open page
    .Navigate i_URL
    'wait until IE finished loading the page
    Do While .ReadyState <> READYSTATE_COMPLETE
      Application.Wait Now + TimeValue("0:00:01")
    Loop
    'check if page could be loaded
    If .Document.URL = i_URL Then
      LoadWebPage = True
    End If
  End With
End Function

But what if the desired page has already been loaded and you just want to use it?
The following function returns a pointer to an already open instance of the Internet Explorer, if it has the desired page loaded. It'll find it by comparing this page's URL with the URLs of open pages in the Internet Explorer. If the page could not be found, the function returns Nothing.

'finds an open IE site by checking the URL
Function GetOpenIEByURL(ByVal i_URL As String) As SHDocVw.InternetExplorer
Dim objShellWindows As New SHDocVw.ShellWindows

  'ignore errors when accessing the document property
  On Error Resume Next
  'loop over all Shell-Windows
  For Each GetOpenIEByURL In objShellWindows
    'if the document is of type HTMLDocument, it is an IE window
    If TypeName(GetOpenIEByURL.Document) = "HTMLDocument" Then
      'check the URL
      If GetOpenIEByURL.Document.URL = i_URL Then
        'leave, we found the right window
        Exit Function
      End If
    End If
  Next
End Function

Sometimes, when you enter an URL to load a page, you'll get redirected, and thus the URL changes. Or your site uses frames and has the same URL for different content, so you can't clearly identify a page by its URL. Then you could check the title of the page instead:

'finds an open IE site by checking the title
Function GetOpenIEByTitle(i_Title As String, _
                          Optional ByVal i_ExactMatch As Boolean = True) As SHDocVw.InternetExplorer
Dim objShellWindows As New SHDocVw.ShellWindows

  If i_ExactMatch = False Then i_Title = "*" & i_Title & "*"
  'ignore errors when accessing the document property
  On Error Resume Next
  'loop over all Shell-Windows
  For Each GetOpenIEByTitle In objShellWindows
    'if the document is of type HTMLDocument, it is an IE window
    If TypeName(GetOpenIEByTitle.Document) = "HTMLDocument" Then
      'check the title
      If GetOpenIEByTitle.Document.Title Like i_Title Then
        'leave, we found the right window
        Exit Function
      End If
    End If
  Next
End Function
(This was tested with IE6. Since IE7 uses tabs for different webpages instead of different windows, this code probably won't work for IE7.)

Now that you have your web page (either by loading it new, or by accessing an already opened page), you can extract information from it, or manipulate the document or do whatever you want/need to do.
To make work with html documents easier, the Document object itself is just the root of a hierarchy of objects (and arrays of objects) that represent the different html tags in the document with all their properties and content. So if you need to address a certain element, you wont have to parse the html source, but you can quite easily address the element directly by its name or index.

A good introduction about how to work with the DOM (Document Object Model) and how to use it with forms in particular is given on this German site: Tagmodell des Webbrowsers

Here's a link to a full reference about the document object and all elements in there.

Last but not least, a very good and comprehensive but also German site is the SELFHTML DOM reference


And because an example often is easier to understand than long-winded explanations: the following example goes to Wikipedia, enters a search string and navigates to the search result.

Sub ExplorerTest()
Const myPageTitle As String = "Wikipedia"
Const myPageURL As String = "http://en.wikipedia.org/wiki/Main_Page"
Const mySearchForm As String = "searchform"
Const mySearchInput As String = "searchInput"
Const mySearchTerm As String = "Document Object Model"
Const myButton As String = "Go"

Dim myIE As SHDocVw.InternetExplorer

  'check if page is already open
  Set myIE = GetOpenIEByTitle(myPageTitle, False)
  
  If myIE Is Nothing Then
    'page isn't open yet
    'create new IE instance
    Set myIE = GetNewIE
    'make IE window visible
    myIE.Visible = True
    'load page
    If LoadWebPage(myIE, myPageURL) = False Then
      'page wasn't loaded
      MsgBox "Couldn't open page"
      Exit Sub
    End If
  End If
  
  With myIE.Document.forms(mySearchForm)
    'enter search term in text field
    .elements(mySearchInput).Value = mySearchTerm
    'press button "Go"
    .elements(myButton).Click
  End With
       
End Sub

Of course an Internet Explorer Object doesn't only contain the Document object. There's a lot more, and you can find an overview by going to your VBA editor and pressing F2. This will open the object browser, where you can look for library SHDocVw, class InternetExplorer, and you'll find a list and short description of all methods, properties and events of this class.

Tags: document, dom, excel, html, internet_explorer, ms_office, web, webpage, word
  • Post a new comment

    Error

    Anonymous comments are disabled in this journal

    default userpic

    Your reply will be screened

    Your IP address will be recorded  

  • 58 comments
Previous
← Ctrl← Alt
Next
Ctrl →Alt →
Previous
← Ctrl← Alt
Next
Ctrl →Alt →