【環境】Windows 10 Pro 64bit、Excel for Microsoft 365
※「Office 365」は「Microsoft 365」に改名したそうですｗ
それはさておき、色々ありましたが、シート一覧の作成を進めていきます！
今回は、入力用シートである「ファイル一覧」シートを作成します
■シートを一覧する方法
シートの一覧を作成する場合、以下のやり方が多く一般的かと思います
【指定したフォルダにあるExcelファイルの全てのシートを一覧する】
しかし、これでは特定のファイルのシートが見たい場合・・
以下のどちらかの作業が必要になってきますてきます
- フォルダ内のファイルを全て調べなおす
- ファイル１つをフォルダに移動する
そこで今回は、以下のステップで、シート一覧を作成します
- フォルダ内のファイル一覧を作成
- 任意のファイルを選択してシート一覧を作成
ベースは前回作成した「ファイル一覧」のツールです
[blogcard url=”https://zesys.net/pc/excel/file-list/vba-file-list-vol1/”]Excelでファイル一覧を作成する[/blogcard]
■ファイル一覧
ファイル一覧をシート一覧の入力シートとして扱うために改良していきます
・改良点
仕様を考える際に記載した改良店は以下になります
※作業順に並び替えました
- 「一覧」シートの名前を「ファイル一覧」に変更
- 「カレントフォルダ情報」の参照を追加
- 「対象」項目の列を追加
- 「シート一覧取得」機能ボタンを追加
- 「ファイル名」項目に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
②お勧めしたいソースサンプル
列の追加や挿入で他の列に影響を与えない方法としては、以下のサンプルのような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 ' 更新日時
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
修正したのは以下の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
このマクロ関数は「Sheet2 (ファイル一覧)」に記載しています
そのため、サンプルに出てくる「Me」は「Sheet2」を表します
ちなみに、「Sheet3」は「Sheet3 (シート一覧)」です
①カレントフォルダ情報
「カレントフォルダ情報」は「ファイ一覧」シートに追加しましたので、ボタン押下した際に、セル情報を関数に渡して対応します
マクロ関数への引数の渡し方は以下を参照してください！
[blogcard url=”https://zesys.net/pc/excel/file-list/vba-file-list-vol4/”]Excelでファイル一覧を作成する～その４：一覧作成～[/blogcard]
②ファイル一覧情報
「ファイル一覧情報」は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
あえて、「対象」項目をメンバに含めていません
つまり、このコレクションは「対象」のファイルのみをコレクションします
それは何故でしょうか？
理由は、シート一覧を作成する際に、参照するファイル一覧情報のデータ数を軽減することで、処理を高速化するためです！
そして、クラスには自分自身「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
ファイル一覧情報の「対象」行だけを取得して、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
– 6.クリアボタンを追加
「ファイル一覧」シートにクリアボタンを作ると、「ファイル一覧」がクリアされると思ってしまうので、長いですが、「シート一覧クリア」ボタンを作成します
単純にRangeで範囲を指定して、「ClearContents」メソッドで一覧を削除します
■次回
入力シートから取得した情報を基に、実際にシート一覧を取得して、シート一覧を作成するところを次回記載します
今回はここまで！
ではでは
