Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


1456 / 13644 ツリー ←次へ | 前へ→

【74322】Next で行まで初期化してしまうかどうか にしもり 13/5/22(水) 19:16 質問[未読]
【74323】Re:Next で行まで初期化してしまうかどうか kanabun 13/5/22(水) 19:31 発言[未読]
【74324】Re:Next で行まで初期化してしまうかどうか kanabun 13/5/22(水) 23:46 発言[未読]
【74325】Re:Next で行まで初期化してしまうかどうか kanabun 13/5/22(水) 23:48 発言[未読]
【74336】Re:Next で行まで初期化してしまうかどうか kanabun 13/5/23(木) 20:08 発言[未読]
【74338】Re:Next で行まで初期化してしまうかどうか にしもり 13/5/24(金) 6:04 お礼[未読]
【74339】Re:Next で行まで初期化してしまうかどうか kanabun 13/5/24(金) 9:18 発言[未読]
【74326】Re:Next で行まで初期化してしまうかどうか kanabun 13/5/23(木) 0:41 発言[未読]
【74327】Re:Next で行まで初期化してしまうかどうか にしもり 13/5/23(木) 5:19 発言[未読]

【74322】Next で行まで初期化してしまうかどうか
質問  にしもり  - 13/5/22(水) 19:16 -

引用なし
パスワード
   こんにちは。
Next Folderで次のフォルダ―に移る時、
貼付行 = 0に初期化してしまうでしょうか?
何故だか、次のフォルダ―に移ると、またsheetの1行目から書いてしまいます。

Sub Sample()
  Call FileSearch("C:\_cosmos\Tr")
End Sub

Sub FileSearch(Path As String)
  Dim FSO As Object, Folder As Variant, File As Variant
  Set FSO = CreateObject("Scripting.FileSystemObject")

  貼付行 = 0
  For Each Folder In FSO.GetFolder(Path).SubFolders
    Call FileSearch(Folder.Path)


    For Each File In FSO.GetFolder(Path).Files
'      Debug.Print File.Path
    

      貼付行 = 貼付行 + 1
      Cells(貼付行, 1).Value = File.Path
      
   
    Next File

  Next Folder
  
End Sub

【74323】Re:Next で行まで初期化してしまうかどう...
発言  kanabun  - 13/5/22(水) 19:31 -

引用なし
パスワード
   ▼にしもり さん:

>Next Folderで次のフォルダ―に移る時、
>貼付行 = 0に初期化してしまうでしょうか?
>何故だか、次のフォルダ―に移ると、またsheetの1行目から書いてしまいます。

>Sub FileSearch(Path As String)
変数「貼付行」は ↑このプロシージャのなかで使っているローカル変数
ですから、このプロシージャが呼ばれるときにはいつも

>  貼付行 = 0

を通るたびに、ゼロに初期化されます。

初期化されないようにするには、
モジュールの先頭で
Private でその変数を宣言するように、変数の有効範囲を拡大すると
いいです。


他には
プロシージャ内で
Static
で宣言して使う方法もあるとも思いますが。

【74324】Re:Next で行まで初期化してしまうかどう...
発言  kanabun  - 13/5/22(水) 23:46 -

引用なし
パスワード
   ▼にしもり さん:

その他の方法として 以下の2方法もあると思います。

(1)貼付行も 呼び出し時にパラメータ(引数)として渡す方法

Sub Sample()
  Call FileSearch("C:\_cosmos\Tr")
End Sub

'「貼付け行」も呼び出し側で指定する
Sub FileSearch(Path As String, 貼付行 As Long)
  Dim Folder As Object
  Dim File As Object

  With CreateObject("Scripting.FileSystemObject")
    For Each Folder In .GetFolder(Path).SubFolders
      
      Call FileSearch(Folder.Path, 貼付行)

      For Each File In .GetFolder(Path).Files
        貼付行 = 貼付行 + 1
        Cells(貼付行, 1).Value = File.Path
      Next File

    Next Folder
  End With

End Sub


(2) 上の方法は FSOを使っていることと、1ファイルづつ都度セルに
  書き込んでいるので、ファイル数が多くなると遅くなります。
  Dirコマンドで検索し、検索結果を配列に格納して シートに
  一括して書き込むようにすれば、格段にスピードアップします。

Sub Sample2()
 Dim n As Long
 Dim FoundFiles() As String

  n = SearchFiles("C:\_cosmos\Tr", "*.*", FoundFiles())
  If n > 0 Then
    Range("B1").Resize(n).Value = _
         Application.Transpose(FoundFiles)
  End If
End Sub

'//サブフォルダを含むファイルの一覧
Function SearchFiles(LookIn As String, Filename As String, _
          FoundFiles() As String) As Long
  Dim i As Long
  Dim Ext As Variant
  Dim tmpPath As String
  Dim sCmd As String
  Dim ko As Long
  
  If Right$(LookIn, 1) <> "\" Then LookIn = LookIn & "\"
  If InStr(Filename, ";") > 0 Then
    Ext = Split(Filename, ";")
    For i = 0 To UBound(Ext)
      Ext(i) = LookIn & Ext(i)
    Next
    Filename = Join(Ext, """ """)
  Else
    Filename = LookIn & Filename
  End If
  tmpPath = LookIn & "Dir.tmp"   'Dirの結果を一時ファイルに出力
  sCmd = "DIR """ & Filename & """ /b/s/o:N > """ & tmpPath & """"
           '' /b ファイル名のみ
           '' /s サブディレクトリも検索
           '' /o:N 名前順でソート
  With CreateObject("WScript.Shell")
    ko = .Run("CMD /C " & sCmd, 7, True) 'Dirコマンド実行
  End With
  If ko Then
    MsgBox "ファイルの検索に失敗しました", , LookIn
    Exit Function
  End If
  If FileLen(tmpPath) < 2 Then Exit Function
  
  Dim io As Integer
  Dim buf() As Byte
  io = FreeFile()
  Open tmpPath For Binary As io
   ReDim buf(1 To LOF(io))
   Get #io, , buf
  Close io
  Kill tmpPath
  FoundFiles() = Split(StrConv(buf, vbUnicode), vbCrLf)
  ko = UBound(FoundFiles)
  ReDim Preserve FoundFiles(ko - 1)
  SearchFiles = ko
  
End Function

【74325】Re:Next で行まで初期化してしまうかどう...
発言  kanabun  - 13/5/22(水) 23:48 -

引用なし
パスワード
   失礼

>(1)貼付行も 呼び出し時にパラメータ(引数)として渡す方法
>
>Sub Sample()
>  Call FileSearch("C:\_cosmos\Tr")
>End Sub

これは

Sub Sample()
  Call FileSearch("C:\_cosmos\Tr", 0)
End Sub

の間違いです m(_ _)m

【74326】Re:Next で行まで初期化してしまうかどう...
発言  kanabun  - 13/5/23(木) 0:41 -

引用なし
パスワード
   ▼にしもり さん:

いまさらですが、

> Sub FileSearch(Path As String, 貼付行 As Long)
>   Dim Folder As Object
>   Dim File As Object
>
>   With CreateObject("Scripting.FileSystemObject")
>     For Each Folder In .GetFolder(Path).SubFolders
>      
>       Call FileSearch(Folder.Path, 貼付行)
>
>       For Each File In .GetFolder(Path).Files
>         貼付行 = 貼付行 + 1
>         Cells(貼付行, 1).Value = File.Path
>       Next File
>
>     Next Folder
>   End With
>
> End Sub

この処理順、おかしくないですか?

実行すると、
同じファイルが 何度も書き出されないですか?

先に 指定フォルダ内のファイルを書き出して、
それから サブフォルダを再帰で検索したほうがいいような気がします。


'↓ ★ 事前に Microsoft Scripting Runtime へ参照設定が必要です。

Sub Sample()
  Dim Fso As Scripting.FileSystemObject
  Dim oFolder As Scripting.Folder
  
  Set Fso = New Scripting.FileSystemObject
  Set oFolder = Fso.GetFolder("C:\_cosmos\Tr")
  
  FileSearch2 oFolder, 0
End Sub

Private Sub FileSearch2(myFolder As Scripting.Folder, _
            貼付行 As Long)

  Dim oFolder As Scripting.Folder
  Dim oFile As Scripting.File
  Dim s As String

  For Each oFile In myFolder.Files
    貼付行 = 貼付行 + 1
    Cells(貼付行, 1).Value = oFile.Path
  Next

  For Each oFolder In myFolder.SubFolders
    FileSearch2 oFolder, 貼付行
  Next

End Sub

【74327】Re:Next で行まで初期化してしまうかどう...
発言  にしもり  - 13/5/23(木) 5:19 -

引用なし
パスワード
   ▼kanabun さん:

>実行すると、
>同じファイルが 何度も書き出されないですか?

アドバイスまことにありがとうございます。
処理順おかしいですかね、、
もう一度よく考えてみます

【74336】Re:Next で行まで初期化してしまうかどう...
発言  kanabun  - 13/5/23(木) 20:08 -

引用なし
パスワード
   ▼にしもり さん:

以下のFunction ですが、
実際にDirをとってみたら、サブフォルダ名まで書き出していましたので
Dirコマンドのパラメータに /a:-D を追加して
「サブディレクトリー名は表示しない」
ように修正しました。


>'//サブフォルダを含むファイルの一覧
>Function SearchFiles(LookIn As String, Filename As String, _
>          FoundFiles() As String) As Long
>  Dim i As Long
>  Dim Ext As Variant
>  Dim tmpPath As String
>  Dim sCmd As String
>  Dim ko As Long
>  
>  If Right$(LookIn, 1) <> "\" Then LookIn = LookIn & "\"
>  If InStr(Filename, ";") > 0 Then
>    Ext = Split(Filename, ";")
>    For i = 0 To UBound(Ext)
>      Ext(i) = LookIn & Ext(i)
>    Next
>    Filename = Join(Ext, """ """)
>  Else
>    Filename = LookIn & Filename
>  End If
>  tmpPath = LookIn & "Dir.tmp"   'Dirの結果を一時ファイルに出力

  sCmd = "DIR """ & Filename & """ /b/s/a:-D/o:N > """ & tmpPath & """"
           '' /b ファイル名のみ
           '' /s サブディレクトリも検索
           '' /a:-D サブディレクトリー名は表示しない
           '' /o:N 名前順でソート

>  With CreateObject("WScript.Shell")
>    ko = .Run("CMD /C " & sCmd, 7, True) 'Dirコマンド実行
>  End With
>  If ko Then
>    MsgBox "ファイルの検索に失敗しました", , LookIn
>    Exit Function
>  End If
>  If FileLen(tmpPath) < 2 Then Exit Function
>  
>  Dim io As Integer
>  Dim buf() As Byte
>  io = FreeFile()
>  Open tmpPath For Binary As io
>   ReDim buf(1 To LOF(io))
>   Get #io, , buf
>  Close io
>  Kill tmpPath
>  FoundFiles() = Split(StrConv(buf, vbUnicode), vbCrLf)
>  ko = UBound(FoundFiles)
>  ReDim Preserve FoundFiles(ko - 1)
>  SearchFiles = ko
>  
>End Function

【74338】Re:Next で行まで初期化してしまうかどう...
お礼  にしもり  - 13/5/24(金) 6:04 -

引用なし
パスワード
   ▼kanabun さん:
ご指摘ありがとうございます。
確かに処理順がおかしいのでご教示いただいたMicrosoft Scripting Runtime の方法を使わせていただきました。
希望通りに出来ました。
ローカル変数「貼付行」が初期化されないやりかた、Microsoft Scripting Runtimeの用い方等、勉強になりました。
サブディレクトリー名を表示しない方法についてもありがとうございました。
ただサブディレクトリー名は表示させたいので実際には用いませんでした。
ありがとうございました。

【74339】Re:Next で行まで初期化してしまうかどう...
発言  kanabun  - 13/5/24(金) 9:18 -

引用なし
パスワード
   ▼にしもり さん:

>確かに処理順がおかしいのでご教示いただいたMicrosoft Scripting Runtime の方法を使わせていただきました。
>希望通りに出来ました。

>サブディレクトリー名を表示しない方法についてもありがとうございました。
>ただサブディレクトリー名は表示させたいので実際には用いませんでした。

補足ですが、たとえば
C:\_cosmos\Tr フォルダの中が 以下のような構成になっているとき、

C:\_cosmos\Tr
  ┃
  ┣━ aaa
  ┃   111.xls
  ┃
  ┣━ bbb
  ┃   222.xls
  ┃
  ┗━ ccc
      333.xls


Fsoを使う方法

  Set oFolder = Fso.GetFolder("C:\_cosmos\Tr")
  FileSearch2 oFolder, 1 '2行目から書き出す

で FileSearch2 を呼び出しますと、

 [FileSearch2]
2  C:\_cosmos\Tr\aaa\111.xls
3  C:\_cosmos\Tr\bbb\222.xls
4  C:\_cosmos\Tr\ccc\333.xls

というファイルリストが出力されます。

(こういうリストが出力されればいいのですよね?)

ところが、(Fsoは遅いので) もっと高速に一覧を得ようとして

  n = SearchFiles("C:\_cosmos\Tr", "*.*", FoundFiles())

で Dirコマンドを呼び出すと、
----------------
    C:\_cosmos\Tr\aaa
    C:\_cosmos\Tr\bbb
    C:\_cosmos\Tr\ccc
    C:\_cosmos\Tr\aaa\111.xls
    C:\_cosmos\Tr\bbb\222.xls
    C:\_cosmos\Tr\ccc\333.xls

というファイル一覧が得られるのです。

こうなってしまうのは
Dirコマンドのオプションを

  sCmd = "DIR """ & Filename & """ /b/s/o:N > """ & tmpPath & """"

としていたからです。

 [FileSearch2]
と同様の結果が欲しいなら、オプションは

  sCmd = "DIR """ & Filename & """ /b/s/a:-D/o:N > """ & tmpPath & """"
           '' /b ファイル名のみ
           '' /s サブディレクトリも検索
           '' /a/:-D サブディレクトリー名は表示しない
           '' /o:N 名前順でソート
としなくてはいけません。

※なお、Dirコマンドの一時ファイル出力先を 対象フォルダ として
 いたのは、全ファイルが検索対象のときそれも表示され都合が悪かったので
 WindowsのTEMPフォルダ内に作成することにしました。
 参考のため、それを以下に再掲しておきます。

'//サブフォルダを含むファイルの一覧 【一時ファイル先 修正後】
Function SearchFiles(LookIn As String, Filename As String, _
          FoundFiles() As String) As Long
  Dim i As Long
  Dim Ext As Variant
  Dim tmpPath As String
  Dim sCmd As String
  Dim ko As Long
  
  If Right$(LookIn, 1) <> "\" Then LookIn = LookIn & "\"
  If InStr(Filename, ";") > 0 Then '複数ファイルタイプ対応
    Ext = Split(Filename, ";")  '例 "*.csv;*.xls"
    For i = 0 To UBound(Ext)
      Ext(i) = LookIn & Ext(i)
    Next
    Filename = Join(Ext, """ """)
  Else
    Filename = LookIn & Filename
  End If
  tmpPath = Environ$("TEMP") & "\Dir.tmp" '●一時ファイル出力先
  
  sCmd = "DIR """ & Filename & """ /b/s/a:-D/o:N > """ & tmpPath & """"
           '' /b ファイル名のみ
           '' /s サブディレクトリも検索
           '' /a/:-D サブディレクトリー名は表示しない●
           '' /o:N 名前順でソート
  With CreateObject("WScript.Shell")
    ko = .Run("CMD /C " & sCmd, 7, True) 'Dirコマンド実行
  End With
  If ko Then
    MsgBox "ファイルの検索に失敗しました", , LookIn
    Exit Function
  End If
  If FileLen(tmpPath) < 2 Then Exit Function
  
  Dim io As Integer
  Dim buf() As Byte
  io = FreeFile()
  Open tmpPath For Binary As io
   ReDim buf(1 To LOF(io))
   Get #io, , buf
  Close io
  Kill tmpPath
  FoundFiles() = Split(StrConv(buf, vbUnicode), vbCrLf)
  ko = UBound(FoundFiles)
  ReDim Preserve FoundFiles(ko - 1)
  SearchFiles = ko
  
End Function

1456 / 13644 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free