ExcelVBAによるIE自動操作サンプル

概要

InternetExplorerをExcel-VBA経由で操作するサンプルを記述する。
Google検索を自動で行い、検索数を取得してみる。
Excel単体テスト仕様書との連携が待たれない。

コード

参照設定:
Microsoft HTML Object Library
Microsoft Internet Controls

Module1.bat

Attribute VB_Name = "Module1"
Option Explicit

Private Const URL_TARGET As String = "http://google.co.jp/"
Private Const TIME_MINIMUMWAIT As Integer = 1
Private Const TIME_MAXWAIT As Integer = 3

Public Sub Main()
    Dim ie As InternetExplorer
    Dim doc As HTMLDocument

    Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = True

    IENavigate ie, URL_TARGET

    Set doc = ie.document
    With doc
        .getElementById("gbqfq").Value = "google"
        .getElementById("gbqfba").Click
    End With
    IEWaitReady ie

    Range("A1").Value = doc.getElementById("resultStats").innerText

    ie.Quit

    Set doc = Nothing
    Set ie = Nothing
End Sub

Public Sub IENavigate(ByRef ie As InternetExplorer, ByVal url As String)
    ie.navigate url
    IEWaitReady ie
    IEWaitFor
End Sub

Public Sub IEWaitReady(ByRef ie As InternetExplorer)
    Do While ie.Busy = True Or ie.readyState <> 4
        DoEvents
    Loop
    IEWaitFor
End Sub

Public Sub IEWaitFor(Optional ByVal second As Integer = -1)
    Dim endTime As Date

    If (second < 0) Then
        Do While second < TIME_MINIMUMWAIT
            second = Int(Rnd * TIME_MAXWAIT)
        Loop
    End If

    endTime = DateAdd("s", second, Now)
    While Now < endTime
       DoEvents
    Wend
End Sub