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

3分

SHARE

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

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

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

ツイート内の動画では、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文の抽出結果がマクロ側に渡ります。

目次へ戻る

最後に

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

大まかな流れとしては次の3つのステップになります。

①Executeメソッドでストアドプロシージャを実行
②ストアドプロシージャ内で実行されたSQLの抽出結果を、Recordsetオブジェクトに格納
③RecordsetオブジェクトのGetRowsメソッドで、②のSQLの抽出結果を取り出す(配列に格納)

以上の3ステップを見て頂ければ、処理の流れを掴んでいただけるかと思います。

是非参考にしてみてくださいね。

プログラミングのスキルを習得するなら

プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。

プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。

→　TechAcademyの「1週間 無料体験」はこちら



目次へ戻る

前の記事

【ExcelVBA】ExcelのマクロからSQLServerの…

次の記事

【ExcelVBA】Excelのマクロから呼び出されたバッチフ…