Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10全般 : Excel 2016)
あるフォルダ直下のファイル名(リンク付きとその他)を取得した
投稿日時: 17/11/22 15:37:06
投稿者: いしやん

別の投稿から、下記のコードを入手しましたが、
一覧にリストとして、表示されるのでなく、「cnt」がリセットされるようで、
すべてのファイル名が順番にリストになりません。
初歩的な質問ですが、よろしくお願いします。
また、リンク付きと可能ならば、ファイル名取得の横にファイルの場所を開けられるリンクも
貼りたいと思っています。
 
 
 
Sub Sample3(Path As String)
    Dim buf As String, f As Object, cnt As Long
    buf = Dir(Path & "\*.*")
    Do While buf <> ""
        cnt = cnt + 1
        Cells(cnt, 1) = buf
        buf = Dir()
    Loop
    With CreateObject("Scripting.FileSystemObject")
        For Each f In .GetFolder(Path).SubFolders
            Call Sample3(f.Path)
        Next f
    End With
End Sub
 
Sub Test10()
    'Dim
    cnt = 0
    Call Sample3("C:\Users\ishitsuji\Desktop\個人資料PC\")
End Sub

回答
投稿日時: 17/11/22 16:19:50
投稿者: bi

そのコードの入手先は
 
サブフォルダを含めてファイル一覧を取得する(Dir関数の再帰呼び出し)
http://www.moug.net/tech/exvba/0060088.html
 
ですよね。A列を選択して昇順に並べ替えるマクロを足せばいいと思います。リンクに関しては
 
ハイパーリンクを設定する
https://www.moug.net/tech/exvba/0050064.html
 
が参考になると思います。

投稿日時: 17/11/22 17:02:10
投稿者: いしやん

biさん
 回答ありがとうございます。
 
 指摘のとおり、サイトから、コピペしたんです。。
 ですが、結果がおかしいのです。。昇順にする以前の問題で、
 例えば、該当フォルダに、ファイルが1個、フォルダが1個(中にファイル1個)あるとすると
 結果、2個ファイル名が表示されると思うのですが、1個だけの表示になります。
 「cnt」が +1 されてなく、1行目に上書きされているようですが、私のPCの問題でしょうか?

回答
投稿日時: 17/11/22 17:16:04
投稿者: bi

テストしてみましたが、こちらではサブフォルダ内のファイルもサブフォルダの上の階層のファイルも取得されました。

回答
投稿日時: 17/11/22 17:36:17
投稿者: もこな2

当方の環境(Win7、Excel2013)でそのままコピペ実行してみましたが、うまくいかなかったです。
(もちろん対象フォルダは適宜変更しましたが)
 
とりあえず、

引用:
「cnt」がリセットされるようで
とのことですが、Sample3の中で宣言してたら、それはリセットされるんじゃないかと。。。
 
再帰呼び出しするときに「cnt」を参照渡しで渡してあげるか、広域変数にしないとだめな気がします。

回答
投稿日時: 17/11/22 18:58:20
投稿者: もこな2

ちょっと調べてみたら「広域変数」っていう言葉は無いようですね。混乱するような言葉を使ってごめんなさい。いいたかったのは、広域で使える変数を使用しないと駄目な気がするということです。
 
用語として正しく言うとSample3の中で宣言した場合、「プロシジャーレベル変数」というものになるようです。
要は「Sub〜〜End Sub」までの間しか有効じゃない(再帰呼び出ししたプロシジャーまでは影響しない)変数ですね。
 
モジュール全体で共有する変数は、「モジュールレベル変数」というものになります。
 
これを使用する場合は「Sub〜〜End Sub」といった記述の上で宣言するようにしてください。
モジュール全体で使うから、プロシジャの記述を始める前に最初に宣言するようなイメージですね。
 
ちなみに、モジュール間で共有する変数であれば「パブリック変数」となります。
今回は使わないと思いますので宣言方法の説明は割愛します。
 
また、

引用:
該当フォルダに、ファイルが1個、フォルダが1個(中にファイル1個)あるとすると結果、2個ファイル名が表示されると思うのですが、1個だけの表示になります。
とのことですが、ソースコードを拝見すると、ファイル名を表示(出力)するようにはなっていますが、パスないしサブフォルダ名を出力するような記述がみあたらないので、提示条件だと、1個のファイル名のみ出力するのが正常な動作になるようにおもいます。

回答
投稿日時: 17/11/22 19:29:42
投稿者: もこな2

度々すみません。サブフォルダのなかにファイルが1個あるんですね。見落としてました。

もこな2 さんの引用:
引用:
該当フォルダに、ファイルが1個、フォルダが1個(中にファイル1個)あるとすると結果、2個ファイル名が表示されると思うのですが、1個だけの表示になります。
とのことですが、ソースコードを拝見すると、ファイル名を表示(出力)するようにはなっていますが、パスないしサブフォルダ名を出力するような記述がみあたらないので、提示条件だと、1個のファイル名のみ出力するのが正常な動作になるようにおもいます。
この場合は、ファイル名が2個出力されるで正しいです。m(_ _)m
 
このほか、
引用:
ファイル名取得の横にファイルの場所を開けられるリンクも貼りたいと思っています。
ですが、ファイル名取得の横っていうのが何を指しているかわからないです。個人的には、出力したファイル名というかセルにbiさんが仰るようにハイパーリンクを設定するのが良さそうに思います。

回答
投稿日時: 17/11/23 11:47:02
投稿者: simple

横から失礼します。
 
質問者さんへ。
 
mougの即効テクニックの元記事
http://www.moug.net/tech/exvba/0060088.html
「サブフォルダを含めてファイル一覧を取得する(Dir関数の再帰呼び出し)|Excel VBA」
では、きちんとモジュールレベルの変数宣言がされていますよ?

Dim cnt As Long

Sub Sample3(Path As String)
  Dim buf As String, f As Object
  buf = Dir(Path & "\*.*")
   (以下略)

 
リンク付加機能があるVBAマクロについては、
例えば、
http://www.asahi-net.or.jp/~ef2o-inue/download/sub09_010.html
にある サンプルファイル(SearchFolder400.zipにあります。)はいかがですか?
「Best フリーソフト大賞 2005」を獲得されたもので定評があると思います。
腰を据えてじっくり細部まで研究してみてはどうでしょうか。

投稿日時: 17/11/24 12:00:42
投稿者: いしやん

みなさま
 回答ありがとうございます。
 下記のコードで、実行できました。(コピペですが、、)
 なんでか作成していたエクセルファイルでは、実行できずに、
 新規作成したエクセルにコピペすることで、実行できました。
 
 もとのファイルも、色々とパラメータとか、コードを書いた場所とかチェックしたのですが、
 原因は、わからないままです。詳細まで突き詰めたいですが、今の私には無理です。。
 
 simpleさんのいうように、じっくり腰を据えて研究しないといけませんが、
 とりあえず、どんどんやってみない処理を作成していきたいと思います。
 今後ともよろしくお願いします。
 
 この処理もまだ、途中なので、少しづつレベルを上げていきたいと思います。
 
 
Dim cnt As Long
 
Sub Sample3(Path As String)
    Dim buf As String, f As Object
    buf = Dir(Path & "\*.*")
    Do While buf <> ""
        cnt = cnt + 1
        Cells(cnt, 1) = buf
        buf = Dir()
    Loop
    With CreateObject("Scripting.FileSystemObject")
        For Each f In .GetFolder(Path).SubFolders
            Call Sample3(f.Path)
        Next f
    End With
End Sub
 
 
Sub Test()
    cnt = 0
    Call Sample3("C:\Users\ishitsuji\Desktop\test100\")
End Sub

回答
投稿日時: 17/11/24 16:17:13
投稿者: もこな2

無事、解決されたようでよかったです。
なお、ファイルを探すのはDir関数で、サブフォルダを探すのはFileSystemObjectでそれぞれ処理されていますが、どちらも、FileSystemObjectで処理できます。
 
また、リンク付きの問題は解決されましたでしょうか。
ファイル名の取得もFileSystemObjectを使用した例と、ハイパーリンク設定のヒントを記載したコードを提示しますので、興味がありましたら参考にされてください。
Dim cnt As Long
 
Sub メインルーチン()
    cnt = 0
    Call サブルーチン(Environ("USERPROFILE") & "\Desktop\test100\")
End Sub
 
Sub サブルーチン(パス As String)
    Dim フォルダ As Object, ファイル As Object
 
    With CreateObject("Scripting.FileSystemObject")
        'ファイルの検索
        For Each ファイル In .GetFolder(パス).Files
            cnt = cnt + 1
            Cells(cnt, 1).Value = ファイル.Name
            'ヒント1 ハイパーリンクのアンカーは、、、
            'ヒント2 「ファイル.ParentFolder」で返ってくるものは、、、
        Next ファイル
 
        'サブフォルダの検索
        For Each フォルダ In .GetFolder(パス).SubFolders
            Call サブルーチン(フォルダ.Path) 'サブフォルダがあれば、サブフォルダのパスを引数にして再帰呼出
        Next フォルダ
    End With
End Sub

投稿日時: 17/11/27 13:42:18
投稿者: いしやん

長々とすみません。
もこな2さんの宿題に取り掛かっておりますが、
下記で、ハイパーリンクの書式にはなりましたが、リンクへ飛びません。
add anchor だけでは、リンク設定できてないのでしょうか?
parentfolderについても、意図がわからないです。こちらでリンク先の記述をしているのでしょうか?
 
Dim cnt As Long
  
Sub Extraction()
    cnt = 3
    Call ExtractSUB(Environ("USERPROFILE") & "\Desktop\test100\")
End Sub
  
Sub ExtractSUB(PATH As String)
    Dim FOLDA As Object, FILE As Object
  
    With CreateObject("Scripting.FileSystemObject")
        'ファイルの検索
        For Each FILE In .GetFolder(PATH).Files
            cnt = cnt + 1
            Cells(cnt, 1).Value = FILE.Name
             
                With ActiveSheet.Hyperlinks
                .Add Anchor:=Cells(cnt, 1), Address:=Cells(cnt, 2).Value
                End With
             
            'ヒント1 ハイパーリンクのアンカーは、、、
            'ヒント2 「ファイル.ParentFolder」で返ってくるものは、、、
             
        Next FILE
  
        'サブフォルダの検索
        For Each FOLDA In .GetFolder(PATH).SubFolders
            Call ExtractSUB(FOLDA.PATH) 'サブフォルダがあれば、サブフォルダのパスを引数にして再帰呼出
        Next FOLDA
    End With
End Sub
 
 

回答
投稿日時: 17/11/27 16:51:52
投稿者: もこな2

引用:
add anchor だけでは、リンク設定できてないのでしょうか?
parentfolderについても、意図がわからないです。こちらでリンク先の記述をしているのでしょうか?

add anchorはリンクが設定される、オブジェクトを指定します。
.Add Anchor:=Cells(cnt, 1)であれば、セル「"A" & cnt」にハイパーリンクが設定されます。(リンク先ではないです。)
 
では、リンク先はどこで設定しているのかというと、 Address:=Cells(cnt, 2).Valueで設定しています。
しかし、作成されたコードを拝見すると、B列には何も書き込むようになっていないので、どの行もB列はブランクだとおもいますので、Excel君からみればセル「"A" & cnt」にセル「"B" & cnt」の値(=何も無い)へのリンクを設定しなさいっていう命令になってるとおもいます。
 
ファイル.ParentFolderについては、ブレークポイントを適宜設定して、イミディエイトウィンドウに、「?ファイル.ParentFolder」と入力実行してみるとわかるとおもいますが、ファイルが保存されているフォルダのフルパスが返ってきます。これは
引用:
ファイルの場所を開けられるリンク
というのが、よくわからなかったので、ファイルが保存されているフォルダがひらけばいいのかなぁとおもったので、親フォルダをつかむ方法をヒントとして出しました。
クリックしたらファイルを直接開くようにしたい場合は、ファイル.〜〜〜(←パスを掴むメソッド)にすればよいとおもいます。

回答
投稿日時: 17/11/27 17:28:49
投稿者: baoo

基本的な物の考え方として、リンクを設定するということは
それをクリックしたときにそのファイルが開くことを期待するわけですよね。
そうするとそのための命令は必ずそのファイルがどこにあるのかを指定するはずで、
つまりどこかでフルパスで指定するか、パス+ファイル名で指定するはずだと考えるべきです。
 
どんな問題でもそうですが技術的な話ではなく、問題を解決するための第一歩として
重要な考え方だと思います。
 
 
 

投稿日時: 17/11/27 18:56:56
投稿者: いしやん

たびたびすみません。
B列にパスを記載するようにしたことで、
A列をクリックすることでファイルが開けられるようになりました。
予定通りですが、なぜうまくいったか、わかりません。
気持ち的には、B列は、表示したくない(非表示にしたいのですが、)
このやり方は、間違っていますか?
ほんとうに初歩的な質問ですみません。
 
 
 
Dim cnt As Long
  
Sub Extraction()
    cnt = 3
    Call ExtractSUB(Environ("USERPROFILE") & "\Desktop\test100\")
End Sub
  
Sub ExtractSUB(PATH As String)
    Dim FOLDA As Object, FILE As Object
  
    With CreateObject("Scripting.FileSystemObject")
        'ファイルの検索
        For Each FILE In .GetFolder(PATH).Files
            cnt = cnt + 1
            Cells(cnt, 1).Value = FILE.Name
            Cells(cnt, 2).Value = FILE.PATH
             
                With ActiveSheet.Hyperlinks
                .Add Anchor:=Cells(cnt, 1), Address:=Cells(cnt, 2).Value
                 
                End With
           
             
             
            'ヒント1 ハイパーリンクのアンカーは、、、
            'ヒント2 「ファイル.ParentFolder」で返ってくるものは、、、
             
        Next FILE
  
        'サブフォルダの検索
        For Each FOLDA In .GetFolder(PATH).SubFolders
            Call ExtractSUB(FOLDA.PATH) 'サブフォルダがあれば、サブフォルダのパスを引数にして再帰呼出
        Next FOLDA
    End With
End Sub

回答
投稿日時: 17/11/27 19:45:33
投稿者: baoo

B列は関係ありません。
B列、Cells(cnt, 2).Valueは何ですか?
そのファイルのパスですよね。
それによってたまたまAddressにそのパスの文字列が指定されたので動いたのです。
パスをどこかのセルに設定しなければならないということは無い筈です。
パスを表示したくないならAddressにどこかのセルを指定するのではなく、
パスを直接指定すれば良いだけです。

回答
投稿日時: 17/11/28 02:55:14
投稿者: もこな2

う〜ん私の説明が悪いのかなぁ・・・・
 
たぶん、Address:=〇〇〇〇の赤字の部分について、セルから取得しないとダメって勘違いされているように思われます。
 
実際にやってみるとわかると思いますが、
Anchor:=〇〇〇  → 〇〇〇 にアンカーの対象となるオブジェクトを指定、(Rangeオブジェクト、シェイプなど)
Address:=◇◇◇ → リンク先のアドレスを◇◇◇に設定。(URL、ファイルやフォルダのパスなど、)
ですので、「Address」については、セルから取得せず直接コード上に記述しても構いません。
 
極端な話、

 ActiveSheet.Hyperlinks.Add Anchor:=Cells(cnt, 1), Address:="http://www.moug.net/faq/viewtopic.php?t=7646"
としてやれば、A列に何と書いてあろうが、どのリンク押そうが全部このスレッドに飛ぶようにもできます。
baoo さんの引用:
パスを表示したくないならAddressにどこかのセルを指定するのではなく、
パスを直接指定すれば良いだけです

答えがすでに出ていますが、一応コードにするとすれば、
        For Each FILE In .GetFolder(PATH).Files 
            cnt = cnt + 1
            Cells(cnt, 1).Value = FILE.Name
            Cells(cnt, 2).Value = FILE.PATH
                With ActiveSheet.Hyperlinks
                 .Add Anchor:=Cells(cnt, 1), Address:=Cells(cnt, 2).Value
                End With
        Next FILE 
     ↓こんな感じに修正
        For Each FILE In .GetFolder(PATH).Files 
            cnt = cnt + 1
            Cells(cnt, 1).Value = FILE.Name
                With ActiveSheet.Hyperlinks
                 .Add Anchor:=Cells(cnt, 1), Address:=FILE.PATH
                End With
        Next FILE 
「Address:=」に何が設定されているかをご確認ください。
 
※だた、この場合リンクをクリックするとファイルが開きますけどそれでいいんでしょうか?
引用:
ファイル名取得の横にファイルの場所を開けられるリンクも貼りたい
っていうのがどういうことかよくわからないですけど、例えば保存されてるフォルダを開きたいっていうのであれば、ファイルパスではなく、親フォルダをつかむメソッドで対応する使用する必要がありますよね。

回答
投稿日時: 17/11/28 08:37:56
投稿者: mattuwan44

>予定通りですが、なぜうまくいったか、わかりません。

エクセルの仕組みを理解せずに作業の指示書(=プログラム)の文言だけを読んでも、
なかなか理解できないでしょう。
 
まずは、1件、手動で確実に設定できるようになる。
次に、その操作をマクロの記録でコードに変換してみる。
これで、どの操作が(あるいはどの設定が)どの文言(コード)にあたるのか理解する。
 
それが解れば、あとは繰り返すだけですよね?
 
>気持ち的には、B列は、表示したくない(非表示にしたいのですが、)
>このやり方は、間違っていますか?

不要なら、作業が終ったら消せばいいのでは?
変数の代わりにセルを使えるのも、エクセルVBAのいいところです。
 
>とりあえず、どんどんやってみない処理を作成していきたいと思います。
興味のあるところに向かうのは、モチベーションの維持という点では有効ですが、
基礎を飛ばしては、すぐに破綻します。
「なぜ?」が出てくれば、その都度、疑問を解消していくことをお勧めします。
それから、疑問を解消するための手段も当然習う必要がありますが、
こういう掲示板ではコードの話しかされない(=質問されない)ので、
そういうことも質問されることをお勧めします。
ネット検索してもVBEの使い方とかデバッグの実例とかガッツリ説明されてるところが、
ないんですよねー。。。

投稿日時: 17/11/28 10:02:24
投稿者: いしやん

皆様
 
回答ありがとうございました。
 
一旦、やってみたい内容はできました。
 
いといろとサイトの見ながら、勉強しているつもりなのですが、
肝心な基礎的な部分、用語とか、構成とかが、正直理解できていないんですよね。
 
ですが、
 
やりたい処理は、いくらでもあるので、地道に少しづつやっていきたいと思います。
 
ありがとうございました!!

投稿日時: 17/11/29 16:29:17
投稿者: いしやん

一旦、できました。
次は、すべてのファイルでなく、検索機能を持たせるように改良していきます。