vba 列出文件夹和子文件夹中的文件以及 .txt 文件的路径
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/20219362/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
List files in folder and subfolder with path to .txt file
提问by manish449
I have an excel sheet that has a cell that contains the path to a directory, i want a macro that searches the directory and any sub directories and lists the files in a .txt file, with the full path of each file.
我有一个 Excel 工作表,其中包含一个包含目录路径的单元格,我想要一个宏来搜索目录和任何子目录并列出 .txt 文件中的文件,以及每个文件的完整路径。
This is currently what i have found that looks like it should find the files except the path is hard-coded and it doesn't do anything with the results.
这是目前我发现的看起来应该找到文件的文件,除了路径是硬编码的并且它对结果没有任何作用。
Any ideas how i can change it to fit my needs?
有什么想法可以改变它以满足我的需求吗?
Sub LoopThroughFiles()
Dim StrFile As String
StrFile = Dir("C:\Work\NCL\nCLs\histogram_addition\TestData\Input\RTE\")
Do While Len(StrFile) > 0
Debug.Print StrFile
StrFile = Dir
Loop
End Sub
回答by rheitzman
Here's a method cobbled together from the FileSystemObject() examples using a recursive call. Apply a sort to the results if needed. You can also filter by .txt extension using other FileSystemObject() methods:
这是使用递归调用从 FileSystemObject() 示例拼凑而成的方法。如果需要,对结果应用排序。您还可以使用其他 FileSystemObject() 方法按 .txt 扩展名过滤:
Sub Sample()
ShowFolderList ("C:\temp")
End Sub
Sub ShowFolderList(folderspec)
Dim fs, f, f1, fc, s, sFldr
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.SubFolders
For Each f1 In fc
If Right(f1, 1) <> "\" Then ShowFolderList f1 & "\" Else ShowFolderList f1
Next
Set fc = f.Files
For Each f1 In fc
Debug.Print folderspec & f1.Name
Next
End Sub
Write to file:
写入文件:
Option Explicit
Dim file As Object
Dim fs As Object
Sub go()
Set fs = CreateObject("Scripting.FileSystemObject")
Set file = fs.OpenTextFile("C:\temp2\results3.txt", 2, True) ' 2=ForWriting, replace
ShowFolderList "C:\temp\"
file.Close
MsgBox "done"
End Sub
Sub ShowFolderList(folderspec)
On Error GoTo local_err
Dim f, f1, fc, s, sFldr
Set f = fs.GetFolder(folderspec)
Set fc = f.SubFolders
For Each f1 In fc
If Right(f1, 1) <> "\" Then ShowFolderList f1 & "\" Else ShowFolderList f1
Next
Set fc = f.Files
For Each f1 In fc
file.writeline folderspec & f1.Name
Next
local_exit:
Exit Sub
local_err:
MsgBox Err & " " & Err.Description
Resume local_exit
Resume
End Sub