【ExcelVBA】Excelのシートの表からSQLのSelect文を使ってデータを抽出するには

この記事では、Excelのシートの表からSQLのSelect文を使ってデータを抽出する方法についてご説明します。

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

Table of Contents

【動画】Excelのシートの表からSQLのSelect文を使ってデータを抽出する実際の動き

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

名前と年齢が入力されている表に対して、マクロがSQLのSelect文を使って名前に該当する年齢を検索・抽出してセルに書き出しています。

マクロ作成の流れ

STEP.1

ADOを使用して検索・抽出したいデータが存在するExcelファイルに接続する

ADOを使用して検索・抽出したいデータが存在するExcelファイルに接続します。

STEP.2

データを検索・抽出するSelect文を生成する

データを検索・抽出するSelect文を生成します。
Select文のFrom句には表のセルの範囲を指定します。

STEP.3

STEP.2のSelect文をrecordsetのOpenメソッドに指定して実行し、データを検索・抽出する

STEP.2のSelect文をrecordsetのOpenメソッドに指定して実行し、データを検索・抽出します。

STEP.4

抽出したデータをセルに書き出す

抽出したデータをセルに書き出します。

目次へ戻る

Excelファイルの例

今回は次のExcelファイルを用意しました。

名前と年齢が入力された表があり、その表対して名前に一致する年齢を検索・抽出します。

抽出したらセルに年齢を出力します。

目次へ戻る

コードの例

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

Option Explicit

Private Sub btn_getData_Click()

    Dim adodbCon    As ADODB.Connection     'ADODB.Connectionオブジェクトのインスタンス用変数
    Dim rs          As ADODB.Recordset      'レコードセット用変数
    Dim sqlStr      As String               'SQL文用変数
    
    'シート名を取得する
    Const sheetNM As String = "work"
    
    '扱いたい表の先端位置のセル
    Const bgnPos As String = "B11"
    
    '扱いたい表の最終位置のセル
    Const lstPos As String = "C18"
    
    'インスタンスの生成
    Set adodbCon = New ADODB.Connection
    
    With adodbCon
    
        '接続情報の取得(自分自身のExcelファイルに接続する)
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source = " & ThisWorkbook.FullName & _
        ";Extended Properties =Excel 12.0;"
        
        'データソースへの接続を開く
        .Open
        
    End With
    
    'シート上の表に対してデータを取得するSelect文を用意する
    sqlStr = "select 年齢 from"
    sqlStr = sqlStr & " [" & sheetNM & "$" & bgnPos & ":" & lstPos & "]"        'シート名とセルの範囲を指定する
    sqlStr = sqlStr & " where 名前 = '" & Worksheets(sheetNM).Range("nameStr").Value & "'"
        
    'Recordsetオブジェクトのインスタンスを生成する
    Set rs = New ADODB.Recordset
        
    'Recordsetを開く
    rs.Open sqlStr, adodbCon, adOpenStatic
    
    '表のデータから取得した年齢をシートに出力する
    Worksheets(sheetNM).Range("scoreVal").Value = rs!年齢
    
    '後処理
    
    'メモリを解放する
    adodbCon.Close
    Set adodbCon = Nothing
    Set rs = Nothing
    
End Sub

コードの解説

注目すべきコード①

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

    '扱いたい表の先端位置のセル
    Const bgnPos As String = "B11"
    
    '扱いたい表の最終位置のセル
    Const lstPos As String = "C18"

以上のコードは、今回扱う表のセルの範囲を指定する設定処理です。

表はヘッダ部とデータ部があり、そのどちらも範囲に含めるために表の最上行かつ左端セル、そして表の最後行かつ最後列のセルを指定します。

今回紹介したExcelファイルの例の表で言うと「名前」(セルB11)のセルが表の最上行かつ左端、数値42が入っているセル(セルC18)が最後行と最後列になります。

注目すべきコード②

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

    'インスタンスの生成
    Set adodbCon = New ADODB.Connection
    
    With adodbCon
    
        '接続情報の取得(自分自身のExcelファイルに接続する)
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source = " & ThisWorkbook.FullName & _
        ";Extended Properties =Excel 12.0;"
        
        'データソースへの接続を開く
        .Open
        
    End With

以上のコードは、マクロが自分自身のExcelファイルにADO接続するためのコードになります。

19行目でADO接続するために必要なインスタンスを生成し、そのインスタンスに対してADO接続するために24行目から26行目で接続情報を設定します。

なお、25行目では自分自身のExcelファイルをフルパスで指定しています。

自分自身のExcelファイルのフルパスはThisWorkbook.FullNameプロパティから取得することができます。

ADO接続するための接続情報を設定したら、29行目のOpenメソッドを実行すると、マクロが自分自身のExcelファイルにADO接続することができます。

注目すべきコード③

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

    'シート上の表に対してデータを取得するSelect文を用意する
    sqlStr = "select 年齢 from"
    sqlStr = sqlStr & " [" & sheetNM & "$" & bgnPos & ":" & lstPos & "]"        'シート名とセルの範囲を指定する
    sqlStr = sqlStr & " where 名前 = '" & Worksheets(sheetNM).Range("nameStr").Value & "'"

以上のコードは、表から対象のデータを検索・抽出するSelect文を組み立てているコードです。

上記のSelect文で気を付ける点はfrom句の設定です。

表に対してデータを検索・抽出する場合は、fromに対して「シート名と表の範囲のセル」を指定する必要があります。

なお、コードだけではSelect文が分かりにくいと思うので、実際に実行するSQLを以下にお見せします。

select
    年齢
from
    [work$B11:C18]
where
    名前 = '鈴木健'

→名前が「鈴木健」の年齢を検索・抽出する

注目すべきコード④

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

    'Recordsetオブジェクトのインスタンスを生成する
    Set rs = New ADODB.Recordset
        
    'Recordsetを開く
    rs.Open sqlStr, adodbCon, adOpenStatic
    
    '表のデータから取得した年齢をシートに出力する
    Worksheets(sheetNM).Range("scoreVal").Value = rs!年齢

以上のコードは、recordsetを利用して表からselect文を参照してデータを検索・抽出、そしてそのデータをセルに出力するコードです。

Openメソッドにselect文を引数に指定して実行することでデータを検索・抽出することができます。

抽出したデータはレコードセット用変数rsから取得することができます。

どうすればいいのかというと、rsと列名を「!」で結合することで取得することができます。

今回は「年齢」という列名のデータが欲しいので、45行目のように「rs!年齢」とコードで記述しています。

その取得した「年齢」の値をセル「scoreVal」に出力しています。

目次へ戻る

動作確認

マクロを実行した実行結果は、本記事内のExcelファイルの例の内容をご覧ください。

目次へ戻る

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

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

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

Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)

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

    Dim adodbCon    As ADODB.Connection     'ADODB.Connectionオブジェクトのインスタンス用変数
    Dim rs          As ADODB.Recordset      'レコードセット用変数

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

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

目次へ戻る

最後に

本記事では、Excelのシートの表からSQLのSelect文を使ってデータを抽出する方法についてご説明しました。

Excelで使われている表に対してSQLのSelect文を使ってデータを検索・抽出することができます。

表の中のデータを検索する方法はいくつかありますが、マクロを使ってSQLのSelect文を実行する方法でも可能なので、機会があれば本記事の内容を参考にしてみてくださいね。

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

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

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

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

目次へ戻る