この記事では、SQL Serverのストアドプロシージャで抽出したデータをExcelのマクロが受け取る方法についてご説明します。
【動画】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のマクロが受け取るには、次の流れの通りにコードを書いていきます。
なので、最初にDBに接続するための接続情報を取得します。
抽出結果の取得は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週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。