【ExcelVBA】マクロからSQL Serverのデータを取得するには

本記事では、マクロからSQL Serverのデータを取得する方法についてご説明します。

マクロからSQL Serverのデータを取得する方法

マクロからSQL Serverのデータを取得するには、次の流れの通りにコードを書いていきます。

STEP.1
SQL Serverへの接続情報取得
SQL Serverに接続するための接続情報を取得します。
STEP.2
Connectionオブジェクトのインスタンスの生成
Connectionオブジェクトのインスタンスを生成します。
このインスタンスは、マクロがSQL Serverに接続するために必要です。
STEP.3
SQL Serverに接続
接続情報が取得出来たら、その接続情報をもとにSQL Serverに接続します。
STEP.4
Recordsetオブジェクトのインスタンスの生成
Recordsetオブジェクトのインスタンスを生成します。
このインスタンスは、SQL Serverのデータの参照・取得に必要です。
STEP.5
Recordsetオブジェクトに接続先情報を設定
RecordsetオブジェクトのActiveConnectionプロパティに、STEP.1で取得した接続先情報が設定されているConnectionオブジェクトのインスタンスを設定します。
この設定により、Recordsetオブジェクトを開いたり操作することができるようになります。
STEP.6
データを抽出するSQL文の設定
データを抽出するSQL文をRecordsetオブジェクトのSourceプロパティに設定します。
STEP.7
指定したテーブルのデータを参照
STEP.6のSQL文で指定したテーブルのデータを参照(アクセス)します。
テーブルのデータ参照(アクセス)は、RecordsetオブジェクトのOpenメソッドで行います。
STEP.8
参照したデータを取得
参照したデータを取得します。
参照したデータを取得するには、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」ボタンをクリックします。

  1. 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のデータを取得する方法についてご説明しました。

以下の処理を実装することで、SQL Serverのデータを取得することができます。

  • ①SQL Serverへの接続情報取得
  • ②Connectionオブジェクトのインスタンスの生成
  • ③ ①の情報を参照してSQL Serverに接続
  • ④Recordsetオブジェクトのインスタンスの生成
  • ⑤Recordsetオブジェクトのインスタンスに、SQL Serverへの接続情報が設定されたConnectionオブジェクトと関連付ける
  • ⑥データを抽出するSQL文の設定
  • ⑦指定したテーブルのデータを参照
  • ⑧参照したデータを取得

手順は多いですが、コードを実装する際は、先ほどお見せしたコードの例を参考にしてみてくださいね。

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

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

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

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