资源描述：
《vba遍历文件夹的三种方法（the three methods of vba traversal folder）》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
1、vba遍历文件夹的三种方法（ThethreemethodsofVBAtraversalfolder）TherearethreekindsofcommonlyusedVBAfoldertraversalmethod,thethreemethods,FileSearchisnotsuitableforthe2007and2010versions,andthespeedisslow,therecursivemethodisslow.OnlybyDIRcyclicmethod,fastspeed.Hereisthecodeofthethreemetho
2、d:1,FileSearchSub(test3)DimWBAsWorkbookDimIAsLongDimtT=TimerWithApplication.FileSearch'fileserchobjectcalled.NewSearch'tostartanewsearch.LookIn=ThisWorkbook.path'setsearchpath.SearchSubFolders=True'searchincludesallsubfoldersinthefolderspecifiedbytheLookInpropertySetthesearc
3、hfiletype.Filename="*.xls"'.FileType=msoFileTypeExcelWorkbooksIf(.Execute)>0Then'ifyoufindthefileForI=1To.FoundFiles.Count'OnErrorResumeNextCells(I,1)=.FoundFiles(I')tofindthedocumentsplacedinthecellNextIElseMsgBox"filenotfound"EndIfEndWithMsgBoxTimer-tEndSub2,therecursiveme
4、thodSub(Test)DimiPathAsString,IAsLongDimtT=TimerWithApplication.FileDialog(msoFileDialogFolderPicker).Title="selectasearchfolder"If.ShowThenIPath=.SelectedItems(1)EndIfEndWithIfiPath="False"OrLen(iPath)=0ThenExitSubI=1CallGetFolderFile(iPath,I)MsgBoxTimer-tMsgBox"filenamelin
5、kforcompletion."VbOKOnly,"that"EndSubPrivateSubGetFolderFile(ByValnPathAsStringByRefiCountAsLong)DimiFileSys'DimiFileAsFiles,gFileAsFile'DimiFolderAsFolder,sFolderAsFolders,nFolderAsFolderSetiFileSys=CreateObject("Scripting.FileSystemObject")SetiFolder=iFileSys.GetFolder(nPa
6、th)SetsFolder=iFolder.SubFoldersSetiFile=iFolder.FilesWithActiveSheetForEachgFileIniFile.Hyperlinks.Addanchor:=.Cells'(iCount,1),Address:=gFile.path,TextToDisplay:=gFile.NameICount=iCount+1NextEndWith'recursivetraversalofallsubfoldersForEachnFolderInsFolderCallGetFolderFile(
7、nFolder.path,iCount)NextEndSub3,thedircirculationmethodSub(Test)'doubledictionary,inordertoimprovethespeedDimMyName,Dic,Did,I,t,F,TT,MyFileName'OnErrorResumeNextSetobjShell=CreateObject("Shell.Application")SetobjShell.BrowseForFolder(objFolder=0,selectfolder,0,0)IfNotobjFold
8、erIsNothingThenLJ=objFolder.self.path&""SetobjFolder=NothingSetobjShell=No
