Microsoft Excel VBA - ADOを使ってExcelシートを読み出す方法

◆概要

このページは、Excel VBAでActiveX データオブジェクト (ADO) と Microsoft Jet OLE DB 4.0 プロバイダを使用して、 Microsoft Excel ブックのデータを読み取る方法について記載しています。Microsoft Excel は、アウトプロセス ActiveX サーバーです。ADO はインプロセスで実行されるため、リソース消費量の多いアウトプロセスを呼び出した場合のオーバーヘッドが生じません。

　ADOを使ってExcelワークシートのデータを読み出すと、対象のExcelワークシートを開かずに必要なデータを取得することができます。また、SQLを使用できますので、全てのデータを取得することも、特定のデータを取得することも可能です。

◆詳細
　ここでは、例として、次のようなエクセルシートを元データとして使用します。

Sapmle Sheet

ここで注意しなければならないのは、フィールド名に空白が含まれていてはいけないということです。たとえば、単価の文字間に空白１字を入れて「単　価」というようになっていると、エラーが発生します。

まず、ADOを利用するための準備が必要です。ExcelでADOを利用するには、[ツール]-[参照設定]でMicrosoft ActiveX Data Objects 2.x Libraryへの参照設定が必要です。

　次に標準モジュールに以下のコードを記述してください。

Sub loadADOJetOLEDB()
' **************************************************************
' Summary:ADO と Microsoft Jet OLE DB 4.0 プロバイダを使用して、
'         Excelシートに接続する
' Date:2009/09/23
' Written by:White Tiger
' **************************************************************
    Dim cn          As ADODB.Connection
    Dim rs          As ADODB.Recordset
    Dim strFilePath As String
    Dim strFileName As String
    
    On Error GoTo Err_Handler
       
    ' Sheet1のデータをクリアする
    With Sheets("Sheet1").Range("a1:G65536")
        .ClearContents
        .Interior.ColorIndex = xlNone
    End With

    ' ファイルパスを指定する
    strFilePath = "C:\temp\"
    
    ' ファイル名を指定する
    strFileName = "売上.xls"
    
    ' 面倒なのでファイルパス＋ファイル名にする
    strFileName = strFilePath & strFileName
    
    Application.ScreenUpdating = False
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
         "Data Source=" & strFileName & ";" & _
        "Extended Properties=""Excel 8.0;HDR=Yes;"";"

    rs.Open "SELECT * FROM [Sheet1$]", cn, _
        adOpenStatic, adLockOptimistic, adCmdText
    
    ' シート「Sheet1」のB2に貼り付けます。
    ThisWorkbook.Sheets("Sheet1").Range("B2").CopyFromRecordset rs
        
    ' オブジェクトの破棄
    rs.Close: Set rs = Nothing
    cn.Close: Set cn = Nothing
    
    Application.ScreenUpdating = True
    Exit Sub
    
Err_Handler:
    Application.ScreenUpdating = True
    MsgBox CStr(Err.Number) & Err.Description
End Sub

　それではコードを見ていきましょう。

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

ここで、2 つのオブジェクト (ADODB.Connection と ADODB.Recordset) を作成しています。

次のコード部分で Excel ワークシートへの接続を開始します。

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & strFileName & ";" & _
    "Extended Properties=""Excel 8.0;HDR=Yes;"";"

大部分が定型コードです。注意するのは、"Data Source" の部分のみです。この部分では、使用するワークシートへのフルパスを指定します。この例では、変数strFileNameにファイル名をフルパスで事前に指定しています。

　なお、"Excel 8.0" は実行しているバージョンの Excel ではなく、Excel へのアクセスに使用される ADO プロバイダを指します。プロバイダを Excel 8.0 のままにしておくことですべてのバージョンのExcelで動作します。

　また、コードの "HDR=Yes" は単にワークシートにヘッダー行があることを示しています。つまり、ワークシートにヘッダー行がなかったら、HDR には No を設定します。ここは結構重要なポイントです。

データソースへの接続確立後は、SQL クエリを使用して、そのデータソースに格納されている情報を取得できます。ワークシート内のすべての行で構成されるレコードセットを返すには、次のコードを使用します。

rs.Open "SELECT * FROM [Sheet1$]", cn, _
        adOpenStatic, adLockOptimistic, adCmdText

Sheet1は元データのシート名です。ワークシートをレコードソースとして指定するには、ワークシート名の後にドル記号を付け、角かっこで囲みます。シート名が「売上一覧」であれば、ここは売上一覧$になります。
シート名の代わりに範囲名を使用することもできます。たとえば、Table1という範囲名が定義してあれば、Sheet1$の代わりにTable1$と指定します。特定のアドレスを持つ範囲 (Sheet1$A1:B10 など) を使用することもできます。その場合は、

"Select * from [売上$A2:B10]"

のように指定します。

SQL文を工夫すれば、特定のデータのみを取得することもできます。たとえば、商品CDと単価のみを取得するには次のようにSQL文を変更するだけです。

"SELECT 商品CD,単価 FROM [Sheet1$]"

レコードセットにレコードが取得されたので、後はそのレコードセットをシートに書き込むだけです。ここではRangeオブジェクトのCopyFromDatasetメソッドを使うことがポイントです。

ThisWorkbook.Sheets("Sheet1").Range("B2").CopyFromRecordset rs

この例では、Sheet1のB2を左上端起点として、データを貼り付けています。

最後にオブジェクトの破棄を行います。レコードセットを先に行い、ADODB.Connectionは後から破棄します。

rs.Close: Set rs = Nothing
cn.Close: Set cn = Nothing

詳しい説明は、マイクロソフトの「Microsoft Office アプリケーションのスクリプト作成に関するヒントとテクニック」にあるADO を使用して Excel ワークシートにクエリを実行するを参照してください。
▼ページトップへ

White Tiger

Microsoft Excel VBA - ADOを使ってExcelシートを読み出す方法

Link

design: TempNate