シート一覧の入力画面

【環境】Windows 10 Pro 64bit、Excel for Microsoft 365

※「Office 365」は「Microsoft 365」に改名したそうですｗ

それはさておき、色々ありましたが、シート一覧の作成を進めていきます！

今回は、入力用シートである「ファイル一覧」シートを作成します

■シートを一覧する方法

シートの一覧を作成する場合、以下のやり方が多く一般的かと思います

【指定したフォルダにあるExcelファイルの全てのシートを一覧する】

しかし、これでは特定のファイルのシートが見たい場合・・

以下のどちらかの作業が必要になってきますてきます

フォルダ内のファイルを全て調べなおす
ファイル１つをフォルダに移動する

そこで今回は、以下のステップで、シート一覧を作成します

フォルダ内のファイル一覧を作成
任意のファイルを選択してシート一覧を作成

ベースは前回作成した「ファイル一覧」のツールです

Studio ZeSys

ファイル一覧の仕様

https://zesys.net/pc/excel/file-list/vba-file-list-vol1

【環境】Windows 10 Pro 64bit、Excel for Office 365Excel VBAを使用して、Excelシート上にカレントフォルダ配下のファイルを一覧する単純なツールをFileSystemObjectを使用して作成し、VBAの勉強をしていきますどんなファイル一覧を作成するのか、仕様を考えていきましょー■ファイル一覧簡単なファイル一覧を作成するのに、仕様書を作るまでもないと考えていますが、作成後のイメージは必要と思いますので、以下に載せておきますシートの構成は以下の２つで、それぞれの画面イメージを載せていますが、シートを分けたのはセル幅を気...

■ファイル一覧

ファイル一覧をシート一覧の入力シートとして扱うために改良していきます

・改良点

仕様を考える際に記載した改良店は以下になります

※作業順に並び替えました

「一覧」シートの名前を「ファイル一覧」に変更
「カレントフォルダ情報」の参照を追加
「対象」項目の列を追加
「シート一覧取得」機能ボタンを追加
「ファイル名」項目にHyperLinkを設定
「シート一覧クリア」機能ボタンを追加

・作業開始

– 1.シート名変更

「ファイル一覧」と「シート一覧」を作成しますので、「一覧」では判りにくいため、シート名を「ファイル一覧」に変更します

– 2.カレントフォルダ情報を追加

これは表紙の情報を参照しているだけです

必要か？と思われるでしょうが、必要です！（たぶん）ｗ

・理由

【１つのシートで完結させること】

ただこれだけですｗ

そしてこの「カレントフォルダ情報」には、以下の２つの役割があります！

一覧を見やすくする
シート一覧取得の入力情報

先に紹介した改良点のところの「改良前」の画像を見てください！

どこのサブフォルダなのか、どこのファイルか、それを知るためには、「表紙」シートを見ないと判らないのです

そのため、シート一覧を作成するにも情報として足りていない

そして、全てのファイルに共通する情報なので、全ての行に表示する必要もない情報です

それらを解消するには！？「カレントフォルダ情報」を追加することです

– 3.「対象」列を追加

これは、どのファイルのシート一覧を作成するのかを選ぶための列です

「○」を付けた行のファイルのシート一覧を作成します

画面を改良するのは簡単、１列（今回は最終列に）追加するだけ！ｗ

問題はVBA側ですが、列を追加する際に、注意していることは、以下です

【他の列に影響を与えない】

当然ですし、単純なことですが、極めて大事なことなのです！！！

以下に２つのサンプルを紹介します（機能は同じです）

①お勧めできないソースサンプル

列番号を数字で直接指定しているため、新しい列を既存列の間に追加(挿入)した場合、他の列に影響が出てしまいます

番号を付けなおさないといけなくなります

これが複数の関数で行われていると、修正漏れも発生するかもしれませんし、テストしないといけない関数が多数出てくるかもしれません

Public Sub MakeListSub(data As Collection, path As String)
    Dim row     As Long
    Dim no      As Long
    Dim fl      As File
    
    ' データ出力の開始行
    row = FILE_START_ROW
    no = 0
    
    With Me
    
        For Each fl In data
            ' No.
            .Cells(row + no, 1).Value = no + 1
            ' サブフォルダ名
            .Cells(row + no, 2).Value = Replace(fl.ParentFolder, path, "")
            ' ファイル名
            .Cells(row + no, 3).Value = fl.name
            ' タイプ
            .Cells(row + no, 4).Value = fl.Type
            ' サイズ
            .Cells(row + no, 5).Value = fl.Size
            ' 作成日時
            .Cells(row + no, 6).Value = Format(fl.DateCreated, "yyyy/mm/dd hh:nn:ss")
            ' 更新日時
            .Cells(row + no, 7).Value = Format(fl.DateLastModified, "yyyy/mm/dd hh:nn:ss")
            
            no = no + 1
        Next
    End With

End Sub

Public Sub MakeListSub(data As Collection, path As String)

Dim row As Long

Dim no As Long

Dim fl As File

' データ出力の開始行

row = FILE_START_ROW

no = 0

With Me

For Each fl In data

' No.

.Cells(row + no, 1).Value = no + 1

' サブフォルダ名

.Cells(row + no, 2).Value = Replace(fl.ParentFolder, path, "")

' ファイル名

.Cells(row + no, 3).Value = fl.name

' タイプ

.Cells(row + no, 4).Value = fl.Type

' サイズ

.Cells(row + no, 5).Value = fl.Size

' 作成日時

.Cells(row + no, 6).Value = Format(fl.DateCreated, "yyyy/mm/dd hh:nn:ss")

' 更新日時

.Cells(row + no, 7).Value = Format(fl.DateLastModified, "yyyy/mm/dd hh:nn:ss")

no = no + 1

End With

End Sub

②お勧めしたいソースサンプル

列の追加や挿入で他の列に影響を与えない方法としては、以下のサンプルのようなEnumを利用した列の指定方法です

番号の付けなおしが必要なくなります

Enumを修正して、必要な列の処理を追加するのみです！

Enum FILE_INFO_E
    E_NO = 1    ' No.
    E_SUB       ' サブフォルダ名
    E_FILE      ' ファイル名
    E_TYPE      ' タイプ
    E_SIZE      ' サイズ
    E_UPDATE    ' 作成日時
    E_NEW       ' 更新日時
End Enum

Public Sub MakeListSub(data As Collection, path As String)
    Dim row     As Long
    Dim no      As Long
    Dim fl      As File
    
    ' データ出力の開始行
    row = FILE_START_ROW
    no = 0
    
    With Me
    
        For Each fl In data
            ' No.
            .Cells(row + no, FILE_INFO_E.E_NO).Value = no + 1
            ' サブフォルダ名
            .Cells(row + no, FILE_INFO_E.E_SUB).Value = Replace(fl.ParentFolder, path, "")
            ' ファイル名
            .Cells(row + no, FILE_INFO_E.E_FILE).Value = fl.name
            ' タイプ
            .Cells(row + no, FILE_INFO_E.E_TYPE).Value = fl.Type
            ' サイズ
            .Cells(row + no, FILE_INFO_E.E_SIZE).Value = fl.Size
            ' 作成日時
            .Cells(row + no, FILE_INFO_E.E_UPDATE).Value = Format(fl.DateCreated, "yyyy/mm/dd hh:nn:ss")
            ' 更新日時
            .Cells(row + no, FILE_INFO_E.E_NEW).Value = Format(fl.DateLastModified, "yyyy/mm/dd hh:nn:ss")
            
            no = no + 1
        Next
    End With

End Sub

Enum FILE_INFO_E

E_NO = 1 ' No.

E_SUB ' サブフォルダ名

E_FILE ' ファイル名

E_TYPE ' タイプ

E_SIZE ' サイズ

E_UPDATE ' 作成日時

E_NEW ' 更新日時

End Enum

Public Sub MakeListSub(data As Collection, path As String)

Dim row As Long

Dim no As Long

Dim fl As File

' データ出力の開始行

row = FILE_START_ROW

no = 0

With Me

For Each fl In data

' No.

.Cells(row + no, FILE_INFO_E.E_NO).Value = no + 1

' サブフォルダ名

.Cells(row + no, FILE_INFO_E.E_SUB).Value = Replace(fl.ParentFolder, path, "")

' ファイル名

.Cells(row + no, FILE_INFO_E.E_FILE).Value = fl.name

' タイプ

.Cells(row + no, FILE_INFO_E.E_TYPE).Value = fl.Type

' サイズ

.Cells(row + no, FILE_INFO_E.E_SIZE).Value = fl.Size

' 作成日時

.Cells(row + no, FILE_INFO_E.E_UPDATE).Value = Format(fl.DateCreated, "yyyy/mm/dd hh:nn:ss")

' 更新日時

.Cells(row + no, FILE_INFO_E.E_NEW).Value = Format(fl.DateLastModified, "yyyy/mm/dd hh:nn:ss")

no = no + 1

End With

End Sub

③「対象」列を追加したサンプル

実際に「対象」列を追加したソースを見てみましょう！

Enum FILE_INFO_E
    E_NO = 1    ' No.
    E_SUB       ' サブフォルダ名
    E_FILE      ' ファイル名
    E_TYPE      ' タイプ
    E_SIZE      ' サイズ
    E_UPDATE    ' 作成日時
    E_NEW       ' 更新日時
    E_TARGET    ' 対象
End Enum

Public Sub MakeListSub(data As Collection, path As String)
    Dim row     As Long
    Dim no      As Long
    Dim fl      As File
    
    ' データ出力の開始行
    row = FILE_START_ROW
    no = 0
    
    With Me
    
        For Each fl In data
            ' No.
            .Cells(row + no, FILE_INFO_E.E_NO).Value = no + 1
            ' サブフォルダ名
            .Cells(row + no, FILE_INFO_E.E_SUB).Value = Replace(fl.ParentFolder, path, "")
            ' ファイル名
            .Cells(row + no, FILE_INFO_E.E_FILE).Value = fl.name
            ' タイプ
            .Cells(row + no, FILE_INFO_E.E_TYPE).Value = fl.Type
            ' サイズ
            .Cells(row + no, FILE_INFO_E.E_SIZE).Value = fl.Size
            ' 作成日時
            .Cells(row + no, FILE_INFO_E.E_UPDATE).Value = Format(fl.DateCreated, "yyyy/mm/dd hh:nn:ss")
            ' 更新日時
            .Cells(row + no, FILE_INFO_E.E_NEW).Value = Format(fl.DateLastModified, "yyyy/mm/dd hh:nn:ss")
            ' 対象
            .Cells(row + no, FILE_INFO_E.E_TARGET).Value = "○"
            
            no = no + 1
        Next
    End With

End Sub

Enum FILE_INFO_E

E_NO = 1 ' No.

E_SUB ' サブフォルダ名

E_FILE ' ファイル名

E_TYPE ' タイプ

E_SIZE ' サイズ

E_UPDATE ' 作成日時

E_NEW ' 更新日時

E_TARGET ' 対象

End Enum

Public Sub MakeListSub(data As Collection, path As String)

Dim row As Long

Dim no As Long

Dim fl As File

' データ出力の開始行

row = FILE_START_ROW

no = 0

With Me

For Each fl In data

' No.

.Cells(row + no, FILE_INFO_E.E_NO).Value = no + 1

' サブフォルダ名

.Cells(row + no, FILE_INFO_E.E_SUB).Value = Replace(fl.ParentFolder, path, "")

' ファイル名

.Cells(row + no, FILE_INFO_E.E_FILE).Value = fl.name

' タイプ

.Cells(row + no, FILE_INFO_E.E_TYPE).Value = fl.Type

' サイズ

.Cells(row + no, FILE_INFO_E.E_SIZE).Value = fl.Size

' 作成日時

.Cells(row + no, FILE_INFO_E.E_UPDATE).Value = Format(fl.DateCreated, "yyyy/mm/dd hh:nn:ss")

' 更新日時

.Cells(row + no, FILE_INFO_E.E_NEW).Value = Format(fl.DateLastModified, "yyyy/mm/dd hh:nn:ss")

' 対象

.Cells(row + no, FILE_INFO_E.E_TARGET).Value = "○"

no = no + 1

End With

End Sub

修正したのは以下の2か所です

Enumに「対象」項目を追加
「対象」に「○」を書き込む処理を追加

今回の変更では、大きな影響はでませんでしたが、既存列の間に複数の列を挿入する変更の場合には、Enumで列を指定しておくのがとめも便利だと言うことに気が付くと思います

是非ともお勧めしたい！（ぃゃ、常識なのか！？ｗ）

– 4.「シート一覧取得」機能ボタンを追加

これは今回の新機能である「シート一覧」を作成するマクロを呼び出すためのボタンです

「シート一覧」を作成するために必要な情報は何かを考えます！

当然ですが、「対象ファイルパス」ですｗ

そのために、ツールにある情報で必要なものは何でしょうか？

カレントフォルダ情報
ファイル一覧情報
出力先シート情報

※出力シートは「その３」で説明します

ここでは、「カレントフォルダ情報」と「ファイル一覧情報」に着目します！

ボタン押下時のマクロ関数は以下のようになります

Sub GetSheetList_Click(cell1 As String, cell2 As String)
    Dim data    As Collection
    Dim path    As String
    Dim name    As String
    
    ' カレントフォルダ情報
    path = GetTextRange(Me, cell1)
    ' 出力先シート情報
    name = GetTextRange(Me, cell2)
    
    ' ファイル一覧情報
    Set data = New Collection
    Call GetListInfo(data)
    
    ' シート一覧を作成
    Call Sheet3.MakeSheetList(data, path)
    
    Set data = Nothing
End Sub

Sub GetSheetList_Click(cell1 As String, cell2 As String)

Dim data As Collection

Dim path As String

Dim name As String

' カレントフォルダ情報

path = GetTextRange(Me, cell1)

' 出力先シート情報

name = GetTextRange(Me, cell2)

' ファイル一覧情報

Set data = New Collection

Call GetListInfo(data)

' シート一覧を作成

Call Sheet3.MakeSheetList(data, path)

Set data = Nothing

End Sub

このマクロ関数は「Sheet2 (ファイル一覧)」に記載しています

そのため、サンプルに出てくる「Me」は「Sheet2」を表します

ちなみに、「Sheet3」は「Sheet3 (シート一覧)」です

①カレントフォルダ情報

「カレントフォルダ情報」は「ファイ一覧」シートに追加しましたので、ボタン押下した際に、セル情報を関数に渡して対応します

マクロ関数への引数の渡し方は以下を参照してください！

Studio ZeSys

ファイル一覧作成

https://zesys.net/pc/excel/file-list/vba-file-list-vol4

【環境】Windows 10 Pro 64bit、Excel for Office 365ExcelマクロのVBAを使用して、ファイル一覧を作成していますさていよいよですが、最後に、一覧作成ボタンと一覧消去ボタンの処理を作成していきますファイル一覧■出力項目その３で実施した通り、ファイル一覧を作成するために、Fileオブジェクトを保存するようにしました～その３：ファイル検索～このFileオブジェクトが持つ情報を基にして、以下の内容を一覧に出力していきますサブフォルダ名ファイル名タイプサイズ作成日時更新日時■やること一覧作成ボタンを押下した際に行うこ...

②ファイル一覧情報

「ファイル一覧情報」はCollection型を作成して、「ファイル一覧」シートから取得して、シート一覧を作成する関数に入力情報として渡します

この時、Collection型の中身は「ファイル一覧」クラスとします

プロジェクトにクラスモジュールを追加して、クラスのオブジェクト名を「clsFileInfo」とします

このクラスのサンプルソースは以下になります

' メンバ
Public m_no         As String   ' No.
Public m_sub        As String   ' サブフォルダ名
Public m_file       As String   ' ファイル名
Public m_type       As String   ' タイプ
Public m_size       As String   ' サイズ
Public m_update     As String   ' 作成日時
Public m_new        As String   ' 更新日時

' クラスを返す
Property Get Self() As clsFileInfo
    Set Self = Me
End Property

' メンバ

Public m_no As String ' No.

Public m_sub As String ' サブフォルダ名

Public m_file As String ' ファイル名

Public m_type As String ' タイプ

Public m_size As String ' サイズ

Public m_update As String ' 作成日時

Public m_new As String ' 更新日時

' クラスを返す

Property Get Self() As clsFileInfo

Set Self = Me

End Property

あえて、「対象」項目をメンバに含めていません

つまり、このコレクションは「対象」のファイルのみをコレクションします

それは何故でしょうか？

理由は、シート一覧を作成する際に、参照するファイル一覧情報のデータ数を軽減することで、処理を高速化するためです！

そして、クラスには自分自身「Me」を返すGetプロパティを作成しています

これの使い方ついては、以下のファイル一覧情報取得のサンプルソースを見てください

Sub GetListInfo(data As Collection)
    Dim row     As Long
    Dim col     As Long
    Dim rows    As Long
    Dim idx     As Long
    Dim buf     As String
    row = FILE_START_ROW                ' データ開始行
    col = FILE_INFO_E.E_NO              ' Noの列
    rows = GetLastRow(Me, col)          ' 行数を取得
    For idx = row To rows
        ' 対象を取得
        buf = GetTextCells(Me, idx, FILE_INFO_E.E_TARGET)
        ' 対象が「○」の行だけ取得する
        If (buf = "○") Then
            ' ファイル情報クラスを作成する
            With New clsFileInfo
                ' No.
                .m_no = GetTextCells(Me, idx, FILE_INFO_E.E_NO)
                ' サブフォルダ名
                .m_sub = GetTextCells(Me, idx, FILE_INFO_E.E_SUB)
                ' ファイル名
                .m_file = GetTextCells(Me, idx, FILE_INFO_E.E_FILE)
                ' タイプ
                .m_type = GetTextCells(Me, idx, FILE_INFO_E.E_TYPE)
                ' サイズ
                .m_size = GetTextCells(Me, idx, FILE_INFO_E.E_SIZE)
                ' 作成日時
                .m_update = GetTextCells(Me, idx, FILE_INFO_E.E_UPDATE)
                ' 更新日時
                .m_new = GetTextCells(Me, idx, FILE_INFO_E.E_NEW)
                ' コレクションに追加する
                data.Add .Self
            End With
        End If
    Next idx
End Sub

Sub GetListInfo(data As Collection)

Dim row As Long

Dim col As Long

Dim rows As Long

Dim idx As Long

Dim buf As String

row = FILE_START_ROW ' データ開始行

col = FILE_INFO_E.E_NO ' Noの列

rows = GetLastRow(Me, col) ' 行数を取得

For idx = row To rows

' 対象を取得

buf = GetTextCells(Me, idx, FILE_INFO_E.E_TARGET)

' 対象が「○」の行だけ取得する

If (buf = "○") Then

' ファイル情報クラスを作成する

With New clsFileInfo

' No.

.m_no = GetTextCells(Me, idx, FILE_INFO_E.E_NO)

' サブフォルダ名

.m_sub = GetTextCells(Me, idx, FILE_INFO_E.E_SUB)

' ファイル名

.m_file = GetTextCells(Me, idx, FILE_INFO_E.E_FILE)

' タイプ

.m_type = GetTextCells(Me, idx, FILE_INFO_E.E_TYPE)

' サイズ

.m_size = GetTextCells(Me, idx, FILE_INFO_E.E_SIZE)

' 作成日時

.m_update = GetTextCells(Me, idx, FILE_INFO_E.E_UPDATE)

' 更新日時

.m_new = GetTextCells(Me, idx, FILE_INFO_E.E_NEW)

' コレクションに追加する

data.Add .Self

End With

End If

Next idx

End Sub

ファイル一覧情報の「対象」行だけを取得して、dataコレクションを作成しています

「タイプ」「サイズ」「作成日時」「更新日時」は要らないんですけどねｗ

「With New clsFileInfo」でクラスを作成（オブジェクト化）しています

そのあと、各メンバを設定（シートから取得）し、最後に作成したGetプロパティ「Self」で作成したクラスをコレクションに追加しています

※「GetLastRow」や「GetTextCells」は不通に書くと行が長くなるので、まとめて関数にしていますので、その辺りはご自身で自由に書いてください

– 5.ファイル名にHyperLinkを付ける

これはファイル一覧を作成する際に行います

「対象」列を追加で記載した③のサンプルに、以下のように「Hyperlinks.Add」メソッドを使って、「ファイル名」にファイルリンクを付ける一行を追加します

Public Sub MakeListSub(data As Collection, path As String)
    Dim row As Long
    Dim no As Long
    Dim fl As File

    ' データ出力の開始行
    row = FILE_START_ROW
    no = 0

    With Me

        For Each fl In data
            ' No.
            .Cells(row + no, FILE_INFO_E.E_NO).Value = no + 1
            ' サブフォルダ名
            .Cells(row + no, FILE_INFO_E.E_SUB).Value = Replace(fl.ParentFolder, path, "")
            ' ファイル名
            .Cells(row + no, FILE_INFO_E.E_FILE).Value = fl.name
            ' HyperLinkを追加
            .Hyperlinks.Add Anchor:=.Cells(row + no, FILE_INFO_E.E_FILE), Address:=fl.path
            ' タイプ
            .Cells(row + no, FILE_INFO_E.E_TYPE).Value = fl.Type
            ' サイズ
            .Cells(row + no, FILE_INFO_E.E_SIZE).Value = fl.Size
            ' 作成日時
            .Cells(row + no, FILE_INFO_E.E_UPDATE).Value = Format(fl.DateCreated, "yyyy/mm/dd hh:nn:ss")
            ' 更新日時
            .Cells(row + no, FILE_INFO_E.E_NEW).Value = Format(fl.DateLastModified, "yyyy/mm/dd hh:nn:ss")
            ' 対象
            .Cells(row + no, FILE_INFO_E.E_TARGET).Value = "○"

            no = no + 1
        Next
    End With

End Sub

Public Sub MakeListSub(data As Collection, path As String)

Dim row As Long

Dim no As Long

Dim fl As File

' データ出力の開始行

row = FILE_START_ROW

no = 0

With Me

For Each fl In data

' No.

.Cells(row + no, FILE_INFO_E.E_NO).Value = no + 1

' サブフォルダ名

.Cells(row + no, FILE_INFO_E.E_SUB).Value = Replace(fl.ParentFolder, path, "")

' ファイル名

.Cells(row + no, FILE_INFO_E.E_FILE).Value = fl.name

' HyperLinkを追加

.Hyperlinks.Add Anchor:=.Cells(row + no, FILE_INFO_E.E_FILE), Address:=fl.path

' タイプ

.Cells(row + no, FILE_INFO_E.E_TYPE).Value = fl.Type

' サイズ

.Cells(row + no, FILE_INFO_E.E_SIZE).Value = fl.Size

' 作成日時

.Cells(row + no, FILE_INFO_E.E_UPDATE).Value = Format(fl.DateCreated, "yyyy/mm/dd hh:nn:ss")

' 更新日時

.Cells(row + no, FILE_INFO_E.E_NEW).Value = Format(fl.DateLastModified, "yyyy/mm/dd hh:nn:ss")

' 対象

.Cells(row + no, FILE_INFO_E.E_TARGET).Value = "○"

no = no + 1

End With

End Sub

– 6.クリアボタンを追加

「ファイル一覧」シートにクリアボタンを作ると、「ファイル一覧」がクリアされると思ってしまうので、長いですが、「シート一覧クリア」ボタンを作成します

単純にRangeで範囲を指定して、「ClearContents」メソッドで一覧を削除します

■次回

入力シートから取得した情報を基に、実際にシート一覧を取得して、シート一覧を作成するところを次回記載します

今回はここまで！

ではでは

カテゴリー:シート一覧

タグ:サンプル

やもす ʕ•͡-•ʔ

のんびり！のほほん！がモットーです！ｗ蕎麦食いたいライブ行きたい暑いの嫌い

■シートを一覧する方法

■ファイル一覧

・改良点

・作業開始

– 1.シート名変更

– 2.カレントフォルダ情報を追加

– 3.「対象」列を追加

①お勧めできないソースサンプル

②お勧めしたいソースサンプル

③「対象」列を追加したサンプル

– 4.「シート一覧取得」機能ボタンを追加

①カレントフォルダ情報

②ファイル一覧情報

– 5.ファイル名にHyperLinkを付ける

– 6.クリアボタンを追加

■次回

関連投稿

VBScriptの覚書

再帰的にファイルを検索

ダイアログでフォルダを指定