フォルダーのとExcelの連動ってできますか？

質問

パソコン内HDDのとある場所のフォルダの一覧を手打ちで入力したExcelデータがあります。Excelで表示しているフォルダーの階層は4階層あります。

A列に4階層目のフォルダー名、B列に3階層目のフォルダー名、C列に2階層目のフォルダー名、D列に1階層目のフォルダー名を既に入力してあるとします。ここに入力されているフォルダー名は、これから生成する予定のフォルダーと既にHDDに生成されているフォルダー名が混在しています。

これからE列に4階層目のフォルダーの有無、F列に4階層目のフォルダー内のファイル数を表示させたいです。

エクスプローラーで、4階層目に新しいフォルダーを生成すると、E列が自動的に「有」になり、そのフォルダーにファイルを追加すると、自動的にF列でカウントされる、という状態にしたいのです。

-------------------------------------------------------------------------------
具体的に例を示します。

最終的には以下のような表をExcelで作ります。設定は架空です。

A　　　　　　　B　　　　　　　　　　　C　　　　　　　　　　D　　　　　　　　 E　　　　　　　　F

店名　　　　 所属1　　　　　　　　　所属2　　　　　　 提出書類　　　　提出有無　　　提出部数
中町店　　　岸和田市内店舗　　大阪府内店舗　　領収書　　　　　有　　　　　　　　209
沼町店　　　岸和田市内店舗　　大阪府内店舗　　領収書　　　　　無　　　　　　　　0
松ノ浜店　　泉大津市内店舗　　大阪府内店舗　　領収書　　　　　有　　　　　　　　0
米田町店　 加古川市内店舗　　兵庫県内店舗　　領収書　　　　　有　　　　　　　　23

現在はここまで手動入力で出来上がっています。

A　　　　　　　B　　　　　　　　　　　C　　　　　　　　　　D　　　　　　　　 E　　　　　　　　F

店名　　　　 所属1　　　　　　　　　所属2　　　　　　 提出書類　　　　提出有無　　　提出部数
中町店　　　岸和田市内店舗　　大阪府内店舗　　領収書
沼町店　　　岸和田市内店舗　　大阪府内店舗　　領収書
松ノ浜店　　泉大津市内店舗　　大阪府内店舗　　領収書　
米田町店　 加古川市内店舗　　兵庫県内店舗　　領収書

これから各店舗に提出書類を提出してもらいます。各店舗の店長は、一つののパソコンに自分たちでフォルダーを生成し、その中に書類データを保存します。フォルダーは以下のように生成します。

D:\領収証\大阪府内店舗\岸和田市内店舗\中町店
D:\領収証\大阪府内店舗\岸和田市内店舗\沼町店
D:\領収証\大阪府内店舗\泉大津市内店舗\松ノ浜店
D:\領収証\兵庫県内店舗\加古川市内店舗\米田町店

・松ノ浜店の店長は、松ノ浜店で領収書が発生しなかったので領収書は1枚もありませんが、提出手続きを行ったかどうかの確認のため、空の「松ノ浜店」フォルダーを生成しました。

・怠惰な沼町店の店長は、期限までに領収書を提出にこなかったので、「沼町店」フォルダーは生成されませんでした。

以上のような操作の結果、ドライブDとリンクさせたExcelファイルを開くと、E、Fが自動的に入力されている、という状態にしたいです。

A　　　　　　　B　　　　　　　　　　　C　　　　　　　　　　D　　　　　　　　 E　　　　　　　　F

店名　　　　 所属1　　　　　　　　　所属2　　　　　　 提出書類　　　　提出有無　　　提出部数
中町店　　　岸和田市内店舗　　大阪府内店舗　　領収書　　　　　有　　　　　　　　209
沼町店　　　岸和田市内店舗　　大阪府内店舗　　領収書　　　　　無　　　　　　　　0
松ノ浜店　　泉大津市内店舗　　大阪府内店舗　　領収書　　　　　有　　　　　　　　0
米田町店　 加古川市内店舗　　兵庫県内店舗　　領収書　　　　　有　　　　　　　　23

----------------------------------------------------------------------------------

そもそもExcelでこのようなことまでできるのかもわからないのですが、もしできるのであれば方法が知りたいです。
可能な限りExcel2010で表示したいのですが、他に推奨されるソフトや一覧方法等ありましたらお教え願います。
但し、フォルダー概況以外の情報(上記例で例えるなら店舗規模など、フォルダ操作に一切関係しない情報)も併記できるツールであることが必須です。

よろしくお願いいたします。

回答No.1

nicotinism

以下のコードを標準モジュールにコピペします。

Private Const BaseDir As String = "D:\領収証\" 'ここが基点
Private bolVolatile As Boolean

Sub chgVolatile()
　　bolVolatile = Not bolVolatile
　　Application.Volatile bolVolatile
　　MsgBox "再計算は " & bolVolatile
End Sub

Function Reported(R1 As Range, R2 As Range,R3 As Range) As String
　　Dim strDir As String
　　strDir = BaseDir & R1 & "\" & R2 & "\" & R3
　　Reported = IIf(Dir(strDir, vbDirectory) <> "", "有", "無")
End Function

Function ReportCount(R1 As Range, R2 As Range,R3 As Range) As Integer
　　Dim strDir As String
　　Dim i As Integer
　　Dim fName As String

　　strDir = BaseDir & R1 & "\" & R2 & "\" & R3

　　If Dir(strDir, vbDirectory) = "" Then
　　　　ReportCount = 0
　　　　Exit Function
　　End If

　　strDir = strDir & "\*.xls*"
　　fName = Dir(strDir)
　　Do Until fName = ""
　　　　i = i + 1
　　　　fName = Dir
　　Loop

　　ReportCount = i
End Function

提出書類の有無を出したいところには
=reported(C2,B2,A2) として、以下必要範囲までドラッグ（中町店がA2の場合）
部数の方も
=reportcount(C2,B2,A2) として同様に。
これで求められますが、フォルダ内に変化が有っても
Ctrl + Alt + F9 を押さないと再計算してくれません。
かといって自動再計算では、ファイルの読み込みに時間が掛かるために
操作がじれったくなります。

そこで、chgVolatile を加えました。
初期状態はFalseです。実行する都度、状態がFalse/Trueに切り替わります。
True状態ではどこかのセルが変更される都度、すべてが再計算されます。
また、F9 でも再計算されるようになります。
状況に応じて使い分けてください。
※提出書類は、XLSファイルだけで良かったんですよね？ By 沼町店 店長