Excel VBA 同じフォルダ内のファイル名を取得しファイル名でリスト入力できるようにする

集計や帳票など、今操作しているブックと同じフォルダ内にあるファイルを取得して、セルにリスト入力できるようにするサンプルを記録します。

下図は、Excel2016です。

ExcelVBAリスト

 

< スポンサーリンク >

 

サンプル

リスト入力させたいシートがアクティブになった時に、ファイル名を取得し、リスト入力できるように設定します。

[開発]タブ > [コードの表示]をクリックします。

ExcelVBAリスト

 

左側のオブジェクトボックスを「WorkSheet」を選択し、右側のプロシージャボックスに「Activate」を選択します。

ExcelVBAリスト

 

「Private Sub Worksheet_SelectionChange(ByVal Target As Range) ・・・ End Sub」は今回必要ないので削除します。

ExcelVBAリスト

 

「Private Sub Worksheet_Activate()」と「End Sub」の間に下記のコードを書き込みます。

※リスト入力用のセルはC2に設定

ExcelVBAリスト

    'このブック用
    Dim wbThis As Workbook
    'このシート用
    Dim wsThis As Worksheet
    'このブックのフォルダ
    Dim myPath As String
    'リスト入力用
    Dim ListStr As String
    '一時的ファイル名用
    Dim buf As String

    '変数にこのブックをセット
    Set wbThis = ThisWorkbook
    '変数にこのシートをセット
    Set wsThis = wbThis.Worksheets("Sheet1")

    'このブックのフォルダ名を変数に代入
    myPath = wbThis.Path

    'リスト入力用変数に空文字を代入
    ListStr = ""

    '一時的ファイル名変数にこのブックと同じフォルダのファイル名を代入
    buf = Dir(myPath & "\*")

    'フォルダ内のファイルがある限り続けます
    Do While buf <> ""
        'このブック名と違う場合リスト入力用変数に代入
        If wbThis.Name <> buf Then
            'リスト入力用変数が空ではない場合、「,」を最後に追加
            If ListStr <> "" Then
                ListStr = ListStr & ","
            End If
            'リスト入力変数にファイル名を追加
            ListStr = ListStr & buf
        End If
        '一時的ファイル名変数に次のファイル名を代入
        buf = Dir()
    Loop
    'このシートのC2のセルにファイル名をリスト入力に設定
    With wsThis.Range("C2").Validation
        Delete
        .Add Type:=xlValidateList, Formula1:=ListStr
    End With

ExcelVBAリスト

 

このシートがアクティブになる度にリストの値が更新されます。

ExcelVBAリスト

 

< スポンサーリンク >



サブコンテンツ

このページの先頭へ