この記事では、Excelのシートの表からSQLのSelect文を使ってデータを抽出する方法についてご説明します。
【動画】Excelのシートの表からSQLのSelect文を使ってデータを抽出する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
名前と年齢が入力されている表に対して、マクロがSQLのSelect文を使って名前に該当する年齢を検索・抽出してセルに書き出しています。
マクロ作成の流れ
Select文のFrom句には表のセルの範囲を指定します。
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週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。