VBA遍历所有文件夹的两种方法(filesearch和FileSystemObject)

垃圾百度文库,非说我文档质量低,害我复制这么多,双横线以后是复制品,大家不用看这段代码是我在网上找到的,速度很快,有需要的朋友保存下吧执行前先加个command,一■个listbox'遍历文件夹Private Sub ShowFolderList(folderspec)Dim fs, f, f1, s, sfDim hs, h, h1, hfSet fs = CreateObject("Scripting.FileSystemObject")Set f = fs.GetFolder(folderspec)Set sf = f.SubFoldersFor Each f1 In sfList1.AddItem folderspec & "\" & Call ShowFolderList(folderspec & "\" & )NextEnd Sub'遍历某文件夹下的文件Private Sub Showfilelist(folderspec)Dim fs, f, f1, fc, sSet fs = CreateObject("Scripting.FileSystemObject")Set f = fs.GetFolder(folderspec)Set fc = f.FilesFor Each f1 In fcList1.AddItem NextEnd Sub'遍历某文件夹及子文件夹中的所有文件Sub SosuoFile(MyPath As String)Dim Myname As StringDim a As StringDim B() As StringDim dir_i() As StringDim i, idir As LongIf Right(MyPath, 1) <> "\" Then MyPath = MyPath + "\"Myname = Dir(MyPath, vbDirectory Or vbHidden Or vbNormal Or vbReadOnly)Do While Myname <> ""If Myname <> "." And Myname <> ".." ThenIf (GetAttr(MyPath & Myname) And vbDirectory) = vbDirectory Then '如果找到的是目录idir= idir + 1ReDim Preserve dir_i(idir) As Stringdir_i(idir - 1) = MynameElseList1.AddItem MyPath & Myname '把找到的文件显示到列表框中End IfEnd IfMyname = Dir '搜索下一项LoopFor i = 0 To idir - 1Call SosuoFile(MyPath + dir_i(i))Next iReDim dir_i(0) As StringEnd Sub'附:'在这里可以处理目录中的文件' '得到文件名'Fn.Size '得到文件大小'Fn.Path '得到文件路径'Fn.Type '得到文件类型'Fn.DateLastModified '得到文件最后的修改日期'调用方法'ShowFolderList ("c:\a") 查找目录'Showfilelist ("c:\a") 查找文件'SosuoFile ("c:\a")查找目录+文件'遍历文件夹Private Sub ShowFolderList(folderspec)Dim fs, f, f1, s, sfDim hs, h, h1, hfSet fs = CreateObject("Scripting.FileSystemObject")Set f = fs.GetFolder(folderspec)Set sf = f.SubFoldersFor Each f1 In sfList1.AddItem folderspec & "\" & Call ShowFolderList(folderspec & "\" & ) NextEnd Sub'遍历某文件夹下的文件Private Sub Showfilelist(folderspec)Dim fs, f, f1, fc, sSet fs = CreateObject("Scripting.FileSystemObject")Set f = fs.GetFolder(folderspec)Set fc = f.FilesFor Each f1 In fcList1.AddItem NextEnd Sub'遍历某文件夹及子文件夹中的所有文件Sub SosuoFile(MyPath As String)Dim Myname As StringDim a As StringDim B() As StringDim dir_i() As StringDim i, idir As LongIf Right(MyPath, 1) <> "\" Then MyPath = MyPath + "\"Myname = Dir(MyPath, vbDirectory Or vbHidden Or vbNormal Or vbReadOnly)Do While Myname <> ""If Myname <> "." And Myname <> ".." ThenIf (GetAttr(MyPath & Myname) And vbDirectory) = vbDirectory Then '如果找到的是目录idir = idir + 1ReDim Preserve dir_i(idir) As Stringdir_i(idir - 1) = MynameElseList1.AddItem MyPath & Myname '把找到的文件显示到列表框中End IfEnd IfMyname = Dir '搜索下一项LoopFor i = 0 To idir - 1Call SosuoFile(MyPath + dir_i(i))Next iReDim dir_i(0) As StringEnd Sub'附:'在这里可以处理目录中的文件' '得到文件名'Fn.Size '得到文件大小'Fn.Path '得到文件路径'Fn.Type '得到文件类型'Fn.DateLastModified '得到文件最后的修改日期'调用方法'ShowFolderList ("c:\a") 查找目录'Showfilelist ("c:\a") 查找文件'SosuoFile ("c:\a") 查找目录+文件 '遍历文件夹Private Sub ShowFolderList(folderspec)Dim fs, f, f1, s, sfDim hs, h, h1, hfSet fs = CreateObject("Scripting.FileSystemObject")Set f = fs.GetFolder(folderspec)Set sf = f.SubFoldersFor Each f1 In sfList1.AddItem folderspec & "\" & Call ShowFolderList(folderspec & "\" & ) NextEnd Sub'遍历某文件夹下的文件Private Sub Showfilelist(folderspec)Dim fs, f, f1, fc, sSet fs = CreateObject("Scripting.FileSystemObject")Set f = fs.GetFolder(folderspec)Set fc = f.FilesFor Each f1 In fcList1.AddItem NextEnd Sub'遍历某文件夹及子文件夹中的所有文件Sub SosuoFile(MyPath As String)Dim Myname As String Dim a As String Dim B() As String Dim dir_i() As String Dim i, idir As LongIf Right(MyPath, 1) <> "\" Then MyPath = MyPath + "\"Myname = Dir(MyPath, vbDirectory Or vbHidden Or vbNormal Or vbReadOnly) Do While Myname <> ""If Myname <> "." And Myname <> ".." ThenIf (GetAttr(MyPath & Myname) And vbDirectory) = vbDirectory Then '如果找到的是目录 idir = idir + 1ReDim Preserve dir_i(idir) As String dir_i(idir - 1) = Myname ElseList1.AddItem MyPath & Myname '把找到的文件显示到列表框中End If End IfMyname = Dir '搜索下一项 LoopFor i = 0 To idir - 1Call SosuoFile(MyPath + dir_i(i)) Next iReDim dir_i(0) As String End Sub'附:'在这里可以处理目录中的文件'得到文件名 '得到文件大小 '得到文件路径 '得到文件类型'调用方法'ShowFolderList ("c:\a") 查找目录 'Showfilelist ("c:\a") 查找文件'SosuoFile ("c:\a") 查找目录+文件 '遍历文件夹 Private Sub ShowFolderList(folderspec)Dim fs, f, f1, s, sf Dim hs, h, h1, hfSet fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFolder(folderspec) Set sf = f.SubFolders For Each f1 In sfList1.AddItem folderspec & "\" & ' 'Fn.Size 'Fn.Path 'Fn.Type'Fn.DateLastModified'得到文件最后的修改日期Call ShowFolderList(folderspec & "\" & )NextEnd Sub'遍历某文件夹下的文件Private Sub Showfilelist(folderspec)Dim fs, f, f1, fc, sSet fs = CreateObject("Scripting.FileSystemObject")Set f = fs.GetFolder(folderspec)Set fc = f.FilesFor Each f1 In fcList1.AddItem NextEnd Sub'遍历某文件夹及子文件夹中的所有文件Sub SosuoFile(MyPath As String)Dim Myname As StringDim a As StringDim B() As StringDim dir_i() As StringDim i, idir As LongIf Right(MyPath, 1) <> "\" Then MyPath = MyPath + "\"Myname = Dir(MyPath, vbDirectory Or vbHidden Or vbNormal Or vbReadOnly)Do While Myname <> ""If Myname <> "." And Myname <> ".." ThenIf (GetAttr(MyPath & Myname) And vbDirectory) = vbDirectory Then '如果找到的是目录idir = idir + 1ReDim Preserve dir_i(idir) As Stringdir_i(idir - 1) = MynameElseList1.AddItem MyPath & Myname '把找到的文件显示到列表框中End IfEnd IfMyname = Dir '搜索下一项LoopFor i = 0 To idir - 1Call SosuoFile(MyPath + dir_i(i))Next iReDim dir_i(0) As StringEnd Sub'附:'在这里可以处理目录中的文件' '得到文件名'Fn.Size '得到文件大小'Fn.Path '得到文件路径'Fn.Type '得到文件类型'Fn.DateLastModified '得到文件最后的修改日期'调用方法'ShowFolderList ("c:\a") 查找目录'Showfilelist ("c:\a") 查找文件'SosuoFile ("c:\a") 查找目录+文件 '遍历文件夹Private Sub ShowFolderList(folderspec)Dim fs, f, f1, s, sfDim hs, h, h1, hfSet fs = CreateObject("Scripting.FileSystemObject")Set f = fs.GetFolder(folderspec)Set sf = f.SubFoldersFor Each f1 In sfList1.AddItem folderspec & "\" & Call ShowFolderList(folderspec & "\" & ) NextEnd Sub'遍历某文件夹下的文件Private Sub Showfilelist(folderspec)Dim fs, f, f1, fc, sSet fs = CreateObject("Scripting.FileSystemObject")Set f = fs.GetFolder(folderspec)Set fc = f.FilesFor Each f1 In fcList1.AddItem NextEnd Sub'遍历某文件夹及子文件夹中的所有文件Sub SosuoFile(MyPath As String)Dim Myname As StringDim a As StringDim B() As StringDim dir_i() As StringDim i, idir As LongIf Right(MyPath, 1) <> "\" Then MyPath = MyPath + "\"Myname = Dir(MyPath, vbDirectory Or vbHidden Or vbNormal Or vbReadOnly)Do While Myname <> ""If Myname <> "." And Myname <> ".." ThenIf (GetAttr(MyPath & Myname) And vbDirectory) = vbDirectory Then '如果找到的是目录idir = idir + 1ReDim Preserve dir_i(idir) As Stringdir_i(idir - 1) = MynameElseList1.AddItem MyPath & Myname '把找到的文件显示到列表框中End IfEnd IfMyname = Dir '搜索下一项LoopFor i = 0 To idir - 1Call SosuoFile(MyPath + dir_i(i))Next iReDim dir_i(0) As StringEnd Sub'附:'在这里可以处理目录中的文件' '得到文件名'Fn.Size '得到文件大小'Fn.Path '得到文件路径'Fn.Type '得到文件类型'得到文件最后的修改日期'Fn.DateLastModified'调用方法'ShowFolderList ("c:\a") 查找目录'Showfilelist ("c:\a") 查找文件'SosuoFile ("c:\a") 查找目录+文件 '遍历文件夹Private Sub ShowFolderList(folderspec)Dim fs, f, f1, s, sfDim hs, h, h1, hfSet fs = CreateObject("Scripting.FileSystemObject")Set f = fs.GetFolder(folderspec)Set sf = f.SubFoldersFor Each f1 In sfList1.AddItem folderspec & "\" & Call ShowFolderList(folderspec & "\" & )NextEnd Sub'遍历某文件夹下的文件Private Sub Showfilelist(folderspec)Dim fs, f, f1, fc, sSet fs = CreateObject("Scripting.FileSystemObject")Set f = fs.GetFolder(folderspec)Set fc = f.FilesFor Each f1 In fcList1.AddItem NextEnd Sub'遍历某文件夹及子文件夹中的所有文件Sub SosuoFile(MyPath As String)Dim Myname As StringDim a As StringDim B() As StringDim dir_i() As StringDim i, idir As LongIf Right(MyPath, 1) <> "\" Then MyPath = MyPath + "\"Myname = Dir(MyPath, vbDirectory Or vbHidden Or vbNormal Or vbReadOnly)Do While Myname <> ""If Myname <> "." And Myname <> ".." ThenIf (GetAttr(MyPath & Myname) And vbDirectory) = vbDirectory Then '如果找到的是目录idir = idir + 1ReDim Preserve dir_i(idir) As Stringdir_i(idir - 1) = MynameElseList1.AddItem MyPath & Myname '把找到的文件显示到列表框中End IfEnd IfMyname = Dir '搜索下一项LoopFor i = 0 To idir - 1Call SosuoFile(MyPath + dir_i(i))Next iReDim dir_i(0) As StringEnd Sub'附:'在这里可以处理目录中的文件' '得到文件名'Fn.Size '得到文件大小'Fn.Path '得到文件路径'Fn.Type '得到文件类型'Fn.DateLastModified '得到文件最后的修改日期'调用方法'ShowFolderList ("c:\a") 查找目录'Showfilelist ("c:\a") 查找文件'SosuoFile ("c:\a") 查找目录+文件 '遍历文件夹Private Sub ShowFolderList(folderspec)Dim fs, f, f1, s, sfDim hs, h, h1, hfSet fs = CreateObject("Scripting.FileSystemObject")Set f = fs.GetFolder(folderspec)Set sf = f.SubFoldersFor Each f1 In sfList1.AddItem folderspec & "\" & Call ShowFolderList(folderspec & "\" & ) Next End Sub'遍历某文件夹下的文件Private Sub Showfilelist(folderspec)Dim fs, f, f1, fc, sSet fs = CreateObject("Scripting.FileSystemObject")Set f = fs.GetFolder(folderspec)Set fc = f.FilesFor Each f1 In fcList1.AddItem NextEnd Sub'遍历某文件夹及子文件夹中的所有文件Sub SosuoFile(MyPath As String)Dim Myname As StringDim a As StringDim B() As StringDim dir_i() As StringDim i, idir As LongIf Right(MyPath, 1) <> "\" Then MyPath = MyPath + "\"Myname = Dir(MyPath, vbDirectory Or vbHidden Or vbNormal Or vbReadOnly)Do While Myname <> ""If Myname <> "." And Myname <> ".." ThenIf (GetAttr(MyPath & Myname) And vbDirectory) = vbDirectory Then '如果找到的是目录idir = idir + 1ReDim Preserve dir_i(idir) As Stringdir_i(idir - 1) = MynameElseList1.AddItem MyPath & Myname '把找到的文件显示到列表框中End IfEnd IfMyname = Dir '搜索下一项LoopFor i = 0 To idir - 1Call SosuoFile(MyPath + dir_i(i))Next iReDim dir_i(0) As String End Sub'附:'在这里可以处理目录中的文件' '得到文件名'Fn.Size '得到文件大小'Fn.Path '得到文件路径'Fn.Type '得到文件类型'Fn.DateLastModified '得到文件最后的修改日期'调用方法'ShowFolderList ("c:\a") 查找目录'Showfilelist ("c:\a") 查找文件'SosuoFile ("c:\a") 查找目录+文件 '遍历文件夹Private Sub ShowFolderList(folderspec)Dim fs, f, f1, s, sfDim hs, h, h1, hfSet fs = CreateObject("Scripting.FileSystemObject")Set f = fs.GetFolder(folderspec)Set sf = f.SubFoldersFor Each f1 In sfList1.AddItem folderspec & "\" & Call ShowFolderList(folderspec & "\" & ) NextEnd Sub'遍历某文件夹下的文件Private Sub Showfilelist(folderspec)Dim fs, f, f1, fc, sSet fs = CreateObject("Scripting.FileSystemObject")Set f = fs.GetFolder(folderspec)Set fc = f.FilesFor Each f1 In fcList1.AddItem NextEnd Sub'遍历某文件夹及子文件夹中的所有文件Sub SosuoFile(MyPath As String)Dim Myname As StringDim a As StringDim B() As StringDim dir_i() As StringDim i, idir As LongIf Right(MyPath, 1) <> "\" Then MyPath = MyPath + "\"Myname = Dir(MyPath, vbDirectory Or vbHidden Or vbNormal Or vbReadOnly)Do While Myname <> ""If Myname <> "." And Myname <> ".." ThenIf (GetAttr(MyPath & Myname) And vbDirectory) = vbDirectory Then '如果找到的是目录idir = idir + 1ReDim Preserve dir_i(idir) As Stringdir_i(idir - 1) = MynameElseList1.AddItem MyPath & Myname '把找到的文件显示到列表框中End IfEnd IfMyname = Dir '搜索下一项LoopFor i = 0 To idir - 1Call SosuoFile(MyPath + dir_i(i))Next iReDim dir_i(0) As StringEnd Sub'附:'在这里可以处理目录中的文件' '得到文件名'Fn.Size '得到文件大小'Fn.Path '得到文件路径'Fn.Type '得到文件类型'Fn.DateLastModified '得到文件最后的修改日期'调用方法'ShowFolderList ("c:\a") 查找目录'Showfilelist ("c:\a") 查找文件'SosuoFile ("c:\a") 查找目录+文件'遍历文件夹Private Sub ShowFolderList(folderspec)Dim fs, f, f1, s, sfDim hs, h, h1, hfSet fs = CreateObject("Scripting.FileSystemObject")Set f = fs.GetFolder(folderspec)Set sf = f.SubFoldersFor Each f1 In sfList1.AddItem folderspec & "\" & Call ShowFolderList(folderspec & "\" & ) Next End Sub'遍历某文件夹下的文件Private Sub Showfilelist(folderspec)Dim fs, f, f1, fc, sSet fs = CreateObject("Scripting.FileSystemObject")Set f = fs.GetFolder(folderspec)Set fc = f.FilesFor Each f1 In fcList1.AddItem NextEnd Sub'遍历某文件夹及子文件夹中的所有文件Sub SosuoFile(MyPath As String)Dim Myname As StringDim a As StringDim B() As StringDim dir_i() As StringDim i, idir As LongIf Right(MyPath, 1) <> "\" Then MyPath = MyPath + "\"Myname = Dir(MyPath, vbDirectory Or vbHidden Or vbNormal Or vbReadOnly)Do While Myname <> ""If Myname <> "." And Myname <> ".." ThenIf (GetAttr(MyPath & Myname) And vbDirectory) = vbDirectory Then '如果找到的是目录idir = idir + 1ReDim Preserve dir_i(idir) As Stringdir_i(idir - 1) = MynameElseList1.AddItem MyPath & Myname '把找到的文件显示到列表框中End IfEnd IfMyname = Dir '搜索下一项LoopFor i = 0 To idir - 1Call SosuoFile(MyPath + dir_i(i))Next iReDim dir_i(0) As StringEnd Sub'附:'在这里可以处理目录中的文件' '得到文件名'Fn.Size '得到文件大小'Fn.Path '得到文件路径'Fn.Type '得到文件类型'Fn.DateLastModified '得到文件最后的修改日期'调用方法'ShowFolderList ("c:\a") 查找目录'Showfilelist ("c:\a") 查找文件'SosuoFile ("c:\a") 查找目录+文件 '遍历文件夹Private Sub ShowFolderList(folderspec)Dim fs, f, f1, s, sfDim hs, h, h1, hfSet fs = CreateObject("Scripting.FileSystemObject")Set f = fs.GetFolder(folderspec)Set sf = f.SubFoldersFor Each f1 In sfList1.AddItem folderspec & "\" & Call ShowFolderList(folderspec & "\" & )NextEnd Sub'遍历某文件夹下的文件Private Sub Showfilelist(folderspec)Dim fs, f, f1, fc, sSet fs = CreateObject("Scripting.FileSystemObject")Set f = fs.GetFolder(folderspec)Set fc = f.FilesFor Each f1 In fcList1.AddItem NextEnd Sub'遍历某文件夹及子文件夹中的所有文件Sub SosuoFile(MyPath As String)Dim Myname As StringDim a As StringDim B() As StringDim dir_i() As StringDim i, idir As LongIf Right(MyPath, 1) <> "\" Then MyPath = MyPath + "\"Myname = Dir(MyPath, vbDirectory Or vbHidden Or vbNormal Or vbReadOnly)Do While Myname <> ""If Myname <> "." And Myname <> ".." ThenIf (GetAttr(MyPath & Myname) And vbDirectory) = vbDirectory Then '如果找到的是目录idir = idir + 1ReDim Preserve dir_i(idir) As Stringdir_i(idir - 1) = MynameElseList1.AddItem MyPath & Myname '把找到的文件显示到列表框中End IfEnd IfMyname = Dir '搜索下一项LoopFor i = 0 To idir - 1Call SosuoFile(MyPath + dir_i(i))Next iReDim dir_i(0) As StringEnd Sub'附:'在这里可以处理目录中的文件' '得到文件名'Fn.Size '得到文件大小'Fn.Path '得到文件路径'Fn.Type '得到文件类型'Fn.DateLastModified '得到文件最后的修改日期'调用方法'ShowFolderList ("c:\a") 查找目录'Showfilelist ("c:\a") 查找文件'SosuoFile ("c:\a") 查找目录+文件 '遍历文件夹Private Sub ShowFolderList(folderspec)Dim fs, f, f1, s, sfDim hs, h, h1, hfSet fs = CreateObject("Scripting.FileSystemObject")Set f = fs.GetFolder(folderspec)Set sf = f.SubFoldersFor Each f1 In sfList1.AddItem folderspec & "\" & Call ShowFolderList(folderspec & "\" & ) Next End Sub'遍历某文件夹下的文件Private Sub Showfilelist(folderspec)Dim fs, f, f1, fc, sSet fs = CreateObject("Scripting.FileSystemObject")Set f = fs.GetFolder(folderspec)Set fc = f.FilesFor Each f1 In fcList1.AddItem NextEnd Sub'遍历某文件夹及子文件夹中的所有文件Sub SosuoFile(MyPath As String)Dim Myname As StringDim a As StringDim B() As StringDim dir_i() As StringDim i, idir As LongIf Right(MyPath, 1) <> "\" Then MyPath = MyPath + "\"Myname = Dir(MyPath, vbDirectory Or vbHidden Or vbNormal Or vbReadOnly) Do While Myname <> ""If Myname <> "." And Myname <> ".." ThenIf (GetAttr(MyPath & Myname) And vbDirectory) = vbDirectory Then '如果找到的是目录 idir = idir + 1ReDim Preserve dir_i(idir) As String dir_i(idir - 1) = Myname ElseList1.AddItem MyPath & Myname '把找到的文件显示到列表框中End If End IfMyname = Dir '搜索下一项 LoopFor i = 0 To idir - 1Call SosuoFile(MyPath + dir_i(i)) Next iReDim dir_i(0) As String End Sub'附:'在这里可以处理目录中的文件'得到文件名 '得到文件大小 '得到文件路径 '得到文件类型'调用方法'ShowFolderList ("c:\a") 查找目录 'Showfilelist ("c:\a") 查找文件'SosuoFile ("c:\a") 查找目录+文件 '遍历文件夹Private Sub ShowFolderList(folderspec)Dim fs, f, f1, s, sf Dim hs, h, h1, hfSet fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFolder(folderspec) Set sf = f.SubFolders For Each f1 In sfList1.AddItem folderspec & "\" & Call ShowFolderList(folderspec & "\" & )Next End Sub' 'Fn.Size 'Fn.Path 'Fn.Type'Fn.DateLastModified '得到文件最后的修改日期'遍历某文件夹下的文件Private Sub Showfilelist(folderspec)Dim fs, f, f1, fc, sSet fs = CreateObject("Scripting.FileSystemObject")Set f = fs.GetFolder(folderspec)Set fc = f.FilesFor Each f1 In fcList1.AddItem NextEnd Sub'遍历某文件夹及子文件夹中的所有文件Sub SosuoFile(MyPath As String)Dim Myname As StringDim a As StringDim B() As StringDim dir_i() As StringDim i, idir As LongIf Right(MyPath, 1) <> "\" Then MyPath = MyPath + "\"Myname = Dir(MyPath, vbDirectory Or vbHidden Or vbNormal Or vbReadOnly)Do While Myname <> ""If Myname <> "." And Myname <> ".." ThenIf (GetAttr(MyPath & Myname) And vbDirectory) = vbDirectory Then '如果找到的是目录idir = idir + 1ReDim Preserve dir_i(idir) As Stringdir_i(idir - 1) = MynameElseList1.AddItem MyPath & Myname '把找到的文件显示到列表框中End IfEnd IfMyname = Dir '搜索下一项LoopFor i = 0 To idir - 1Call SosuoFile(MyPath + dir_i(i))Next iReDim dir_i(0) As StringEnd Sub'附:'在这里可以处理目录中的文件' '得到文件名'Fn.Size '得到文件大小'Fn.Path '得到文件路径'Fn.Type '得到文件类型'Fn.DateLastModified '得到文件最后的修改日期'调用方法'ShowFolderList ("c:\a") 查找目录'Showfilelist ("c:\a") 查找文件'SosuoFile ("c:\a") 查找目录+文件 '遍历文件夹Private Sub ShowFolderList(folderspec)Dim fs, f, f1, s, sfDim hs, h, h1, hfSet fs = CreateObject("Scripting.FileSystemObject")Set f = fs.GetFolder(folderspec)Set sf = f.SubFoldersFor Each f1 In sfList1.AddItem folderspec & "\" & Call ShowFolderList(folderspec & "\" & ) Next End Sub'遍历某文件夹下的文件Private Sub Showfilelist(folderspec)Dim fs, f, f1, fc, sSet fs = CreateObject("Scripting.FileSystemObject")Set f = fs.GetFolder(folderspec)Set fc = f.FilesFor Each f1 In fcList1.AddItem Next End Sub'遍历某文件夹及子文件夹中的所有文件Sub SosuoFile(MyPath As String) Dim Myname As String Dim a As String Dim B() As String Dim dir_i() As String Dim i, idir As LongIf Right(MyPath, 1) <> "\" Then MyPath = MyPath + "\"Myname = Dir(MyPath, vbDirectory Or vbHidden Or vbNormal Or vbReadOnly) Do While Myname <> ""If Myname <> "." And Myname <> ".." ThenIf (GetAttr(MyPath & Myname) And vbDirectory) = vbDirectory Then '如果找到的是目录 idir = idir + 1ReDim Preserve dir_i(idir) As String dir_i(idir - 1) = Myname ElseList1.AddItem MyPath & Myname '把找到的文件显示到列表框中End If End IfMyname = Dir '搜索下一项 LoopFor i = 0 To idir - 1Call SosuoFile(MyPath + dir_i(i)) Next iReDim dir_i(0) As String End Sub'附:'在这里可以处理目录中的文件'得到文件名 '得到文件大小 '得到文件路径 '得到文件类型'调用方法'ShowFolderList ("c:\a") 查找目录' 'Fn.Size 'Fn.Path 'Fn.Type'Fn.DateLastModified'得到文件最后的修改日期'Showfilelist ("c:\a") 查找文件'SosuoFile ("c:\a") 查找目录+文件 '遍历文件夹Private Sub ShowFolderList(folderspec)Dim fs, f, f1, s, sfDim hs, h, h1, hfSet fs = CreateObject("Scripting.FileSystemObject")Set f = fs.GetFolder(folderspec)Set sf = f.SubFoldersFor Each f1 In sfList1.AddItem folderspec & "\" & Call ShowFolderList(folderspec & "\" & )NextEnd Sub'遍历某文件夹下的文件Private Sub Showfilelist(folderspec)Dim fs, f, f1, fc, sSet fs = CreateObject("Scripting.FileSystemObject")Set f = fs.GetFolder(folderspec)Set fc = f.FilesFor Each f1 In fcList1.AddItem NextEnd Sub'遍历某文件夹及子文件夹中的所有文件Sub SosuoFile(MyPath As String)Dim Myname As StringDim a As StringDim B() As StringDim dir_i() As StringDim i, idir As LongIf Right(MyPath, 1) <> "\" Then MyPath = MyPath + "\"Myname = Dir(MyPath, vbDirectory Or vbHidden Or vbNormal Or vbReadOnly)Do While Myname <> ""If Myname <> "." And Myname <> ".." ThenIf (GetAttr(MyPath & Myname) And vbDirectory) = vbDirectory Then '如果找到的是目录idir = idir + 1ReDim Preserve dir_i(idir) As String dir_i(idir - 1) = MynameElseList1.AddItem MyPath & Myname '把找到的文件显示到列表框中End IfEnd IfMyname = Dir '搜索下一项LoopFor i = 0 To idir - 1Call SosuoFile(MyPath + dir_i(i))Next iReDim dir_i(0) As StringEnd Sub'附:'在这里可以处理目录中的文件' '得到文件名'Fn.Size '得到文件大小'Fn.Path '得到文件路径'Fn.Type '得到文件类型'Fn.DateLastModified '得到文件最后的修改日期'调用方法'ShowFolderList ("c:\a") 查找目录'Showfilelist ("c:\a") 查找文件'SosuoFile ("c:\a") 查找目录+文件 '遍历文件夹Private Sub ShowFolderList(folderspec)Dim fs, f, f1, s, sfDim hs, h, h1, hfSet fs = CreateObject("Scripting.FileSystemObject")Set f = fs.GetFolder(folderspec)Set sf = f.SubFoldersFor Each f1 In sfList1.AddItem folderspec & "\" & Call ShowFolderList(folderspec & "\" & ) NextEnd Sub'遍历某文件夹下的文件Private Sub Showfilelist(folderspec)Dim fs, f, f1, fc, sSet fs = CreateObject("Scripting.FileSystemObject")Set f = fs.GetFolder(folderspec)Set fc = f.FilesFor Each f1 In fcList1.AddItem NextEnd Sub'遍历某文件夹及子文件夹中的所有文件Sub SosuoFile(MyPath As String)Dim Myname As StringDim a As StringDim B() As StringDim dir_i() As StringDim i, idir As LongIf Right(MyPath, 1) <> "\" Then MyPath = MyPath + "\"Myname = Dir(MyPath, vbDirectory Or vbHidden Or vbNormal Or vbReadOnly)Do While Myname <> ""If Myname <> "." And Myname <> ".." ThenIf (GetAttr(MyPath & Myname) And vbDirectory) = vbDirectory Then '如果找到的是目录idir = idir + 1ReDim Preserve dir_i(idir) As Stringdir_i(idir - 1) = MynameElseList1.AddItem MyPath & Myname '把找到的文件显示到列表框中End IfEnd IfMyname = Dir '搜索下一项LoopFor i = 0 To idir - 1Call SosuoFile(MyPath + dir_i(i))Next iReDim dir_i(0) As StringEnd Sub'附:'在这里可以处理目录中的文件' '得到文件名'Fn.Size '得到文件大小'Fn.Path '得到文件路径'Fn.Type '得到文件类型'Fn.DateLastModified '得到文件最后的修改日期'调用方法'ShowFolderList ("c:\a") 查找目录'Showfilelist ("c:\a") 查找文件'SosuoFile ("c:\a") 查找目录+文件 '遍历文件夹Private Sub ShowFolderList(folderspec)Dim fs, f, f1, s, sfDim hs, h, h1, hfSet fs = CreateObject("Scripting.FileSystemObject")Set f = fs.GetFolder(folderspec)Set sf = f.SubFoldersFor Each f1 In sfList1.AddItem folderspec & "\" & Call ShowFolderList(folderspec & "\" & ) NextEnd Sub'遍历某文件夹下的文件Private Sub Showfilelist(folderspec)Dim fs, f, f1, fc, sSet fs = CreateObject("Scripting.FileSystemObject")Set f = fs.GetFolder(folderspec)Set fc = f.FilesFor Each f1 In fcList1.AddItem NextEnd Sub'遍历某文件夹及子文件夹中的所有文件Sub SosuoFile(MyPath As String)Dim Myname As StringDim a As StringDim B() As StringDim dir_i() As StringDim i, idir As LongIf Right(MyPath, 1) <> "\" Then MyPath = MyPath + "\"Myname = Dir(MyPath, vbDirectory Or vbHidden Or vbNormal Or vbReadOnly)Do While Myname <> ""If Myname <> "." And Myname <> ".." ThenIf (GetAttr(MyPath & Myname) And vbDirectory) = vbDirectory Then '如果找到的是目录idir = idir + 1ReDim Preserve dir_i(idir) As Stringdir_i(idir - 1) = MynameElse。

【VBA】遍历文件夹（含子文件夹）方法一、调用目标文件夹的方法1、Application.FileDialog方法Sub ListFilesT est()With Application.FileDialog(msoFileDialogFolderPicker) '运行后出现标准的选择文件夹对话框If .Show Then myPath = .SelectedItems(1) Else Exit Sub '如选中则返回=-1 / 取消未选则返回=0End WithIf Right(myPath, 1) <> '' Then myPath = myPath & '''返回的是选中目标文件夹的绝对路径,但除了本地C盘、D盘会以'C:'形式返回外，其余路径无''需要自己添加End Sub2、视窗浏览器界面选择目标文件夹Sub ListFilesT est()Set myFolder = CreateObject('Shell.Application').BrowseForFolder(0, 'GetFolder', 0)If Not myFolder Is Nothing Then myPath$ = myFolder.Items.Item.Path Else MsgBox 'Folder not Selected': Exit SubIf Right(myPath, 1) <> '' Then myPath = myPath & '''同样返回的是选中目标文件夹的绝对路径,但除了本地C盘、D盘会以'C:'形式返回外，其余路径无''需要添加End Sub二、仅列出所有文件不包括子文件夹、不包括子文件夹中的文件Sub ListFilesTest()With Application.FileDialog(msoFileDialogFolderPicker)If .Show ThenRight(myPath, 1) <> '' Then myPath = myPath & '''以上选择目标文件夹以得到路径myPath MsgBox ListFiles(myPath) '调用FSO的ListFiles过程返回目标文件夹下的所有文件名End SubFunction ListFiles(myPath$)Set fso = CreateObject('Scripting.FileSystemObject') '打开FSO脚本、建立FSO对象实例 For Each f In fso.GetFolder(myPath).Files '用FSO方法遍历指定文件夹内所有文件 i = i + 1: s = s & vbCr & '逐个列出文件名并统计文件个数 i Next ListFiles = i & ' Files:' & s '返回所有文件名的合并字符串End Function三、仅列出目标文件夹中所有子文件夹名不包括目标文件夹中文件、不包括子文件夹中的文件或子文件夹Sub ListFilesTest()With Application.FileDialog(msoFileDialogFolderPicker)If .Show Then myPath$ = .SelectedItems(1) Else Exit SubEnd WithIf Right(myPath, 1) <> '' Then myPath = myPath & '' MsgBox ListFolders(myPath)End SubFunction ListFolders(myPath$)Set fso = CreateObject('Scripting.FileSystemObject')For Each f In fso.GetFolder(myPath).SubFolders j = j + 1: t = t & vbCr & Next ListFolders = j & ' Folders:' & tEnd Functionfso.GetFolder(myPath).Filesfso.GetFolder(myPath).SubFolders四、遍历目标文件夹内所有文件、以及所有子文件夹中的所有文件【字典】Sub ListFilesTest() With Application.FileDialog(msoFileDialogFolderPicker) If .Show ThenRight(myPath, 1) <> '' Then myPath = myPath & ''MsgBox 'List Files:' & vbCr & Join(ListAllFsoDic(myPath), vbCr) MsgBox 'List SubFolders:' & vbCr & Join(ListAllFsoDic(myPath, 1), vbCr)End SubFunction ListAllFsoDic(myPath$, Optional k = 0) '使用2个字典但无需递归的遍历过程Dim i&, j& Set d1 = CreateObject('Scripting.Dictionary') '字典d1记录子文件夹的绝对路径名 Set d2 = CreateObject('Scripting.Dictionary') '字典d2记录文件名（文件夹和文件分开处理）d1(myPath) = '' '以当前路径myPath作为起始记录，以便开始循环检查Set fso = CreateObject('Scripting.FileSystemObject') Do While i < d1.Count '当字典1文件夹中有未遍历处理的key存在时进行Do循环直到 i=d1.Count即所有子文件夹都已处理时停止kr = d1.Keys '取出文件夹中所有的key即所有子文件夹路径（注意每次都要更新） For Each f In fso.GetFolder(kr(i)).Files '遍历该子文件夹中所有文件（注意仅从新的kr(i) 开始）j = j + 1: d2(j) = '把该子文件夹内的所有文件名作为字典Item项加入字典d2 (为防止文件重名不能用key属性) Nexti = i + 1 '已经处理过的子文件夹数目 i +1 （避免下次产生重复处理） For Each fd In fso.GetFolder(kr(i - 1)).SubFolders '遍历该文件夹中所有新的子文件夹 d1(fd.Path) = ' ' & & '' '把新的子文件夹路径存入字典d1以便在下一轮循环中处理 Next Loop If k Then ListAllFsoDic = d1.Keys Else ListAllFsoDic = d2.Items '如果参数=1则列出字典d1中所有子文件夹的路径名(如使用d1.Items则仅列出子文件夹名称不含路径) '如果参数=0则默认列出字典d2中Items即所有文件名 End Function【DIR】Sub ListAllDirDicTest() WithApplication.FileDialog(msoFileDialogFolderPicker) If .Show Then myPath$ = .SelectedItems(1) Else Exit Sub End With If Right(myPath, 1) <> '' Then myPath = myPath & ''MsgBox Join(ListAllDirDic(myPath), vbCr) 'GetAllSubFolder's File 列出目标文件夹内含子文件夹内所有文件MsgBox Join(ListAllDirDic(myPath, 1), vbCr) 'GetThisFolder's File 列出目标文件夹内所有文件(不含子文件夹)MsgBox Join(ListAllDirDic(myPath, -1), vbCr) 'GetThisFolder's SubFolder 仅列出目标文件夹内的子文件夹MsgBox Join(ListAllDirDic(myPath, -2), vbCr) 'GetAllSubFolder 列出目标文件夹内含子文件夹的所有子文件夹MsgBox Join(ListAllDirDic(myPath, 1, 'tst'), vbCr) 'GetThisFolder's SpecialFile 仅列出文件夹内含关键字文件 MsgBox Join(ListAllDirDic(myPath, , 'tst'), vbCr) 'GetAllSubFolder's SpecialFile 列出子文件夹内含关键字文件End SubFunction ListAllDirDic(myPath$, Optional sb& = 0, Optional SpFile$ = '') '利用Dir方法、以及用2个字典分别记录子文件夹路径和文件名的文件搜寻方法。

VBA批量处理文件和文件夹的技巧VBA（Visual Basic for Applications）是一种广泛应用于Microsoft Office套件中的编程语言，它可以帮助用户自动化和简化在Excel、Word、PowerPoint等应用中的重复操作。

对于需要处理大量文件和文件夹的任务，使用VBA可以大大提高效率。

本文将介绍一些VBA批量处理文件和文件夹的技巧，帮助用户在日常工作中更高效地完成相关任务。

1. 批量处理文件在VBA中，可以使用File System Object（文件系统对象）来访问和处理文件。

使用下面的代码可以批量处理指定文件夹中的所有文件：```vbaSub BatchProcessFiles()Dim FileSystem As ObjectDim SourceFolder As ObjectDim FileItem As Object' 设置源文件夹路径Set FileSystem = CreateObject("Scripting.FileSystemObject")Set SourceFolder = FileSystem.GetFolder("C:\SourceFolder")' 循环处理文件夹中的所有文件For Each FileItem In SourceFolder.Files' 在此处添加具体的文件处理逻辑' 例如，可以使用获取文件名' 若需要重新命名文件，可以使用 = "NewFileName"Next FileItem' 清除对象Set FileItem = NothingSet SourceFolder = NothingSet FileSystem = NothingEnd Sub```在上述代码中，我们首先使用`CreateObject("Scripting.FileSystemObject")`创建了一个文件系统对象，然后使用`GetFolder()`方法指定了源文件夹路径。

VBA遍历⽂件夹的三种⽅法VBA遍历⽂件夹常⽤有三种⽅法，这三种⽅法中,filesearch不适合2007和2010版本，⽽且速度⽐较慢，递归法速度也慢。

只有⽤DIR加循环的⽅法，速度飞快。

下⾯是三种⽅法的代码：1、filesearch法Sub test3()Dim wb As WorkbookDim i As LongDim tt = TimerWith Application.FileSearch '调⽤fileserch对象.NewSearch '开始新的搜索.LookIn = ThisWorkbook.path '设置搜索的路径.SearchSubFolders = True '搜索范围包括 LookIn 属性指定的⽂件夹中的所有⼦⽂件夹.Filename = "*.xls" '设置搜索的⽂件类型' .FileType = msoFileTypeExcelWorkbooksIf .Execute() > 0 Then '如果找到⽂件For i = 1 To .FoundFiles.Count'On Error Resume NextCells(i, 1) = .FoundFiles(i) '把找到的⽂件放在单元格⾥Next iElseMsgBox "没找到⽂件"End IfEnd WithMsgBox Timer - tEnd Sub2、递归法Sub Test()Dim iPath As String, i As LongDim tt = TimerWith Application.FileDialog(msoFileDialogFolderPicker).Title = "请选择要查找的⽂件夹"If .Show TheniPath = .SelectedItems(1)End IfEnd WithIf iPath = "False" Or Len(iPath) = 0 Then Exit Subi = 1Call GetFolderFile(iPath, i)MsgBox Timer - tMsgBox "⽂件名链接获取完毕。

VBA中的文件路径与文件名操作技巧VBA（Visual Basic for Applications）是一种广泛用于Microsoft Office应用程序中的编程语言。

在VBA中，文件路径和文件名操作是常见的需求，因为几乎所有的应用程序都需要与文件进行交互。

本文将介绍一些VBA中的文件路径和文件名操作技巧，帮助你更好地处理文件。

1. 获取文件路径获取文件路径是一个常见的需求，通常是为了打开文件、保存文件或对文件进行操作。

在VBA中，你可以使用FileSystemObject对象的GetAbsolutePathName方法来获取文件的绝对路径。

以下是一个示例代码：```Dim fso As ObjectSet fso = CreateObject("Scripting.FileSystemObject")Dim filePath As StringfilePath = fso.GetAbsolutePathName("filename.extension")```在这个示例代码中，你需要将"filename.extension"替换为你要操作的文件名和扩展名。

执行该代码后，`filePath`变量将保存文件的绝对路径。

另外，如果你想获取当前正在运行的VBA代码文件的路径，可以使用`ThisWorkbook.Path`方法。

这个方法提供了当前工作簿（Workbook）所在的目录路径。

2. 获取文件名获取文件名是另一个常见需求，有时你需要从文件路径中提取文件名。

VBA 提供了几种方法来获取文件名。

下面是两种常用的方法：方法一：使用FileSystemObject对象的GetFileName方法。

以下是一个示例代码：```Dim fso As ObjectSet fso = CreateObject("Scripting.FileSystemObject")Dim fileName As StringfileName = fso.GetFileName("C:\Folder\filename.extension")```在这个示例代码中，你需要将"C:\Folder\filename.extension"替换为你要操作的文件的绝对路径。

Excel：VBA 文件及文件夹操作2011年06月12日星期日 09:08VBA文件及文件夹操作1.VBA操作文件及文件夹on error resume next下测试A，在D：\下新建文件夹，命名为folder方法1：MkDir "D：\folder"方法2：Set abc = CreateObject("Scripting.FileSystemObject")abc.CreateFolder ("D：\folder")B，新建2个文件命名为a.xls和b.xlsWorkbooks.AddActiveWorkbook.SaveAs Filename：="D：\folder\a.xls"ActiveWorkbook.SaveAs Filename：="D：\folder\b.xls"C,创建新文件夹folder1并把a.xls复制到新文件夹重新命名为c.xlsMkDir "D：\folder1"FileCopy "D：\folder\a.xls", "D：\folder1\c.xls"D,复制folder中所有文件到folder1Set qqq = CreateObject("Scripting.FileSystemObject")qqq.CopyFolder "D：\folder", "D：\folder1"D,重命名a.xls为d.xlsname "d：\folder1\a.xls" as "d：\folder1\d.xls"E,判断文件及文件夹是否存在Set yyy = CreateObject("Scripting.FileSystemObject")If yyy.FolderExists("D：\folder1) = True Then ...If yyy.FileExists("D：\folder1\d.xls) = True Then ...F,打开folder1中所有文件Set rrr = CreateObject("Scripting.FileSystemObject")Set r = rrr.GetFolder("d：\folder1")For Each i In r.FilesWorkbooks.Open Filename：=("d：\folder1\" + + "")NextG,删除文件c.xlskill "d：\folder1\c.xls"H,删除文件夹folderSet aaa = CreateObject("Scripting.FileSystemObject")aaa.DeleteFolder "d：\folder"2.excel vba一次性获取文件夹下的所有文件名的方法小生今天上网下载了一个财务常用报表的文件包，里面有几百个excel工作表，要是手工一个一个的获得文件名的话，那我可是要忙十天半月哦。

VBA编程中的文件路径与文件夹操作详解VBA是一种用于Excel、Word、Access等微软Office软件的编程语言，它的强大功能可以帮助用户自动化操作、处理数据和创建自定义应用程序。

在VBA编程的过程中，文件路径和文件夹操作是非常重要的，它们可以帮助我们定位和管理需要处理的文件和文件夹。

本文将详细介绍VBA编程中文件路径和文件夹操作的相关知识。

一、文件路径的操作在VBA中，文件路径可以用于定位需要处理的文件。

我们可以使用文件路径操作打开、保存、复制、移动等文件操作。

下面是一些常用的文件路径操作：1. 获取文件路径使用VBA获取当前文件的路径可以通过ActiveWorkbook.Path来实现。

这个属性返回的是一个字符串，即当前文件的完整路径。

我们可以将此路径赋值给一个变量，以便后续的文件操作使用。

2. 获取特定文件路径如果我们需要获取特定文件的路径，可以使用VBA的FileDialog函数。

通过设置FileDialog的属性，我们可以选择特定的文件类型和过滤器，然后返回所选文件的路径。

例如，以下代码演示了如何打开选择文件对话框并获取文件路径：```Sub GetFilePath()Dim filePath As StringWith Application.FileDialog(msoFileDialogFilePicker).AllowMultiSelect = False.Title = "选择文件".Filters.Add "Excel文件", "*.xlsx"If .Show = True ThenfilePath = .SelectedItems(1)End IfEnd WithEnd Sub```3. 打开文件使用VBA打开文件可以通过Workbooks.Open方法实现。

我们需要提供文件的完整路径作为参数，然后可以对该文件进行操作。

vba filesearch 用法VBA的FileSearch对象是一个强大的工具，它可以帮助您在文件系统中搜索文件。

通过使用FileSearch对象，您可以基于各种标准（如文件名、文件大小、修改日期等）来查找文件。

以下是FileSearch对象的基本用法：创建一个FileSearch对象：首先，您需要创建一个FileSearch对象。

vbaDim fs As FileSearchSet fs = New FileSearch定义搜索标准：使用Criteria属性来定义搜索标准。

您可以设置多个条件，这些条件将与逻辑运算符（如AND、OR）结合使用。

vbaWith fs.RootFolder = "C:\YourFolder" '指定搜索的根文件夹.FileName = "*.txt" '搜索所有txt文件.Size = "*" '可以设置文件大小条件.DateLastModified = "1/1/2020" '设置修改日期条件End With执行搜索：使用Execute方法执行搜索。

这将返回一个FoundFiles集合，其中包含所有匹配的文件。

vbaDim results As FoundFilesSet results = fs.Execute处理搜索结果：您可以使用FoundFiles集合的属性和方法来处理搜索结果。

例如，可以使用Count属性来获取匹配文件的数量，使用Item方法来访问特定的匹配文件。

vbaIf results.Count > 0 ThenFor i = 1 To results.CountDebug.Print results.Item(i).Name '打印文件名Next iElseMsgBox "没有找到匹配的文件。

"End If清理资源：完成搜索后，确保释放FileSearch对象所使用的资源。

用VBA遍历指定文件夹里包括子文件夹里的所有文件如何用VBA遍历指定文件夹内的所有文件?如果仅仅是指定文件夹下的文件而不包括子文件夹内文件的话，那好办。

一个Do...While加上Dir就可以搞定。

要包括子文件夹，那就要费一番小功夫了。

网上没有找到用Dir的完美答案，所以参考网上的思路，根据自己的理解编了一个，以备后用。

主要还是利用两个字典对象及递归的思想。

------------------------------------------------Sub test()Dim startfolder As Stringstartfolder = "D:\starcraft\" '指定文件夹Set folderlist = CreateObject("scripting.dictionary")Set filelist = CreateObject("scripting.dictionary")i = 1folderlist.Add startfolder, ""Do While folderlist.Count > 0For Each FolderName In folderlist.keysfname = Dir(FolderName, vbDirectory)Do While fname <> ""If fname <> ".." And fname <> "." ThenIf GetAttr(FolderName & fname) And vbDirectory Thenfolderlist.Add FolderName & fname & "\", ""Elsefilelist.Add FolderName & fname, "" '这里列出的该文件的路径＋文件名End IfEnd Iffname = DirLoopfolderlist.Remove (FolderName)NextLoopFor Each arr In filelist.keys ‘将文件路径＋文件名放在当前工作表的A列Range("A" & i).Value = arri = i + 1NextEnd Sub。

不久前有个朋友让我帮忙写段代码，可以一次性处理指定文件夹下（包括子文件夹下）所有Word文档，花了点时间终于得出了如下的代码。

首先，打开Office的宏编辑器，选择“工具”-“引用”，选中下面两个对象：Microsoft Scripting RuntimeMicrosoft Script Control 1.0代码如下：Sub T est()EnumFiles "e:" ' 遍历e: 下的所有文件End SubSub EnumFiles(ByVal sPath As String)Dim fs As New FileSystemObjectGetFile fs.GetFolder(sPath)Set fs = NothingEnd SubSub GetFile(ByVal fldParent As Folder)Dim fldSub As Folder, fSub As FileFor Each fldSub In fldParent.SubFoldersGetFile fldSubNextFor Each fSub In fldParent.FilesIf UCase(Right(Trim(fSub), 3)) = "DOC" Then ' 文件类型判断Debug.Print fSubEnd IfNextSet fldSub = NothingSet fSub = NothingEnd Sub经过这段时间在Excel和Word下面的宏的使用发现，原来在Office中使用VBA也是可以完成很多工作的。

打开D盘TEST下所有.xls的文件.Sub test()Dim sFolder As StringDim wb As WorkbookDim i As LongWith Application.FileSearch.NewSearch.LookIn = "D:\test".SearchSubFolders = True.Filename = "*.xls".FileType = msoFileTypeExcelWorkbooksIf .Execute() > 0 ThenFor i = 1 To .FoundFiles.CountOn Error Resume NextSet wb = Workbooks.Open(Filename:=.FoundFiles(i))Next iElseMsgBox "Folder " & sFolder & " contains no required files"End IfEnd WithExit Sub给个测试下,至于add新的workbook，自己改下吧Sub test()PathName = "c:\test"+ "\*.xls"dirna = Dir(PathName)i = 1Do While dirna <> ""Cells(i, 1) = Left(dirna, Len(dirna) - 10) & ".xls"i = i + 1dirna = DirLoopEnd SubSub ChgName()Dim Fdlg As FileDialog, FlDim Fso, Fld, Fln, WkSet Fdlg = Application.FileDialog(msoFileDialogFolderPicker) With Fdlg.InitialFileName = ThisWorkbook.Path & "\"If .Show Then Fl = .SelectedItems(1)End WithSet Fso = CreateObject("Scripting.FileSystemObject")Set Fld = Fso.GetFolder(Fl)Set Fln = Fld.FilesFor Each Wk In Fln = Left(, Len() - 10) & ".xls" NextEnd Sub'引用Microsoft Scripting RuntimePrivate Sub Command1_Click()Dim pFolder As String, fso As New FileSystemObjectpFolder = "D:\12"Dim objFile, objFolderSet objFolder = fso.getFolder(pFolder)For Each objFile In objFolder.FilesDebug.Print objFile.PathNextFor Each objFolder In objFolder.SubFoldersDebug.Print objFolderNextEnd SubPrivate Sub Command1_Click()MyProc1 "d:\"End SubSub MyProc1(ByVal Folder As String)Dim fso As New FileSystemObjectDim objFile, objFolderSet objFolder = fso.getFolder(Folder)For Each objFile In objFolder.FilesMyProc2 objFile.PathNextFor Each objFolder In objFolder.SubFoldersMyProc1 objFolder '递归遍历整颗树NextEnd SubSub MyProc2(FileName As String)Debug.Print FileNameEnd Sub。