2020/12/28
こんにちは、キャンベルです。
最近、近所のおじさんが庭の土いじりをしているのを部屋の窓から眺めるのが日課です。
今回は、VBAでサブフォルダ含むファイル名の取得をおこないます。
VBAではFileSystemObjectを使用することで、フォルダやファイルを操作することができます。
「ファイル名一覧を表示して更新履歴を管理したい」や「先月との差分を確認したい」などいろいろ使い道はあるかと思います。
とりあえず形にしたい。コードの意味を理解するのはその後。という方のために、ペロッと貼ってゴニョゴニョっとやってすぐ使えるコードをご紹介します。
エディタ起動~コード記述
1. Visual Basicエディタの起動
まずはVBAのエディタを起動します。
ショートカットだと、Alt+F11キーで起動します。
メニューからだと、[開発]→[Visual Basic]で同じ動作になります。
[開発]メニューが表示されていない方は、まず表示させることから始めてください。
(ここでは触れませんので、分からない方は調べてくださいね)
※メニュー[ツール]→[参照設定]で、「Microsoft Scripting Runtime」にチェックが入っていない場合はチェックしておきましょう。
2. モジュール作成
エディタが起動したら、メニューより[挿入]→[標準モジュール]をクリックします。
すると、左側のプロジェクトエクスプローラに「Module1」というファイルが追加されるので、クリックします。
3. コード記述
白い画面に以下をコピペしてください。
Option Explicit
Sub setFileList(searchPath)
Dim startCell As Range
Dim maxRow As Long
Dim maxCol As Long
Set startCell = Cells(5, 2) 'このセルから出力し始める
startCell.Select
'シートをいったんクリア
maxRow = startCell.SpecialCells(xlLastCell).Row
maxCol = startCell.SpecialCells(xlLastCell).Column
Range(startCell, Cells(maxRow, maxCol)).ClearContents
Call getFileList(searchPath)
startCell.Select
End Sub
Sub getFileList(searchPath)
Dim FSO As New FileSystemObject
Dim objFiles As File
Dim objFolders As Folder
Dim separateNum As Long
'サブフォルダ取得
For Each objFolders In FSO.GetFolder(searchPath).SubFolders
Call getFileList(objFolders.Path)
Next
'ファイル名の取得
For Each objFiles In FSO.GetFolder(searchPath).Files
separateNum = InStrRev(objFiles.Path, "\")
'セルにパスとファイル名を書き込む
ActiveCell.Value = Left(objFiles.Path, separateNum - 1)
ActiveCell.Offset(0, 1).Value = Right(objFiles.Path, Len(objFiles.Path) - separateNum)
ActiveCell.Offset(0, 2).Value = FileDateTime(objFiles)
ActiveCell.Offset(0, 3).Value = Format((FileLen(objFiles) / 1024), "#.0")
ActiveCell.Offset(1, 0).Select
Next
End Sub
シートにボタンを設置して呼び出そう
ここまでで、ファイルリスト一覧を取得する処理が完成しました。
次に、いつでも簡単に実行できるように、シートにボタンを設置してみましょう。
4. ボタン設置
Excelシートに戻って、対象フォルダを記入する枠と、実行ボタンを作りましょう。
私はB2セルをフォルダパス入力欄にして、その横にボタンを設置しました。
(ボタンはメニュー[開発]→[挿入]より、ActiveXのコマンドボタンを設置)
5. ボタンクリック時の動作
メニュー[開発]→[デザインモード]をONにした状態で、作ったボタンをダブルクリックします。
するとふたたびエディタが表示され「Private Sub CommandButton1_Click()」と書かれていると思います。
ここでさっきコピペしたプロシージャを呼びます。
Private Sub CommandButton1_Click()
Call setFileList(Cells(2, 2)) 'フォルダパスを入力するセル
End Sub
6. 完成!
B2セルに一覧を取得したいフォルダ名を入力して、ボタンをクリックしてみてください。
以上です。
あとは、出力項目を増やすも良し、配列を使って速度アップするも良し、処理が終わるまで画面描画を止めても良し、いい感じにブラッシュアップしてくださいね!