Excel VBAでネットワークデータの獲得、XML、WMI、API拡張を実現する

システムのCOMによって実行できる機能が、基本的にVBAで実行できます。さらに、対応する関数を完了するためにCOMオブジェクトを呼び出す以外に、VBAの機能を拡張する最も純粋な方法は、APIを直接呼び出すことです。これはVBAの範囲を超えています。ここで簡単に説明します。 COMオブジェクトを作成する場合、「アーリーバインディング」と「レイトバインディング」の区別を説明しません。オブジェクトごとに、一般的な使い方を簡単にまとめます。

ネットワークへの応用

Workbookオブジェクトを直接使用して、ネットワークの内容を取得します。

Frontpage Server Extensionを備えたサーバーでは、直接にWorkbookの形式で保存して、Excelを開けます。これらのExcelドキュメントを共有することに加えて、Workbookを使用して、Webページのコンテンツを直接開いて使用できます。開いた後は基本的にSheetのように扱われます。

Dim oBk As Workbook
'リモートでExcelを開く
Set oBk = Workbooks.Open("http://www.MySite.com/book1.xlsx")
'名前を付けて保存する
oBk.SaveAs "http://www.MySite.com/Book2.xlsx"

Dim oRng As Range
'Webページを開く
Set oBk = Workbooks.Open("http://www.x-rates.com/d/USD/table.html")
'内容を検索する
Set oRng = oBk.Worksheets(1).Cells.Find("British Pound")
'内容を表示する
MsgBox oRng.Offset(0, 1).Value

Web Queryを使用してネットワークデータを取得します

Excel97がWeb Queryのサポートを追加した以来、更新するたびに強化されています。この機能を使用して、ネットワーク上のテーブルのデータを取得できます。主にApplication.QueryTablesを使います。例は次のとおりです。

Sub GetRatesWithWebQuery()
Dim oBk As Workbook
Dim oQT As QueryTable
'数値の書式設定
Dim sDecimal As String
Dim sThousand As String
Dim bUseSystem As Boolean

Set oBk = Workbooks.Add
With oBk.Worksheets(1)
Set oQT = .QueryTables.Add( _
Connection:="URL;http://www.x-rates.com/d/USD/table.html", _
Destination:=.Range("A1"))
End With

'QueryTableの関連プロパティを設定する
With oQT
.Name = "USD"
'特定のテーブルを選択する
.WebSelectionType = xlSpecifiedTables
'ページにある14番目のテーブルをインポートする
.WebTables = "14"
'ページの書式を無視する
.WebFormatting = xlWebFormattingNone
'日付を識別しない
.WebDisableDateRecognition = True
'ファイルを開くたびにデータが更新されない
.RefreshOnFileOpen = False
'検索が終了まで待つ
.BackgroundQuery = True
'Workbookでデータを保存する
.SaveData = True
'テーブルのデータに応じて列幅を調整する
.AdjustColumnWidth = True
End With
With Application
'現在のドキュメントの区切りを保存する
sDecimal = .DecimalSeparator
sThousand = .ThousandsSeparator
bUseSystem = .UseSystemSeparators
'区切りを設定する
.DecimalSeparator = "."
.ThousandsSeparator = ","
.UseSystemSeparators = True
'エラーを無視する
On Error Resume Next
'検索を実行し、終了まで待つ
oQT.Refresh BackgroundQuery:=False
'ドキュメントの区切りを復元する
.DecimalSeparator = sDecimal
.ThousandsSeparator = sThousand
.UseSystemSeparators = bUseSystem
End With
End Sub

この方法は、ページ内のノードの順序に大きく依存するため、注意してください。

Internet Explorerオブジェクトを使用してDOMモデルを操作します

前の方法を使用する場合、一定的な制限があります。テーブル全体ではなく、ページの一部のみに焦点を当てる場合は、この時点でInternetExplorerオブジェクトを使用するのが最も便利です。このオブジェクトのDocumentプロパティは、ページ全体を表します。このDocumentのプロパティとメソッドを使用して、ページのさまざまな部分を取得できます。（これは、DOM操作と呼ばれることがよくあります）このオブジェクトを使用してページの内容を処理することは、最も強力で直接的な方法です。以下は、InternetExplorerオブジェクトの使用例です

Sub GetUSDtoGBPRateUsingIE()
Dim oIE As InternetExplorer
Dim sPage As String
Dim iGBP As Long, iDec As Long
Dim iStart As Long, iEnd As Long
Dim dRate As Double
'非表示のIEインスタンスを作成する。Visibleプロパティを設定することでIEを表示できる
Set oIE = New InternetExplorer
'Webページを開く
oIE.Navigate "http://www.x-rates.com/d/USD/table.html"

'次の方法でロードが完了するまで待つ:
'While obIE.Busy = True
'DoEventsWend
Do Until oIE.readyState = 4
DoEvents
Loop

'DOM操作
sPage = oIE.Document.body.InnerText

'ターゲットを見つける
iGBP = InStr(1, sPage, "British Pound")
iDec = InStr(iGBP, sPage, ".")

iStart = InStrRev(sPage, " ", iDec) + 1
iEnd = InStr(iDec, sPage, " ")
dRate = Val(Mid$(sPage, iStart, iEnd - iStart))

MsgBox "The USD/GBP exchange rate is " & dRate
End Sub

XmlHttpを使用してWeb内容ツを取得します

XmlHttpは、Javascript、VbScript、Jscriptなどのスクリプト言語でhttpプロトコルでXMLやその他のデータを送受信できるAPIのセットです。XmlHttpの最大の用途は、ページ全体を更新せずにWebページの一部を更新できることです。MSDNからの説明：XmlHttpは、端末がhttpサーバーと通信するためのプロトコルを提供します。端末は、XmlHttpオブジェクト（MSXML2.XMLHTTP.3.0）でhttpサーバーに要求を送信し、Microsoft®XMLドキュメントオブジェクトモデル（DOM）を使用して応答を処理できます。現在ほとんどのブラウザでXmlHttpのサポートが追加されました。IEはActiveXObjectを使用してXmlHttpオブジェクトを作成します。FirefoxやOperaなどの他のブラウザはwindow.XMLHttpRequestを使用してxmlhttpオブジェクトを作成します。この方法でWeb内容にアクセスする方が速いですが、互換性はIEオブジェクトを使用するほど良くありません。

VBAがネットワークからファイルをダウンロードすることは、XmlHttpオブジェクトで実行できます。例は次のとおりです。

Set http = CreateObject("Microsoft.XmlHttp")

http.Open "GET", url, True
http.send

If http.ReadyState <> 4 Then
DoEvents
Else

Set oStream = CreateObject("ADODB.Stream")
oStream.Type = 1
oStream.Open
oStream.Write http.responsebody
sFile = Replace(Mid(url, InStrRev(url, "/") + 1), "?", "-")
oStream.SaveToFile "C:" & "\" & sFile, 2 'ローカルにファイル名を保存する
oStream.Close
End if

VBAでWebServiceを呼び出します

これはVBAでも実装でき、通常はクラスライブラリのサポートが必要です。実際、これはXML操作に関連しています。次のリンクを参照してください。

Webサービスの呼び出し方法：http://msdn.microsoft.com/en-us/magazine/cc163837.aspx

XML操作

VBAでのXMLドキュメントの処理により、以前のファイルシステムで開くと保存することはもう説明しました。次にはまとめます。

通常のテキストドキュメントとして処理する、またはWorkbookで開いて処理します。

このメソッドを使用し、補助正規表現に加えて文字列を処理するための多くのメソッドでタスクを実行できますが、XMLのメリットはまったく役に立ちません。

ADOを使用してXMLドキュメントを処理します。

これはADOの能力を完全に反映しており、ほとんどすべてのタスクをSQLで完了できます。MSADOライブラリへの参照を追加することを忘れないでください。

XMLを生成する操作の例：

Sub Create_XML_Recordset() 
    Const stSQL As String = "SELECT * FROM [Report]"
    Dim stCon As String      
    stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=" & ThisWorkbook.FullName & ";" & _ 
    "Extended Properties=""Excel 8.0;HDR=Yes"";" 
     
    Dim rst As New ADODB.Recordset 
    Dim str As New ADODB.Stream 
     
    With rst 
        .CursorLocation = adUseClient 
        .Open stSQL, stCon, adOpenStatic, adLockReadOnly, adCmdText 
        .Save str, adPersistXML 
        .Close 
        With str 
            .SaveToFile "C:\Report.xml", adSaveCreateOverWrite 
            .Close 
        End With 
    End With 
     
    Set str = Nothing 
    Set rst = Nothing 
End Sub

XMLを読み取る操作の例：

Sub Read_XML_Data() 
    Dim rst As ADODB.Recordset 
    Dim stCon As String, stFile As String 
    Dim i As Long, j As Long 
     
    Set rst = New ADODB.Recordset 
     
    stFile = "C:\Report.xml" 
    stCon = "Provider=MSPersist;" 
     
    With rst 
        .CursorLocation = adUseClient 
        .Open stFile, stCon, adOpenStatic, adLockReadOnly, adCmdFile 
        Set .ActiveConnection = Nothing 
    End With 
     
    With ActiveSheet 
        For j = 0 To i - 1 
            .Cells(1, j + 1).Value = rst.Fields(j).Name 
        Next j 

        .Range("A2").CopyFromRecordset rst 
    End With 
     
    rst.Close 
    Set rst = Nothing      
End Sub

MSXMLでXMLドキュメントを処理します

これは、XMLドキュメントを処理するための一押しのメソッドです。XML DOMのすべてのメソッドと属性を提供し、非常に使いやすいです。このオブジェクトを使用するには、“Microsoft XML，v6.0”への参照を追加する必要があります。

Sub ReadXML()
    Dim xmlDom As MSXML2.DOMDocument
    Dim xmlPlaceMark As MSXML2.IXMLDOMNode
    Dim xmlPolygon As MSXML2.IXMLDOMNode
    Dim xmlCoord As MSXML2.IXMLDOMNode
    Dim sName As String
    Dim vaSpace As Variant, vaComma As Variant
    Dim i As Long, j As Long
    
    Set xmlDom = New MSXML2.DOMDocument    
    xmlDom.Load "C:\Downloads\overlay_1198.kml"
    
    For i = 0 To xmlDom.childNodes(1).childNodes(0).childNodes.Length - 1
        If xmlDom.childNodes(1).childNodes(0).childNodes.Item(i).nodeName = "Placemark" Then
            Set xmlPlaceMark = xmlDom.childNodes(1).childNodes(0).childNodes.Item(i)
            Set xmlPolygon = xmlPlaceMark.childNodes(2).childNodes(0)
            Set xmlCoord = xmlPolygon.childNodes(0).childNodes(0).childNodes(0)
            sName = xmlPlaceMark.childNodes(1).childNodes(5).nodeTypedValue                

            With Sheet4.Cells(Sheet2.Rows.Count, 1).End(xlUp).Offset(1, 0)
                .Value = sName
                vaSpace = Split(xmlCoord.childNodes(0).Text, " ")
                For j = LBound(vaSpace) To UBound(vaSpace)
                    vaComma = Split(vaSpace(j), ",")
                    .Offset(0, 1).Value = vaComma(0)
                    .Offset(0, 2).Value = vaComma(1)
                Next j
            End With
        End If 
    Next i
End Sub

WMI情報

WMIは、Windows 2000、Windows XP、およびWindows Server 2003シリーズのオペレーティングシステムに組み込まれているコア管理サポートテクノロジです。 WMIは、Distributed Management Task Force（DMTF）によって監視されている業界標準に基づいており、ほぼすべてのWindowsリソースにアクセス、設定、管理、および監視できる規範と基本的な構成です。ほとんどのユーザーは、多数のグラフィカル管理ツールを使用してWindowsリソースを管理することに慣れています。WMIが発表した前は、これらのツールはWin32アプリケーションプログラミングインターフェイス(Application ProgrammingInterfaces，API)でWindowsリソースにアクセスして管理していました。システムプログラミングに精通している限り、APIの重要性をご存知でしょう。ただし、ほとんどのスクリプト言語はWin32 APIを直接呼び出すことができません。WMIの登場により、システム管理者は一般的なスクリプト言語を使用して、常用のシステム管理タスクを簡単な方法で実装できます。WMIの使用は、WSHやVBScriptなどのスクリプトと組み合わせる必要があります。実現できる機能は、MicrosoftのMSDNドキュメントに参照してください。

API拡張

理論的には、VBAでAPIを使用すると、任意のオペレーティングシステムによって提供される機能を実行できますが、実際には、必要な場合を除いて、VBAまたは他のCOMによって提供される機能を使用するだけで十分です。上記の関数がニーズを満たさない場合（特にVBAで繊細なUserFormを使用する必要がある場合）、APIを使用します。APIを使用するには、使用する前にAPIを宣言する必要があります。宣言プロセスにはタイプの変換も含まれます。

CreateObjectとGetObjectの違い：上記にはCreateObjectを何度も使用してCOMインスタンスを作成しました。ここでは、別の関連関数について簡単に説明します。

オブジェクトの現在のインスタンスがある場合、またはロードされたファイルを使用してオブジェクトを作成する場合は、GetObject関数を使用して、COMコンポーネントのパスをこの関数に渡すだけでいいです。現在のインスタンスがなく、ロードされたファイルを使用してオブジェクトを開始したくない場合は、CreateObject関数を使用します。常用の使い方は、ExcelファイルのパスをGetObjectに渡すことです。このとき、メソッドはファイルに対応するWorkbookオブジェクトを返しますが、ファイルを開くのは表示しません。このとき、現在開いているExcelファイルにSheetをコピーするなど、ファイルの内容をバックグラウンドで処理できます。