Excel VBAを利用してデータベースのストアドプロシージャを実行し、結果を表示する方法

この記事では、Excel VBAを使用してデータベースのストアドプロシージャを実行し、その結果をExcelシート上に表示する方法について詳細に説明します。具体的なコード例、その詳細な解説、および応用例を含めています。

はじめに

データベースのストアドプロシージャは、SQLサーバーなどのデータベースに保存される事前に定義されたSQLスクリプトであり、これを実行することで複雑な操作やクエリを容易に行うことができます。Excel VBAからこのストアドプロシージャを呼び出すことで、データベースの情報をリアルタイムでExcel上に取得・表示することが可能となります。

基本的な実装方法

まず、基本的な実装方法としてのコードを以下に示します。

Sub RunStoredProcAndDisplayResults()
    Dim conn As Object
    Dim cmd As Object
    Dim rs As Object
    Dim connectionString As String
    
    ' 接続文字列の設定
    connectionString = "Provider=SQLOLEDB;Data Source=YOUR_SERVER_NAME;Initial Catalog=YOUR_DB_NAME;User Id=YOUR_USER;Password=YOUR_PASSWORD;"
    
    ' オブジェクトの作成
    Set conn = CreateObject("ADODB.Connection")
    Set cmd = CreateObject("ADODB.Command")
    Set rs = CreateObject("ADODB.Recordset")
    
    ' データベース接続
    conn.Open connectionString
    
    ' コマンドの設定
    With cmd
        .ActiveConnection = conn
        .CommandType = 4  ' CommandTypeEnum.adCmdStoredProc
        .CommandText = "Your_StoredProcedure_Name"
        Set rs = .Execute
    End With
    
    ' 結果をExcelに表示
    ThisWorkbook.Worksheets("Sheet1").Range("A1").CopyFromRecordset rs
    
    ' オブジェクトの開放
    rs.Close
    Set rs = Nothing
    Set cmd = Nothing
    conn.Close
    Set conn = Nothing
End Sub

コードの詳細解説

1. **オブジェクトの初期化**: `ADODB.Connection`、`ADODB.Command`、`ADODB.Recordset` の3つのオブジェクトを利用しています。これらは、データベース接続、コマンドの実行、結果の取得にそれぞれ使用されます。
2. **接続文字列の設定**: 接続文字列では、サーバーの名前、データベースの名前、ユーザーID、パスワードを指定します。
3. **データベースへの接続**: `conn.Open` メソッドを使用してデータベースに接続します。
4. **ストアドプロシージャの実行**: `CommandType`を`4`に設定することで、コマンドとしてストアドプロシージャを実行することを指定します。`CommandText`には実行したいストアドプロシージャの名前を指定します。
5. **結果の取得**: `Execute` メソッドを使用してストアドプロシージャを実行し、その結果を `Recordset` オブジェクトに格納します。
6. **結果のExcelへの表示**: `CopyFromRecordset` メソッドを使用して、結果をExcelシートに転送します。

応用例

応用例1: パラメータを持つストアドプロシージャの実行

ストアドプロシージャには、実行時にパラメータを渡して結果をフィルタリングするものもあります。

With cmd
    .ActiveConnection = conn
    .CommandType = 4
    .CommandText = "Your_StoredProcedure_WithParam"
    .Parameters.Append .CreateParameter("@ParamName", 200, 1, 50, "ParameterValue")  ' ParamType, ParameterDirection, Size, Value
    Set rs = .Execute
End With

応用例2: 複数のストアドプロシージャの結果を異なるシートに表示

一度の接続で複数のストアドプロシージャを実行し、それぞれの結果を異なるシートに表示する方法です。

' First stored procedure
With cmd
    .ActiveConnection = conn
    .CommandType = 4
    .CommandText = "StoredProcedure1"
    Set rs = .Execute
End With
ThisWorkbook.Worksheets("Sheet1").Range("A1").CopyFromRecordset rs

' Second stored procedure
With cmd
    .CommandText = "StoredProcedure2"
    Set rs = .Execute
End With
ThisWorkbook.Worksheets("Sheet2").Range("A1").CopyFromRecordset rs

応用例3: 実行結果をExcelのテーブルとしてフォーマット

取得したデータをExcelのテーブルとして整形し、見やすく表示します。

Dim rng As Range
Set rng = ThisWorkbook.Worksheets("Sheet1").Range("

A1").CurrentRegion
rng.ListObjects.Add(xlSrcRange, rng, , xlYes).Name = "DataTable"

まとめ

Excel VBAを使用してデータベースのストアドプロシージャを実行し、その結果をExcel上に表示する方法を学びました。この技術を使うことで、リアルタイムのデータベース情報をExcel上で容易に取得・解析することが可能となります。