この記事では、SQLのSELECT文を使ってExcelのシートにある表のデータ件数を取得する方法についてご説明します。
【動画】SQLのSELECT文を使ってExcelのシートにある表のデータ件数を取得する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
表のデータ件数を取得するSELECT文を用意して実行すると、rs.Fields(0).Valueに表のデータ件数が取得されています。
ちなみに、表のデータ件数取得は、recordsetを使用しています。
【前提】表以外のセルには値が入力されていないこと
今回のマクロの前提として、表以外のセルには値が入力されていないことが前提です。
表のデータ件数を取得するのに、表以外のセルに値が入力されていると、そのセルも件数に含んでしまうので表のデータ件数が正しく取得することができません。
例えばデータ件数が5件なのに、さらに2つ下の行のセルに値が入っていると、マクロはデータ件数が7件と結果を返してしまいます。
なので、表以外に値が存在している場合は、そのセルの値を削除しておくようにお願いします。
マクロ作成の流れ
SELECT文を実行するとデータ件数が取得されます。
コードの例
Option Explicit
Private Sub btn_exec_Click()
Dim ws As Worksheet
Dim adodbCon As ADODB.Connection 'ADODB.Connectionオブジェクトのインスタンス用変数
Dim rs As ADODB.Recordset 'レコードセット用変数
Dim sqlStr As String 'SQL文用変数
Dim sheetNM As String 'シート名
'本マクロのブックのシート名を取得する
Set ws = Worksheets("top")
'インスタンスの生成
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 count(*) from"
sqlStr = sqlStr & " [" & ws.Range("sheetNM").Value & "$]"
'Recordsetオブジェクトのインスタンスを生成する
Set rs = New ADODB.Recordset
'Recordsetを開く
rs.Open sqlStr, adodbCon, adOpenStatic
'表のデータから取得した年齢をシートに出力する
ws.Range("dataCount").Value = rs.Fields(0).Value
'後処理
adodbCon.Close
Set adodbCon = Nothing
Set rs = Nothing
End Sub
注目すべきコード①
最初に見て頂きたいのは15行目から27行目です。
'インスタンスの生成
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ファイルに接続するための接続情報を取得して接続するコードです。
コードの詳細
15行目のコードでは、Connectionインスタンスを生成します。
このインスタンスがないとマクロが自分自身のExcelファイルに接続することができないので必ず生成しておきます。
20行目から22行目で接続情報を取得し、25行目でOpenメソッドを実行してマクロが自分自身のExcelファイルに接続します。
注目すべきコード②
次に見て頂きたいのは30行目から31行目です。
'シート上の表に対してデータ件数を取得するSelect文を用意する
sqlStr = "select count(*) from"
sqlStr = sqlStr & " [" & ws.Range("sheetNM").Value & "$]"
コードの説明
以上のコードは、シートの表のデータ件数を取得するSELECT文を用意し実行する処理のコードです。
select count(*)でデータ件数を取得するよう記述し、From句には表のデータ件数を取得するシート名を指定します。
ただし、シート名の後ろに「$」を付けないと、SELECT文実行時にエラーになるので忘れないようにします。
また、そのシート名+「$」を、［］で囲みます。
例えばシート名が「3月」の場合は、[3月$]と記述します。
なお、コードだけではSelect文が分かりにくいと思うので、実際に実行するSQLをサンプルで以下にお見せします。
select
count(*)
from
[3月$]
注目すべきコード③
次に見て頂きたいのは34行目から40行目です。
'Recordsetオブジェクトのインスタンスを生成する
Set rs = New ADODB.Recordset
'Recordsetを開く
rs.Open sqlStr, adodbCon, adOpenStatic
'表のデータから取得した年齢をシートに出力する
ws.Range("dataCount").Value = rs.Fields(0).Value
コードの説明
以上のコードは、Recordsetオブジェクトのインスタンスを生成してシートの表のデータ件数を取得し、その件数をシートに出力する処理のコードです。
コードの詳細
34行目のコードでは、Recordsetオブジェクトのインスタンスを生成します。
37行目のコードでは、Openメソッドの引数に「注目すべきコード②」で用意したSELECT文を指定して実行することでシートのデータ件数を取得することができます。
40行目のコードでは、recordsetの1つ目のフィールドのValueプロパティから、取得したデータ件数を「dataCount」のセルに出力しています。
動作確認
マクロ実行前
今回は以下のExcelファイルを用意しました。
シート名には3月と入力してマクロを実行します。
3月のシートには下の通りにデータが存在しています。データ件数は9件存在しています。
マクロ実行後
マクロを実行すると、下の通りにデータ件数がセルに「9」の値が出力されました。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
なぜ必要かというと、Excelのマクロのコードの6行目の「ADODB.Connection」と7行目の「ADODB.Recordset」というオブジェクトが「msado28.tlb」というファイルを参照するからです。
Dim adodbCon As ADODB.Connection 'ADODB.Connectionオブジェクトのインスタンス用変数
Dim rs As ADODB.Recordset 'レコードセット用変数
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」「ADODB.Recordset」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
本記事では、SQLのSELECT文を使ってExcelのシートにある表のデータ件数を取得する方法についてご説明しました。
シートの表のデータが何件あるのか確認したい場合は本記事を参考にしてみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
