【ExcelVBA】SQL Serverのストアドプロシージャで抽出したデータをExcelのマクロが受け取るには

この記事では、SQL Serverのストアドプロシージャで抽出したデータをExcelのマクロが受け取る方法についてご説明します。

Table of Contents

【動画】SQL Serverのストアドプロシージャで抽出したデータをExcelのマクロが受け取る実際の動き

本題に入る前に、まずはSQL Serverのストアドプロシージャで抽出したデータをExcelのマクロが受け取る実際の動きについて、次のツイートをご覧ください。

ExcelのマクロでSQL Serverのストアドを呼び出し、そのストアド内のselect文で抽出したデータをマクロ側に渡す処理を作ってみました😃

抽出したデータをマクロ側が受け取るには、recordsetの扱いさえ分かれば特に難しくはないですね😊#Excel #vba #マクロ #SQLServer pic.twitter.com/vDfkeypazh

— まさ@Excel、VBA、マクロ（経験年数16年） (@masamasa9785) April 1, 2021

ツイート内の動画では、SQL Serverのストアドプロシージャで抽出したデータをExcelのマクロが受け取っています。

目次へ戻る

SQL Serverのストアドプロシージャで抽出したデータをExcelのマクロが受け取る方法

SQL Serverのストアドプロシージャで抽出したデータをExcelのマクロが受け取るには、次の流れの通りにコードを書いていきます。

STEP.1

DBへの接続情報取得

ストアドプロシージャを実行するにはまずはDBに接続しなければなりませんが、接続する情報(ユーザ名やパスワードといった接続情報)がなければDBに接続できません。
なので、最初にDBに接続するための接続情報を取得します。

STEP.2

DBへの接続

接続情報が取得出来たら、その接続情報をもとにDBに接続します。

STEP.3

ストアドプロシージャの実行

ストアドプロシージャを実行します。

STEP.4

ストアドプロシージャ内で実行されたSQLの抽出結果を取得

ストアドプロシージャ内で実行されたSQLの抽出結果を取得します。
抽出結果の取得はRecordsetオブジェクトのGetRowsメソッドを使い、配列に格納することができます。

目次へ戻る

コードの例

Excelのマクロのコード(例)

    Dim DBName As String, connDB As String
    Dim objConnection As ADODB.Connection
    Dim objCommand As ADODB.Command
    Dim objRecordset As ADODB.Recordset

    'データベース接続情報を取得
    'DBの名前
    DBName = "workDB"
    connDB = "Provider=SQLNCLI11.1;"
    connDB = connDB & "Data Source=(LocalDB)\MSSQLLocalDB;"
    connDB = connDB & "Initial Catalog=" & DBName & ";"
    connDB = connDB & "Trusted_Connection=yes;"

    'DBへの接続
    Set objConnection = New ADODB.Connection
    objConnection.CursorLocation = adUseClient
    objConnection.Open connDB

    '実行するストアドプロシージャを設定
    Set objCommand = New ADODB.Command
    Set objCommand.ActiveConnection = objConnection
    objCommand.CommandType = adCmdStoredProc
    objCommand.CommandText = "dbo.sp_dataEdit"

    'ストアドプロシージャ内で実行されたSQLの結果を取得
    Set objRecordset = objCommand.Execute
            
    '取得したデータを配列に格納する
    getVal = objRecordset.GetRows

    '終了処理
    objRecordset.Close
    Set objRecordset = Nothing
    Set objParameter = Nothing
    Set objCommand = Nothing
    Set objConnection = Nothing

コードの解説

最初に見て頂きたいのは26行目です。

この26行目では、ストアドプロシージャの実行をExecuteメソッドで行っています。

Executeメソッドはストアドプロシージャを実行するだけでなく、ストアドプロシージャ内で実行されたSQLの抽出結果を取得することもできます。(抽出結果はRecordsetオブジェクトのインスタンスに取得)

では、このRecordsetオブジェクトのインスタンスから、ストアドプロシージャ内で実行されたSQLの抽出結果を取得するにはどうすればいいのかというと、29行目をご覧ください。

RecordsetオブジェクトのGetRowsメソッドを使っているのが分かるかと思います。

このGetRowsメソッドを使うことで、Recordsetオブジェクトのインスタンスから、ストアドプロシージャ内で実行されたSQLの抽出結果を取り出すことができます。(取り出した抽出結果は配列に格納することができます)

目次へ戻る

ストアドプロシージャのコード(例)も見てみましょう

ストアドプロシージャのコード(例)は次の通りです。

ALTER PROCEDURE [dbo].[sp_dataEdit]

AS
BEGIN

	SET NOCOUNT ON;

	BEGIN TRANSACTION

	/* データを抽出する */
	select * from dbo.tbl_userInfo

	COMMIT TRANSACTION

END

コード(例)では非常にシンプルなselect文が記載されているだけですが、このselect文の抽出結果がマクロ側に渡ります。

目次へ戻る