連載
» 2005年08月06日 00時00分 公開

Excel‐Oracle連携(2):お手軽が一番、ExcelをOracleクライアントに (2/3)

[宮本尚志,@IT]

マクロを使用する方法

 Excelでは、VBAマクロのコードの中にOracleデータベースへの接続や、検索処理の命令を記述できます。コーディングが発生するため、誰でも簡単にできるわけではありませんが、その分柔軟な処理が可能です。

ミドルウェアの選択

 この方法では、VBAでOracleデータベースに接続するためのインターフェイスに何を使うかによって、開発手法も異なります。非常に多くのパターンがありますが、主要なものは、

  • ActiveXData Objects(以下ADO)からODBCドライバを使用する方法
  • ADOからOLE DBドライバを使用する方法
  • Oracle Objectsfor OLE(以下oo4o)を使用する方法

の3つです。どれを使ってもExcelのマクロからOracleデータベースのデータを検索できますが、それぞれに特性があるので、違いを理解したうえでどれを使用するか決定することになります。以下に違いをまとめてみました。

連携パターン メリット デメリット
ADO+ODBC ・汎用的なADOのAPIを使用して開発できる ・Oracleデータベース固有のデータ型やパラメータなどが使用できない
・ODBCデータソースの設定が必要
・ODBCブリッジを介すため、ADO+OLE DBよりパフォーマンスが劣る
ADO+OLE DB ・汎用的なADOのAPIを使用して開発できる ・Oracleデータベース固有のデータ型やパラメータなどが使用できない
・oo4oよりはパフォーマンスが劣る
oo4o ・追加的に必要なミドルウェアがないため、バージョン間での整合性などを意識する必要がない
・Oracleで実装されているすべての機能を利用可能
・PL/SQLを記述可能
・DAOに類似したインターフェイスを利用し、プロパティ、メソッドを用いることでプログラミングが容易
・oo4oのパラメータを変更することで、oo4o自体のチューニングが可能、高パフォーマンス
・Oracle以外のRDBMSには接続できない
・コーディングに使用するメソッドなどはoo4o固有
表2 VBAのマクロで使用するミドルウェアごとの違い

 それぞれの開発手法での実際のマクロは以下のようになります。このマクロでは、SIDまたはDSN名がorclであるデータベースに対して接続し、「商品情報」「商品分類」という表から検索した結果をExcelシートの4行2列目に出力します。リスト1?3に掲載しているのは、そのマクロの主要部分を抜粋したものです。赤字部分はそれぞれデータベースユーザー名、パスワード、接続記述子(ADO+ODBCではDSN名)で、環境により異なります。SQL文に日本語の列名や表名などが含まれる場合は、二重引用符「"」で囲むことに注意してください。

Private Sub CommandButton1_Click()

On Error GoTo Err_Han
    
    If oraconn.State = 0 Then
        Call Conn
    End If
    
    ' データ取得メイン処理を呼び出す
    getProductInfo

    Exit Sub
    
Err_Han:
    ' エラー処理
    MsgBox (Err.Description)

End Sub

'データベース接続処理
Private oraconn As New ADODB.Connection
Private Sub Conn()

    ConnectionString = "DSN=orcl;UID=scott;PWD=tiger"
    oraconn.ConnectionString = ConnectionString
    oraconn.Open

End Sub

'データ取得メイン処理
Private Sub getProductInfo()
    
    ' 変数定義
    Dim rs As ADODB.Recordset      ' レコードセット(商品情報)
    
    ' データ取得処理
    Set rs = selectProducts
    
    ' データが取得されなかったら何もしない
    If rs Is Nothing Then
        Exit Sub
    End If
    
    ' 商品情報データをシートに格納
    Call setDataView(rs, theSheet.Cells(4, 2))
  
    ' レコードセットクローズ
    rs.Close

End Sub

'データ取得処理
Private Function selectProducts() As ADODB.Recordset

    Dim strSql As String
 
    Dim strSql As String
 
    strSql = " select ""商品ID"",""商品名"",""分類名"", " & _
             "        ""内容量"",""内容量単位"",""原材料""," & _
             "        ""保存方法"",""賞味期限"",""価格"" " & _
             "   from ""商品情報"",""商品分類"" " & _
             "  where ""商品情報"".""分類ID"" = ""商品分類"".""分類ID""
    
    Set selectProducts = oraconn.Execute(strSql)
    
End Function
リスト1 ADO+ODBCの場合のデータベースへの接続(表示の都合で改行されています。コードはlist01から確認できます)

'〜 ボタンクリックのプロシージャはADO+ODBCと同一なので省略 〜

Private oraconn As New ADODB.Connection
Private Sub Conn()

    ' データベースに接続する
    ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=orcl;User ID=scott;Password=tiger;"
    
    oraconn.ConnectionString = ConnectionString
    oraconn.Open

End Sub

'〜 データ取得処理はADO+ODBCの場合と同一なので省略 〜
リスト2 ADO+OLE DBの場合のデータベースへの接続(表示の都合で改行されています。コードはlist02から確認できます)

Private Sub CommandButton1_Click()

On Error GoTo Err_Han
    
    If OraDB Is Nothing Then
        Call Conn
    End If
    
    If OraDB.ConnectionOK = False Then
        Call Conn
    End If
    
    ' データ取得メイン処理を呼び出す
    getProductInfo

    Exit Sub
    
Err_Han:
    ' エラー処理
    MsgBox (Err.Description)

End Sub

Private OraSess As OraSession
Private OraDB As OraDatabase 

' データベース接続処理
Private Sub Conn()

    Set OraSess = CreateObject("OracleInProcServer.XOraSession")
    Set OraDB = OraSess.OpenDatabase("orcl", "scott" & "/" & "tiger", 0&)

End Sub

'データ取得メイン処理
Private Sub getProductInfo()
    
    ' 変数定義
    Dim oraDS As OraDynaset      ' レコードセット(商品情報)
    
    ' データ取得処理
    Set oraDS = selectProducts()
    
    ' データが取得されなかったら何もしない
    If oraDS Is Nothing Then
        Exit Sub
    End If
    
    ' 商品情報データをシートに格納する
    Call setDataView(oraDS, theSheet.Cells(4, 2))
  
    ' レコードセットクローズ
    oraDS.Close

End Sub

'データ取得処理
Private Function selectProducts() As OraDynaset

    Dim strSql As String
 
    strSql = " select ""商品ID"",""商品名"",""分類名"", " & _
             "        ""内容量"",""内容量単位"",""原材料""," & _
             "        ""保存方法"",""賞味期限"",""価格"" " & _
             "   from ""商品情報"",""商品分類"" " & _
             "  where ""商品情報"".""分類ID"" = ""商品分類"".""分類ID""
    
    Set selectProducts = OraDB.CreateDynaset(strSql, 0&)
    
End Function
リスト3 oo4oの場合のデータベースへの接続(表示の都合で改行されています。コードはlist03から確認できます)

 上記を一部編集したマクロを含むExcelシートと、表作成用のSQLスクリプトを以下からダウンロードできます。

 ・サンプルのダウンロード(demo.zip)

 マクロを使用する方法では、MS Queryよりも柔軟な検索処理が可能です。あらかじめ帳票のようなExcelシートを作成しておき、その中で特定のセルにデータベースからの検索結果を出力することも可能ですし、検索のみならず更新処理を行うことも可能です。クライアントにOracleClientは必要になりますが、マクロを含んだExcelファイルを配布すれば、受け取った人はそのファイルを開いてマクロを実行するだけで、データベースからの検索を行うことが可能です。

 ただ、マクロをコーディングするのは手間がかかります。次に説明するKeySQLを使用すると、同等の処理をもっと簡単に行うことが可能です。(次ページに続く)

Copyright © ITmedia, Inc. All Rights Reserved.

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。