查找并列出文件名扩展名以包括子文件夹 [英] Find and List File Names Augment to Include Subfolders

问题描述

我有两个代码.将搜索并命名目录中的每个文件夹.另一个将列出单个文件夹中的文件和文件名.我对VBA不够熟练，无法解决这个问题，所以我需要StackOverflow！

I have two codes. One will search and name every folder within a directory. The other will list the files and file names within a single folder. I am not proficient enough with VBA to figure this out, so I need StackOverflow!

这是文件名列表程序:

Sub Example1()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer

'Create an instance of the FileSystemObject
    Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
    Set objFolder = objFSO.GetFolder("\\fc8fsp01\litho_recipe_amat_data")
    i = 1
'loops through each file in the directory and prints their names and path
    For Each objFile In objFolder.Files
    'print file name
        Cells(i + 1, 1) = objFile.Name
    'print file path
        Cells(i + 1, 2) = objFile.Path
        i = i + 1
Next objFile
End Sub

这是第二个将导航子文件夹以写入文件夹名称的代码:

Here is the second code that will navigate sub-folders to write folder names:

Option Explicit

Dim i As Long, j As Long
Dim searchfolders As Variant
Dim FileSystemObject

    Sub ListOfFolders()
        Dim LookInTheFolder As String

        i = 1
        LookInTheFolder = "\D: ' As you know; you should modificate this row.
        Set FileSystemObject = CreateObject("Scripting.FileSystemObject")
        For Each searchfolders In FileSystemObject.GetFolder(LookInTheFolder).SubFolders
            Cells(i, 1) = searchfolders
            i = i + 1
            SearchWithin searchfolders
        Next searchfolders

    End Sub

Sub SearchWithin(searchfolders)
        On Error GoTo exits
    For Each searchfolders In FileSystemObject.GetFolder(searchfolders).SubFolders
        j = UBound(Split(searchfolders, "\"))
        Cells(i, j) = searchfolders
        i = i + 1
        SearchWithin searchfolders
        Next searchfolders
exits:
End Sub

我需要一个代码，该代码将搜索所有子文件夹并列出所有包含的文件.请帮助D:

I need a code that will search all sub folders and list all files contained. Please help D:

推荐答案

由于我访问的某些文件夹出现在网络驱动器上时出现速度问题，因此我编写了一个使用Windows Shell 的小VBA程序dir 命令.使用适当的参数，这将返回基本目录中的所有文件.以及所有子文件夹和文件等.我将结果写到一个文本文件中，然后我将其读入Excel进行进一步处理.

Because of speed issues when some of the folders I was accessing were present on a network drive, I wrote a little VBA program that uses the Windows Shell dir command. With the proper arguments, this will return all the files in the base directory; as well as all the subfolders and files and so forth. I have it write the results to a text file, which I then read into Excel for further processing.

与使用VBA的DIR或FSO相比，当文件位于网络驱动器上时，运行速度大约快了五倍-在本地计算机上时不那么明显-但我将其作为另一种方法来展示.

Compared with using VBA's DIR or the FSO, this ran about five times faster when the files were on a network drive -- not so noticeable when on the local computer -- but I present it as another approach.

您必须设置对 Windows脚本宿主对象模型的引用. sDrive 和 sBasePath 用于设置起始文件夹名称. sFileList 是将结果写入文本文件的位置.

You must set a reference to Windows Script Host Object Model. sDrive and sBasePath are used to set the starting folder name. sFileList is where the results will be written into a text file.

/S 参数显示指定目录和所有子目录中的文件./B 参数导致省略标题信息和摘要

The /S argument Displays files in specified directory and all subdirectories. The /B argument results in omitting heading information and summary

如果运行 CMD.EXE 并在 dir 命令上寻求帮助，则会看到其他参数的说明.

If you run CMD.EXE and look for help on the dir command, you will see an explanation of the other arguments.

Public sDrive As String
Public sBasePath As String
Public Const sFileList As String = "C:\Users\Ron\FileList.txt"
Option Explicit
Sub GetDirTree()
    Dim WSH As WshShell
    Dim lErrCode As Long

Set WSH = New WshShell
lErrCode = WSH.Run("cmd.exe /c dir """ & sDrive & sBasePath & """/B /S >" & sFileList, 0, True)
If lErrCode <> 0 Then
    MsgBox ("Error in GetDirTree: Error Number: " & CStr(lErrCode))
    Stop
End If

End Sub

这篇关于查找并列出文件名扩展名以包括子文件夹的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！