この記事では、Excelファイルのシート名を取得する方法についてご説明します。
【動画】Excelファイルのシート名を取得する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
Excelファイルに接続する情報を元にExcelファイルに接続したらOpenSchemaメソッドを実行してExcelのシート名を取得しています。
取得したシート名はリストボックスに出力しています。
コード作成の流れ
ExcelファイルとAccessのデータベースファイルの例
今回は次のExcelファイルとAccessのデータベースファイルを用意しました。
Excelファイル
Excelファイルには「1月」「2月」「3月」という名前の3つのシートがあります。
Accessのデータベースファイル
Accessのデータベースファイルにはリストボックスと「実行」ボタンが配置されています。
「実行」ボタンをクリックすると、Excekファイルにあるシート名が取得されてリストボックスに出力されます。
先ほどお見せしたExcelファイルの「1月」「2月」「3月」という名前の3つのシート名がリストボックスに出力されました。
コードの例
Option Compare Database Option Explicit Private Sub btn_exec_Click() Dim excelFile As String 'Excelファイル Dim cn As Object 'Connection用変数 Dim rs As Object 'レコードセット用変数 'Excelファイルのフルパスを取得する excelFile = Application.CurrentProject.Path & "\0005.xlsm" 'Connectionオブジェクトのインスタンスを生成する Set cn = CreateObject("ADODB.Connection") 'Excelファイルへの接続情報の取得 cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & excelFile & ";" & _ "Extended Properties=""Excel 12.0 Xml;HDR=YES"";" 'Excelファイルに接続する cn.Open 'OpenSchemaメソッドを実行してExcelファイルのテーブル情報を取得しeRsに格納する Set rs = cn.OpenSchema(20) 'リストボックスの名前を取得する Const controlName As String = "ltb1" 'リストボックスをクリアする Me.Controls(controlName).RowSource = "" 'Excelファイルのシートの数分処理を繰り返すループ Do Until rs.EOF If rs.Fields("TABLE_TYPE").Value = "TABLE" Then 'リストボックスに値を追加する Me.Controls(controlName).AddItem Replace(rs.Fields("TABLE_NAME").Value, "$", "") End If 'Recordsetのレコードのカーソルを次に移動する rs.MoveNext Loop 'Recordsetを閉じる rs.Close '後処理 Set cn = Nothing Set rs = Nothing End Sub
注目すべきコード①
最初に見て頂きたいのは14行目から25行目です。
'Connectionオブジェクトのインスタンスを生成する Set cn = CreateObject("ADODB.Connection") 'Excelファイルへの接続情報の取得 cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & excelFile & ";" & _ "Extended Properties=""Excel 12.0 Xml;HDR=YES"";" 'Excelファイルに接続する cn.Open 'OpenSchemaメソッドを実行してExcelファイルのテーブル情報を取得しeRsに格納する Set eRs = cn.OpenSchema(20)
コードの説明
以上のコードは、Connectionオブジェクトのインスタンスを生成し、Excelファイルへの接続後、OpenSchemaメソッドを実行してExcelファイルのテーブル情報を取得する処理のコードです。
OpenSchemaを実行することで、Excelファイルのテーブル情報を取得することができ、その情報の中にExcelファイルのシート名が含まれています。
注目すべきコード②
次に見て頂きたいのは28行目から31行目です。
'リストボックスの名前を取得する Const controlName As String = "ltb1" 'リストボックスをクリアする Me.Controls(controlName).RowSource = ""
コードの説明
以上のコードは、Excelのシート名を表示させるリストボックスの名前を取得し、そのリストボックスをクリアする処理のコードです。
リストボックスをクリアするには、RowSourceプロパティにブランクを設定します。
注目すべきコード③
次に見て頂きたいのは34行目から46行目です。
'Excelファイルのシートの数分処理を繰り返すループ Do Until rs.EOF If rs.Fields("TABLE_TYPE").Value = "TABLE" Then 'リストボックスに値を追加する Me.Controls(controlName).AddItem Replace(rs.Fields("TABLE_NAME").Value, "$", "") End If 'Recordsetのレコードのカーソルを次に移動する rs.MoveNext Loop
コードの説明
以上のコードは、Excelファイルのシート名をリストボックスに出力する処理のコードです。
コードの詳細
33行目のコードは、Excelファイルのシートの数分処理を繰り返すループで、36行目でeRs.Fieldsの「TABLE_TYPE」が「TABLE」かどうかを判定しています。
「TABLE」の場合はシートなので、39行目でリストボックスに出力します。
Excelファイルのシート名をリストボックスに出力するには、AddItemメソッドを使います。
AddItemメソッドの引数にExcelファイルのシート名を指定して実行することで、リストボックスにExcelファイルのシート名を出力することができます。
44行目のコードでは、recordsetに格納された次のデータを参照するためMoveNextメソッドを実行しています。
例えば1つ目のデータを参照した後に2つ目のデータを参照するにはこのMoveNextを実行しないといけません。
MoveNextを実行しないと永久に1つ目のデータを参照し続けます。
なので、2つ目、3つ目・・・と順々にデータを参照するためには忘れずにMoveNextメソッドを実行します。
動作確認
「ExcelファイルとAccessのデータベースファイルの例」をご覧ください。
最後に
この記事では、Excelファイルのシート名を取得する方法についてご説明しました。
Excelファイルのシート名を取得したいときは本記事を参考にしてみてくださいね。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。