サブホルダー内のエクセルファイルの名前と更新日を

質問

Excel　VBAですが、
サブホルダー内のエクセルファイルの名前と更新日を取得し、セルに書き出したい。
サブホルダーは３０位あり、各サブホルダーには２０以上のエクセルイルがあります。
イメージですが、最初のサブホルダーをA列に2行目にホルダー名、3行目以降はファイル名（A列）、更新日(B列）、１つ空列、次のサブホルダー名(D列の2行目）・・・・と表示していきたい。
要点でいいのですが、お願いします。

回答No.8

SI299792

最初は腹が立ちましたが、ここのシステムが判っていなければ仕方ありません。私も最初はここのシステムが判らすのお礼をしませんでした。
　私は、回答No.4にお礼を書いて下さい。と言いたかったのです。
　ここは、補足やお礼を書いた相手に、メールが届きます。回答No.4にお礼コメントを書かない限り、HohoPapaさんに解決したというメールはは届きません。メールが届かなくても、ここを見れば解決したことが判るので、既に知っている可能性も高いですが、HohoPapaさんの所にも解決したというメールが届けるべきと思います。
　プロフィールを拝見しました。石川県だそうで、実は私も石川県です。案外近くにいたりして。

回答No.7

SI299792

HohoPapaさんの所にも、お礼を書いて下さい。
そうすれば、HohoPapaさんにお礼メールが届きます。
それが礼儀だと思います。

回答No.6

SI299792

実は私も、MS-BASICをやっていました。どうしてもその癖が抜けせん。例えばWhile 、本当はDo Whileを使うべきなのですが。私も、HohoPapaさんが使っているような、新しい機能はわかりません。

＞Declare Sub Sleep Lib "kernel32.dll" ( _
＞ ByVal vKey As Long)
Sleep という外部サブルーチンを定義しています。外部サブルーチンなので、定義しないと使えません。これはミリ秒単位でプログラムを止めるものです。Sleep 2000で２秒止まります。
＞ Const PathName =
Dim と同じ、変数の宣言です。プログラム中で変更ができません。
＞ Shell "Cmd /C Dir *.xls* /S > Dir.txt"
Shell は、コマンドプロンプトを使ってファイルを呼び出します。
Command.com を呼び出して実行しています。
＞ [A2:XFD1048576].ClearContents
セル[A2:XFD1048576] の中身を消します。１行目にタイトルがあるかもしれないと思い、２行目以降を消すようにしました。失敗して再実行することを考えて付けました。無くてもいいです。
VBA Sleep
VBA Const
のようにググれば、詳しい説明が出てきます。

ここから先はMS-BASICにもあります。
＞ Open "Dir.txt" For Input As #1
ファイルを開きます。
＞ While Not EOF(1)
While 　条件が成立している間続けます。
EOF(1)　最後のファイルを読んだらTrueになります
＞ Line Input #1, FileName
ファイを読みます。"," で区切らす１行読みます。
＞ Kill "Dir.txt"
ファイルを消去します。

　プログラムとしては、HohoPapaさんの方が優れていますが、興味がおありのようなので、参考までに。

お礼

SI299792 さん
ありがとうございます。７０歳の小生では、MS-BASICの延長でしか、なかなか理解が難しく、SI299792 さんのプログラムは見た目は昔よくやったテキスト（バッチで書き込んだテキスト　DIR　＞）を読み込む処理は、かえって　すごく新鮮に感じました。どうもありがとうございました。
それと　HohoPapa さんへのお礼ですが、私がMYWAVEのシステム？をよく理解していなくて、ごめんなさい。
投稿日時 - 2018-03-03 15:54:44の「補足コメント」でお二人にお礼を伝えたと思っていました。
それと　問題が解決したので、その後私の質問について　目を通すこと疎かにしておりました。誠に失礼しました。
今後とも宜しくお願いします。

回答No.5

SI299792

HohoPapaさんのプログラムでうまくいったのなら、私の出る幕ではありませんが、補足があったので、参考までに。
"Dir.txt" はChdir で指定した所にできます。
PathNameで指定した所にできるはずです。
　もし、"Dir.txt" ができていなければ、エラーが出るはずです。動かなかった原因として可能性は２つあります。
(1)xls ファイルが無い。エクセルファイルと聞いたので、"*.xls*"にしましたが、HohoPapaさんのプログラムは"*.*" で全部が対象になっています。"*.*" で試して下さい。
(2)OSの差で、Dir の形式が違っている。例えば、フォルダの表示が「のディレクトリ」ではない、５桁目が"/" でないなど。

　Kill "Dir.txt"を外せば、"Dir.txt" が残るので、見て下さい。
　できれば、結果を書いていただけないでしょうか。もし、Dir で出力される内容が、OSによって違うのであれば、この手段は使えません。今後の参考にさせて下さい。

お礼

本当にありがとうございました。

補足

SI299792さん、HohoPapaさん
ありがとうございました。
色々とお騒がせしました。大変なご迷惑をお掛けしてしまいました。
私が練習用に作成したホルダーとサブホルダー内にファイルを作成したはずが複写しているうちに、なぜかホルダーしかコピーされていなくて、
SI299792さん、HohoPapaさんのプログラムが何回やってもファイルがありませんと出てしまい、思い込んでいた（ファイルはあると）のでプログラムのバグ、ミスと早とちりしてしまいました。
本当にごめんなさい。
私がイメージした処理は出来ていました。お二人のプログラムはやり方は違いますが、私の思いどおりに処理が出来ております。
どうも有難うございました。

回答No.4

HohoPapa

>イメージですが、
>最初のサブホルダーをA列に2行目にホルダー名、
>3行目以降はファイル名（A列）、更新日(B列）、
>１つ空列、次のサブホルダー名(D列の2行目）・・・・と表示していきたい
このレイアウトで出力されていませんでしょうか？

また、ファイルごとの最終更新日時を出力しているハズなんですが、
どこがどのように期待と異なりますか？

回答No.3

HohoPapa

>Dim fso As FileSystemObject 'ユーザー定義型は定義されていません

ありゃ、ごめんなさい。
参照設定が必要です。

https://www.tipsfound.com/vba/18001

↑の記事後半にその説明があります。
この設定を行ってください。

補足

HohoPapaさん
有難うございます。ms-basic からの老人にはやや理解しずらい領域に入りましたが、「スクリプト ランタイム」　を利用できるように「OK」したところ、結果が出ました。本当にありがとうございました。
しかし、図々しいお願になりますが、本当に得たい結果は
そのそれぞれのホルダー内にあるエクセルファイルの名前と最終更新日なのです。サブホルダー内には約２０件ほどのファイルがあります。それをホルダー名の下に順次表示し、次々と右にホルダを処理していく　イメージです。
少し　サジェスチョンを頂ければありがたいのですが・・・
宜しくお願いします。

回答No.2

HohoPapa

>サブホルダーは30位あり、各サブホルダーには20以上のエクセルイルがあります。

もし、
このサブフォルダーの下階層に更にフォルダーがあるような
多層の構造の場合は回帰処理が必要です。
この回帰処理を使ったコードを示します。
（多層じゃなくても使えます）

もし、親フォルダーもシートに出力したい場合は
注1の行をコメントアウトしてください。

Option Explicit

Dim cnt As Long
Dim fso As FileSystemObject
Dim colnum As Integer
Const TargetDir = "D:\wk"　　'これが対象の親フォルダー

Sub Main()
　cnt = 0
　colnum = 1
　Set fso = New FileSystemObject
　Call MySub(TargetDir)
End Sub

Sub MySub(Path As String)
　Dim buf As String, f As Object
　Dim MyFile As File

　If Path <> TargetDir Then　　　　　　　　　　　'注１

　　cnt = 2

　　Cells(cnt, colnum) = Path
　　buf = Dir(Path & "\*.*")
　　Do While buf <> ""
　　　cnt = cnt + 1
　　　Cells(cnt, colnum) = buf
　　　Set MyFile = fso.GetFile(Path & "\" & buf)
　　　Cells(cnt, colnum + 1) = MyFile.DateLastModified
　　　buf = Dir()
　　Loop

　　colnum = colnum + 3

　End If　　　　　　　　　　　　　　　　　　　　'注１

　With CreateObject("Scripting.FileSystemObject")
　　For Each f In .GetFolder(Path).SubFolders
　　　Call MySub(f.Path)
　　Next f
　End With
End Sub

補足

HohoPapa さん　回答ありがとうございます。
が　一発でエラーメッセジがでました。どのように解決するのでしょうか？
大したエラーではないと思いますが、分かりません。

Option Explicit

Dim cnt As Long

>Dim fso As FileSystemObject 'ユーザー定義型は定義されていません

Dim colnum As Integer
Const TargetDir = "D:\家のノート\2017\情報処理_山本" 'これが対象の親フォルダー

Dim fso As FileSystemObjectの定義が「'ユーザー定義型は定義されていません」というエラーになりますが・・・

回答No.1

SI299792

PathNameは変更して下さい。
'
Option Explicit
' 一時停止
Declare Sub Sleep Lib "kernel32.dll" ( _
　　ByVal vKey As Long)
'
Sub Macro1()
'
　　Const PathName = "C:\Users\MA\Desktop\My Documents"
　　Dim FileName As String
　　Dim Row As Long
　　Dim Col As Integer
　　Dim PLen As Integer
'
　　ChDrive PathName
　　ChDir PathName
　　Close
　　Shell "Cmd /C Dir *.xls* /S > Dir.txt"
　　Sleep 2000
　　PLen = Len(PathName)
　　[A2:XFD1048576].ClearContents
　　Col = -2
'
　　Open "Dir.txt" For Input As #1
　　While Not EOF(1)
　　　　DoEvents
　　　　Line Input #1, FileName
　　　　If FileName Like "*のディレクトリ" Then
　　　　　　Col = Col + 3
　　　　　　Row = Len(FileName) - PLen - 10
　　　　　　Row = WorksheetFunction.Max(Row, 0)
　　　　　　Cells(2, Col) = Mid(FileName, PLen + 3, Row)
　　　　　　Row = 2
　　　　ElseIf Mid(FileName, 5, 1) = "/" Then
　　　　　　Row = Row + 1
　　　　　　Cells(Row, Col) = Mid(FileName, 37)
　　　　　　Cells(Row, Col + 1) = Left(FileName, 10)
　　　　End If
　　Wend
　　Close
　　Kill "Dir.txt"
End Sub

補足

SI299792 さん
早速の回答をありがとうございます。Webで色々と検索していましたが、ある程度のところまでは行くのですが・・・ということで、
「ＯＫ　ＷＡＶＥ」でお願いした次第です。
エラー無くおわっているようですが　どこが悪いのか？まったく動きがありません。
以下のステートメント（コード）が理解できません。少し教えてもらえませんか？
＞Declare Sub Sleep Lib "kernel32.dll" ( _
＞ ByVal vKey As Long)

＞ Const PathName = "D:\家のノート\2017\情報処理"

＞ Shell "Cmd /C Dir *.xls* /S > Dir.txt"

＞ [A2:XFD1048576].ClearContents

＞ Open "Dir.txt" For Input As #1
＞ While Not EOF(1)
＞ Line Input #1, FileName

＞ Kill "Dir.txt"
　　　　最後のOpen以降は過去に(５０年以上前かな）MS-BASICをやっていた時に使っていたので、理解できますが
　　　　"Dir.txt"はどこに　作成されるのですか？　DOSレベルですね
以上　教えてください。お願いします。　「これは教えられない」という事であれば、それでも結構ですが
他に利用することは全く考えていません。私の年齢は７０歳です。単にVBAを楽しんでいるだけです。