【ExcelVBA】Recordsetで取得したSQL Serverのテーブルデータをシート上に設置されたListboxに表示させる方法

この記事では、Recordsetで取得したSQL Serverのテーブルデータをシート上に設置されたListboxに表示させる方法についてご説明します。

すぐにソースコードを見たい場合はこちらをクリック

Table of Contents

【動画】Recordsetで取得したSQL Serverのテーブルデータをシート上に設置されたListboxに表示させる実際の動き

本題に入る前に、まずは次の動画をご覧ください。

SQL ServerのテーブルデータをRecordsetで取得し、シート上に設置されたListboxに表示させています。

マクロ作成の流れ

STEP.1

SQL Serverのデータベースに接続する

SQL Serverのデータベースに接続に接続します。

STEP.2

Recordsetオブジェクトのインスタンスを生成する

Recordsetオブジェクトのインスタンスを生成します。

STEP.3

データベースからテーブルデータを取得する

データベースファイルからテーブルデータを取得します。

STEP.4

STEP.3で取得したデータをListboxに表示させる

STEP.3で取得したデータをListboxに表示させます。

目次へ戻る

コードの例

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

Option Explicit

Sub test1()

    Dim connDB              As String               'データベース接続情報
    Dim objConnection       As ADODB.Connection     'ADODB.Connectionオブジェクトのインスタンス用変数
    Dim oRS                 As ADODB.Recordset      'レコードセット用変数
    Dim sqlStr              As String               'SQL文
        
    Const DBName            As String = "testDB1"    'データベース名
    
    'データベース接続情報を取得
    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
    
    'Recordsetオブジェクトのインスタンスを生成する
    Set oRS = New ADODB.Recordset
    
    '先ほど取得したデータベース接続情報が設定されているobjConnectionをActiveConnectionプロパティに設定し、
    'ConnectionオブジェクトとRecordsetオブジェクトを関連付ける
    '⇒Recordsetオブジェクトを開いたり操作するのに必要な設定
    oRS.ActiveConnection = objConnection
    
    'SQL Serverからテーブルデータを取得するSQL文を作成
    sqlStr = "select * from dbo.PrefecturesList "
    sqlStr = sqlStr & " ORDER BY 1 ASC"
    
    'データを抽出するSQL文をSourceプロパティに設定する
    oRS.Source = sqlStr
 
    '指定したテーブルのデータを参照する(アクセスする)
    oRS.Open
 
    With ListBox1
        
        'データの数分列を表示する
        .ColumnCount = 2
        
        'SQL Serverのテーブルから取得したデータをListboxに表示させる
        .Column = oRS.GetRows
        
    End With
    
    'Connectionを閉じる
    oRS.Close
    objConnection.Close
    
    Set oRS = Nothing
    Set objConnection = Nothing

End Sub

注目すべきコード①

最初に見て頂きたいのは13行目から21行目です。

    'データベース接続情報を取得
    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

13行目から16行目でSQL Serverのデータベースに接続するのに必要な情報を取得します。

19行目でConnectionオブジェクトを生成したら、21行目のOpenメソッドに先ほど取得したSQL Serverのデータベースに接続するのに必要な情報を引数に指定してOpenメソッドを実行します。

Openメソッドの実行が成功すると、ExcelのマクロがSQL Serverのデータベースに接続されます。

注目すべきコード②

次に見て頂きたいのは24行目です。

    'Recordsetオブジェクトのインスタンスを生成する
    Set oRS = New ADODB.Recordset

24行目でRecordsetオブジェクトのインスタンスを生成します。

インスタンスが生成されると、Recordsetを扱うことができます。

注目すべきコード③

次に見て頂きたいのは29行目です。

    '先ほど取得したデータベース接続情報が設定されているobjConnectionをActiveConnectionプロパティに設定し、
    'ConnectionオブジェクトとRecordsetオブジェクトを関連付ける
    '⇒Recordsetオブジェクトを開いたり操作するのに必要な設定
    oRS.ActiveConnection = objConnection

29行目では、先ほど取得したデータベース接続情報が設定されているobjConnectionをActiveConnectionプロパティに設定し、ConnectionオブジェクトとRecordsetオブジェクトを関連付けます。

以上のコードを実行することで、SQL Serverのテーブルデータを取得するSelect文(※このあとの「注目すべきコード④」で説明します)を実行してテーブルデータを取得することができます。

注目すべきコード⑤

次に見て頂きたいのは32行目から39行目です。

    'SQL Serverからテーブルデータを取得するSQL文を作成
    sqlStr = "select * from dbo.PrefecturesList "
    sqlStr = sqlStr & " ORDER BY 1 ASC"
    
    'データを抽出するSQL文をSourceプロパティに設定する
    oRS.Source = sqlStr
 
    '指定したテーブルのデータを参照する(アクセスする)
    oRS.Open

32行目と33行目では、SQL Serverのテーブルデータを取得するSQL文を用意します。

36行目では、先ほどのSQL文をSourceプロパティに設定します。

39行目ではOpenメソッドを実行し、SQL Serverからテーブルデータを取得します。

注目すべきコード④

次に見て頂きたいのは41行目から49行目です。

    With ListBox1
        
        'データの数分列を表示する
        .ColumnCount = 2
        
        'SQL Serverのテーブルから取得したデータをListboxに表示させる
        .Column = oRS.GetRows
        
    End With

上記は、Listboxに関する設定を行っているコードです。

47行目では、GetRowsメソッドを実行してRecordsetで取得したSQL ServerのテーブルデータをListboxのColumnプロパティに設定することで、シート上に設置されたListboxにSQL Serverのテーブルデータを表示させています。

目次へ戻る

動作確認

今回の動作確認用に、SQL Serverのデータベースファイルに都道府県の名称が保存されているテーブル「PrefecturesList」を用意しました。

この都道府県の名称を、Excelのシート上に設置されたListboxに表示させます。

マクロ実行前

下のExcelは、マクロを実行する前の状態です。

マクロ実行後

マクロを実行すると、下の画像のように都道府県の名称がExcelのシート上に設置されたListboxに表示されています。

目次へ戻る

【注意】参照設定が必要です

一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。

参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。

Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)

なぜ必要かというと、Excelマクロのコードの6行目「ADODB.Connection」と7行目の「ADODB.Recordset」というオブジェクトが「msado28.tlb」というファイルを参照するからです。

    Dim adoCON      As New ADODB.Connection 'Connection用変数
    Dim adoRS       As ADODB.Recordset      'レコードセット用変数

この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。

ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」と「ADODB.Recordset」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。

目次へ戻る

最後に

本記事では、Recordsetで取得したSQL Serverのテーブルデータをシート上に設置されたListboxに表示させる方法についてご説明しました。

SQL Serverのテーブルデータをシート上に設置されたListboxに表示させたい場合、Recordsetを使うと簡単に表示させることができます。是非参考にしてみてくださいね。

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

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

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

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

目次へ戻る