この記事では、recordsetを使ってExcelの表の列名を取得する方法についてご説明します。
【動画】recordsetを使ってExcelの表の列名を取得する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
recordsetのOpenメソッドに、取得したい列の表を引数に設定して実行することで列名を取得することができます。
表を引数に設定する、というのは、表の最上行かつ左端のセル、そして表の最後列のセルを引数に設定することを指します。
マクロ作成の流れ
Excelファイルの例
今回は次のExcelファイルを用意しました。
生徒データが入力された表があり、今回はこの表の列名を列名を出力する用の表(I列の表)に出力します。
取得する列名はB2からG2のセルの列名(名前、国語、数学、理科、社会、英語)です。
マクロを実行すると列名が取得され、列名を出力する用の表に出力されます。
コードの例
Excelのマクロのコード(例)
Option Explicit
Private Sub getHeader_Click()
Dim adodbCon As ADODB.Connection 'ADODB.Connectionオブジェクトのインスタンス用変数
Dim rs As ADODB.Recordset 'レコードセット用変数
Dim rsFCnt As Integer 'レコードセットのフィールドの数用カウンタ
Dim tblNM As String '表のセル範囲
'シート名を取得する
Const sheetNM As String = "work"
'扱いたい表の先端位置のセル
Const bgnPos As String = "B2"
'扱いたい表の最終列
Const lstPos As String = "G"
'インスタンスの生成
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
'列名を取得する表のセル範囲を取得
tblNM = "[" & sheetNM & "$" & bgnPos & ":" & lstPos & "]"
'Recordsetオブジェクトのインスタンスを生成する
Set rs = New ADODB.Recordset
'Recordsetを開く
rs.Open tblNM, adodbCon, adOpenStatic
'表の列数分だけ処理をループする
For rsFCnt = 0 To rs.Fields.Count - 1
'表の列名をセルに出力する
Worksheets(sheetNM).Range("I" & rsFCnt + 2).Value = rs.Fields.Item(rsFCnt).Name
Next rsFCnt
'後処理
'メモリを解放する
adodbCon.Close
Set adodbCon = Nothing
Set rs = Nothing
End Sub
コードの解説
注目すべきコード①
最初に見て頂きたいのは14行目から17行目です。
'扱いたい表の先端位置のセル
Const bgnPos As String = "B2"
'扱いたい表の最終列
Const lstPos As String = "G"
以上のコードは、今回扱う表のセルの範囲を指定する設定処理です。
表の列名を取得するには表の範囲を指定する必要があるため、表の最上行かつ左端セル、そして表の最終列のセルを指定します。
今回紹介したExcelファイルの例の表で言うと「名前」(セルB2)のセルが表の最上行かつ左端、最後列が列Gになります。
注目すべきコード②
次に見て頂きたいのは20行目から32行目です。
'インスタンスの生成
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接続するためのコードになります。
20行目でADO接続するために必要なインスタンスの生成し、そのインスタンスに対して25行目から27行目でADO接続するための接続情報を設定します。
なお、26行目では自分自身のExcelファイルをフルパスで指定しています。
自分自身のExcelファイルのフルパスはThisWorkbook.FullNameプロパティから取得することができます。
ADO接続するための接続情報を設定したら、30行目のOpenメソッドを実行すると、マクロが自分自身のExcelファイルにADO接続することができます。
注目すべきコード③
次に見て頂きたいのは35行目です。
'列名を取得する表のセル範囲を取得
tblNM = "[" & sheetNM & "$" & bgnPos & ":" & lstPos & "]"
以上のコードは、列を取得する表のセル範囲を取得するコードです。
コードは「シート名と表の範囲のセル」と記述します。
なお、コードだけでは分かりにくいと思うので、マクロ実行時の実際の値をお見せします。
"[work$B2:G]"
注目すべきコード④
次に見て頂きたいのは38行目から49行目です。
'Recordsetオブジェクトのインスタンスを生成する
Set rs = New ADODB.Recordset
'Recordsetを開く
rs.Open tblNM, adodbCon, adOpenStatic
'表の列数分だけ処理をループする
For rsFCnt = 0 To rs.Fields.Count - 1
'表の列名をセルに出力する
Worksheets(sheetNM).Range("I" & rsFCnt + 2).Value = rs.Fields.Item(rsFCnt).Name
Next rsFCnt
以上のコードは、recordsetを利用して表から列名を取得してセルに出力するコードです。
41行目のOpenメソッドに列名を取得したい表のセル範囲を引数に指定して実行することで列名を取得することができます。
取得した列名レコードセット用変数rsのFields.Item.Nameプロパティから取得することができます。(47行目)
Itemにカウンタを引数に指定するとn番目の列の列名が取得できます。
例えば1列目の列名「名前」を取得する場合はrs.Fields.Item(0).Nameになります。
6列目の「英語」を取得する場合はrs.Fields.Item(5).Nameになります。
動作確認
マクロを実行した実行結果は、本記事内の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」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
本記事では、recordsetを使ってExcelの表の列名を取得する方法についてご説明しました。
Excelの表の列名はrecordsetを使って取得することができます。
あまり使う機会がないかもしれませんが、こういった方法もあるということを知って頂けたら幸いです。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
