【AccessVBA】Excelファイルのシート名を取得するには

この記事では、Excelファイルのシート名を取得する方法についてご説明します。

【動画】Excelファイルのシート名を取得する実際の動き

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


Excelファイルに接続する情報を元にExcelファイルに接続したらOpenSchemaメソッドを実行してExcelのシート名を取得しています。

取得したシート名はリストボックスに出力しています。

コード作成の流れ

STEP.1
Excelファイルに接続する情報を元にExcelファイルに接続する
Excelファイルに接続する情報を元にExcelファイルに接続します。
STEP.2
OpenSchemaメソッドを実行してExcelファイルの各情報を取得する
OpenSchemaメソッドを実行してExcelファイルの各情報を取得します。
STEP.3
するSTEP.3で取得したExcelファイルの各情報から、シート名を取得
STEP.3で取得した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」かどうかを判定しています。

eRs.Fieldsの各プロパティ

「TABLE」の場合はシートなので、39行目でリストボックスに出力します。

Excelファイルのシート名をリストボックスに出力するには、AddItemメソッドを使います。

AddItemメソッドの引数にExcelファイルのシート名を指定して実行することで、リストボックスにExcelファイルのシート名を出力することができます。

44行目のコードでは、recordsetに格納された次のデータを参照するためMoveNextメソッドを実行しています。

例えば1つ目のデータを参照した後に2つ目のデータを参照するにはこのMoveNextを実行しないといけません。

MoveNextを実行しないと永久に1つ目のデータを参照し続けます。

なので、2つ目、3つ目・・・と順々にデータを参照するためには忘れずにMoveNextメソッドを実行します。

動作確認

ExcelファイルとAccessのデータベースファイルの例」をご覧ください。

最後に

この記事では、Excelファイルのシート名を取得する方法についてご説明しました。

Excelファイルのシート名を取得したいときは本記事を参考にしてみてくださいね。

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

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

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

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