循环浏览包含子文件夹的文件夹以查找excel文件

debugcn 发表于 Dev

帕维尔

我想循环浏览包含多个子文件夹的文件夹，以查找包含指定名称的excel文件，并对发现的excel文件进行处理。有什么建议可以实现吗？

我发现了这样的东西，但它不起作用：

Application.ScreenUpdating = False
 Application.DisplayAlerts = False

 With Application.FileSearch
 .NewSearch
 .LookIn = "C:\temp 1" ' your drive / directory here
 .SearchSubFolders = True
 .FileName = ".xls" ' all files ending in xls
 .FileType = msoFileTypeExcelWorkbooks
 If .Execute() > 0 Then
 ' how many files are there in the selected folder?
 MsgBox "There were " & .FoundFiles.Count & " file(s) found."

 For i = 1 To .FoundFiles.Count
 Workbooks.Open .FoundFiles(i), 0
 '
 ' code 
 '

 '
 ActiveWorkbook.Save
 ActiveWorkbook.Close
 Next i
 Else
 MsgBox "There were no files found."
 End If
 End With


 Application.ScreenUpdating = True
 Application.DisplayAlerts = True

 MsgBox "All Done!"

普雷斯顿

因此，仅针对指定目录中的文件：

https://msdn.microsoft.com/zh-CN/library/dk008ty4(v=vs.90).aspx

Dim MyFile, MyPath, MyName As String 
' Returns "WIN.INI" if it exists.
MyFile = Dir("C:\WINDOWS\WIN.INI")   

' Returns filename with specified extension. If more than one *.INI 
' file exists, the first file found is returned.
MyFile = Dir("C:\WINDOWS\*.INI")

' Call Dir again without arguments to return the next *.INI file in the 
' same directory.
MyFile = Dir()

' Return first *.TXT file, including files with a set hidden attribute.
MyFile = Dir("*.TXT", vbHidden)

' Display the names in C:\ that represent directories.
MyPath = "c:\"   ' Set the path.
MyName = Dir(MyPath, vbDirectory)   ' Retrieve the first entry.
Do While MyName <> ""   ' Start the loop.
      ' Use bitwise comparison to make sure MyName is a directory. 
      If (GetAttr(MyPath & MyName) And vbDirectory) = vbDirectory Then 
         ' Display entry only if it's a directory.
         MsgBox(MyName)
      End If   
   MyName = Dir()   ' Get next entry.
Loop

或对于所有子文件夹，请在此处查看：

使用VBA遍历所有子文件夹

Dim FileSystem As Object
Dim HostFolder As String

HostFolder = "C:\"

Set FileSystem = CreateObject("Scripting.FileSystemObject")
DoFolder FileSystem.GetFolder(HostFolder)

Sub DoFolder(Folder)
    Dim SubFolder
    For Each SubFolder In Folder.SubFolders
        DoFolder SubFolder
    Next
    Dim File
    For Each File In Folder.Files
        ' Operate on each file
    Next
End Sub

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。