本記事では、マクロからSQL Serverのデータを取得する方法についてご説明します。
マクロからSQL Serverのデータを取得する方法
マクロからSQL Serverのデータを取得するには、次の流れの通りにコードを書いていきます。
このインスタンスは、マクロがSQL Serverに接続するために必要です。
このインスタンスは、SQL Serverのデータの参照・取得に必要です。
この設定により、Recordsetオブジェクトを開いたり操作することができるようになります。
テーブルのデータ参照(アクセス)は、RecordsetオブジェクトのOpenメソッドで行います。
参照したデータを取得するには、RecordsetオブジェクトのGetRowsメソッドを使います。
この取得したデータは配列に格納することができます。
コードの例
Excelのマクロのコード(例)
Excelのマクロのコード(例)は次の通りです。
' Dim DBName As String, connDB As String, getVal() As Variant Dim oCon As ADODB.Connection Dim oRS 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;" 'Connectionオブジェクトのインスタンスを生成する Set oCon = New ADODB.Connection 'SQL Serverに接続する oCon.Open connDB 'Recordsetオブジェクトのインスタンスを生成する Set oRS = New ADODB.Recordset '先ほど取得したデータベース接続情報が設定されているoConをActiveConnectionプロパティに設定し、 'ConnectionオブジェクトとRecordsetオブジェクトを関連付ける '⇒Recordsetオブジェクトを開いたり操作するのに必要な設定 oRS.ActiveConnection = oCon 'データを抽出するSQL文をSourceプロパティに設定する oRS.Source = "select * from dbo.tbl_userInfo" '指定したテーブルのデータを参照する(アクセスする) oRS.Open 'レコード有無判定(EOFプロパティがTrueならデータなし、Falseならデータあり) If oRS.EOF = True Then 'データが存在ない場合 Else 'データが存在する場合 'GetRowsメソッドでデータを取得する(取得したデータを配列getValに格納) getVal = oRS.GetRows End If '各終了処理 oRS.Close oCon.Close If Not oRS Is Nothing Then Set oRS = Nothing If Not oCon Is Nothing Then Set oCon = Nothing
コードの解説
最初に見て頂きたいのは33行目です。
RecordsetオブジェクトのOpenメソッドを使い、取得したいテーブルのデータを参照しています。
その参照したデータを取得するには、45行目にあるRecordsetオブジェクトのGetRowsメソッドを使います。
GetRowsメソッドを使うことで、データを取得することができます。(コード(例)では、取得したデータを配列getValに格納しています)
なお、どんなデータを取得したいかはSQL文を記述し、Sourceプロパティに指定します。(30行目)
また、SQL Serverのデータを取得するにはまずSQL Serverに接続しなければいけませんが、マクロからSQL Serverに接続する処理(10行目から19行目)については次の記事で詳しく説明しているので、そちらもあわせてご覧ください。
【ExcelVBA】マクロからSQL Serverに接続するには【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
なぜ必要かというと、先ほどのコードの3行目の「ADODB.Connection」と4行目の「ADODB.Recordset」というオブジェクトが、「msado28.tlb」というファイルを参照するからです。
Dim oCon As ADODB.Connection Dim oRS As ADODB.Recordset
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」と「ADODB.Recordset」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
【動画】マクロからSQL Serverのデータを取得する参考動画
ツイート内の動画では、会員番号が入ったコンボボックスで会員番号を選ぶとSQL Serverに接続してデータを取得し、取得したデータをExcelのシートにある黄色のセルに設定しています。(動画の0:00~0:10あたり)
先日SQL Serverをインストールしたので次にVBAからSQL Serverにアクセスして参照・更新処理ができるか確認してみました😄なんだかおかしなことやってますが💦
参照・追加・更新・削除の確認ができたのでこれでVBAとSQL Serverの連携が必要な処理を盛り込むことができます😁#Excel #vba#SQLServer pic.twitter.com/uqvLIu2fR3
— まさ@Excel、VBA、マクロ(経験年数16年) (@masamasa9785) February 24, 2021
最後に
本記事では、マクロからSQL Serverのデータを取得する方法についてご説明しました。
以下の処理を実装することで、SQL Serverのデータを取得することができます。
- ①SQL Serverへの接続情報取得
- ②Connectionオブジェクトのインスタンスの生成
- ③ ①の情報を参照してSQL Serverに接続
- ④Recordsetオブジェクトのインスタンスの生成
- ⑤Recordsetオブジェクトのインスタンスに、SQL Serverへの接続情報が設定されたConnectionオブジェクトと関連付ける
- ⑥データを抽出するSQL文の設定
- ⑦指定したテーブルのデータを参照
- ⑧参照したデータを取得
手順は多いですが、コードを実装する際は、先ほどお見せしたコードの例を参考にしてみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。