excel遍历文件夹

❶ vba读取excel遍历文件指定数据

Excel文件格式一致，汇总求和，其他需求自行变通容
汇总使用了字典

Public d
Sub 按钮1_Click()
Application.ScreenUpdating = False
ActiveSheet.UsedRange.ClearContents
Cells(1, 1) = "编号"
Cells(1, 2) = "数量"
Set d = CreateObject("scripting.dictionary")
Getfd (ThisWorkbook.Path) 'ThisWorkbook.Path是当前代码文件所在路径，路径名可以根据需求修改
Application.ScreenUpdating = True
If d.Count > 0 Then
ThisWorkbook.Sheets(1).[a2].Resize(d.Count) = WorksheetFunction.Transpose(d.keys)
ThisWorkbook.Sheets(1).[b2].Resize(d.Count) = WorksheetFunction.Transpose(d.items)
End If
End Sub
Sub Getfd(ByVal pth)

Set Fso = CreateObject("scripting.filesystemobject")
Set ff = Fso.getfolder(pth)
For Each f In ff.Files
Rem 具体提取哪类文件，还是需要根据文件扩展名进行处理
If InStr(Split(f.Name, ".")(UBound(Split(f.Name, "."))), "xl") > 0 Then
If f.Name <> ThisWorkbook.Name Then
Set wb = Workbooks.Open(f)
For Each sht In wb.Sheets
If WorksheetFunction.CountA(sht.UsedRange) > 1 Then
arr = sht.UsedRange
For j = 2 To UBound(arr)
d(arr(j, 1)) = d(arr(j, 1)) + arr(j, 2)
Next j
End If
Next sht
wb.Close False
End If
End If
Next f
For Each fd In ff.subfolders
Getfd (fd)
Next fd
End Sub

❷ 如何用excel vba按关键字选择性的遍历文件夹搜索文件

Excel怎样批量提取文件夹和子文件夹所有文件

❸ EXCEL vba 读取指定文件夹的名字和循环打开文件夹

'------------------------------------------------------------------------------

'

'FormCode

'

'------------------------------------------------------------------------------

OptionExplicit

PrivaterowAsInteger,colAsInteger

PrivateSubCloseWindows_Click()

IfTextStartRow.Text=""ThenTextStartRow=0

IfTextStartCol=""ThenTextStartCol=0

IfTextPath=""ThenTextPath="D:"

CloseMyDialogTextStartRow,TextStartCol

EndSub

PrivateSubGetDir_Click()

IfTextStartRow.Text=""ThenTextStartRow=0

IfTextStartCol=""ThenTextStartCol=0

IfTextPath=""Then

TextPath="D:"

ElseIfRight(TextPath,1)<>""Then

TextPath=TextPath&""

EndIf

doGetDirTextPath,Val(TextStartRow),Val(TextStartCol)

EndSub

PrivateSubShowWindows_Click()

IfTextStartRow.Text=""ThenTextStartRow=0

IfTextStartCol=""ThenTextStartCol=0

IfTextPath=""ThenTextPath="D:"

ShowMyDialogApplication.hWnd,TextStartRow,TextStartCol

EndSub

上面是Form上面的

OptionExplicit

DimMyFile,Mypath,MyName

Dimi%,j%

DimDirPath()AsString

SubGetDir(ByValMypathAsString,rowAsInteger,colAsInteger)

'显示C:目录下的名称。

'MyPath="d:电大"'指定路径。

MyName=Dir(Mypath,vbDirectory)'找寻第一项。

DoWhileMyName<>""'开始循环。

'跳过当前的目录及上层目录。

IfMyName<>"."AndMyName<>".."Then

'使用位比较来确定MyName代表一目录。

If(GetAttr(Mypath&MyName)AndvbDirectory)=vbDirectoryThen

Cells(row+i,col)=Mypath&MyName'如果它是一个目录，将其名称显示出来。

ReDimPreserveDirPath(i)

DirPath(i)=Mypath&MyName&""

i=i+1

EndIf

EndIf

MyName=Dir'查找下一个目录。

Loop

EndSub

PublicSubdoGetDir(ByValTextPath$,ByValTextStartRow%,ByValTextStartCol%)

j=1

i=1

Mypath=TextPath

GetDirMypath,TextStartRow,TextStartCol

Forj=1Toi-1

GetDirDirPath(j),TextStartRow,TextStartCol

Next

EndSub

'endcode---------------------------------------------------

OptionExplicit

PublicConstOFN_ALLOWMULTISELECTAsLong=&H200

PublicConstOFN_CREATEPROMPTAsLong=&H2000

PublicConstOFN_ENABLEHOOKAsLong=&H20

PublicConstOFN_ENABLETEMPLATEAsLong=&H40

PublicConstOFN_ENABLETEMPLATEHANDLEAsLong=&H80

PublicConstOFN_EXPLORERAsLong=&H80000

PublicConstOFN_EXTENSIONDIFFERENTAsLong=&H400

PublicConstOFN_FILEMUSTEXISTAsLong=&H1000

PublicConstOFN_HIDEREADONLYAsLong=&H4

PublicConstOFN_LONGNAMESAsLong=&H200000

PublicConstOFN_NOCHANGEDIRAsLong=&H8

PublicConstOFN_NODEREFERENCELINKSAsLong=&H100000

PublicConstOFN_NOLONGNAMESAsLong=&H40000

PublicConstOFN_NONETWORKBUTTONAsLong=&H20000

PublicConstOFN_NOREADONLYRETURNAsLong=&H8000&'*seecomments

PublicConstOFN_NOTESTFILECREATEAsLong=&H10000

PublicConstOFN_NOVALIDATEAsLong=&H100

PublicConstOFN_OVERWRITEPROMPTAsLong=&H2

PublicConstOFN_PATHMUSTEXISTAsLong=&H800

PublicConstOFN_READONLYAsLong=&H1

PublicConstOFN_SHAREAWAREAsLong=&H4000

PublicConstOFN_SHAREFALLTHROUGHAsLong=2

PublicConstOFN_SHAREWARNAsLong=0

PublicConstOFN_SHARENOWARNAsLong=1

PublicConstOFN_SHOWHELPAsLong=&H10

PublicConstOFS_MAXPATHNAMEAsLong=260

PublicConstOFS_FILE_OPEN_FLAGS=OFN_EXPLORER_

OrOFN_LONGNAMES_

OrOFN_CREATEPROMPT_

OrOFN_NODEREFERENCELINKS

PublicConstOFS_FILE_SAVE_FLAGS=OFN_EXPLORER_

OrOFN_LONGNAMES_

OrOFN_OVERWRITEPROMPT_

OrOFN_HIDEREADONLY

PublicTypeOPENFILENAME

nStructSizeAsLong

hWndOwnerAsLong

hInstanceAsLong

sFilterAsString

sCustomFilterAsString

nMaxCustFilterAsLong

nFilterIndexAsLong

sFileAsString

nMaxFileAsLong

sFileTitleAsString

nMaxTitleAsLong

sInitialDirAsString

sDialogTitleAsString

flagsAsLong

nFileOffsetAsInteger

nFileExtensionAsInteger

sDefFileExtAsString

nCustDataAsLong

fnHookAsLong

sTemplateNameAsString

EndType

PublicOFNAsOPENFILENAME

PublicConstWM_CLOSE=&H10

"comdlg32"_

Alias"GetOpenFileNameA"_

(pOpenfilenameAsOPENFILENAME)AsLong

"comdlg32"_

Alias"GetSaveFileNameA"_

(pOpenfilenameAsOPENFILENAME)AsLong

"kernel32"_

Alias"GetShortPathNameA"_

(ByVallpszLongPathAsString,_

ByVallpszShortPathAsString,_

ByValcchBufferAsLong)AsLong

PublicConstWM_INITDIALOG=&H110

PrivateConstSW_SHOWNORMAL=1

PublicTypeRECT

LeftAsLong

TopAsLong

RightAsLong

BottomAsLong

EndType

"user32"_

(ByValhWndAsLong)AsLong

"user32"_

Alias"SetWindowTextA"_

(ByValhWndAsLong,_

ByVallpStringAsString)AsLong

"user32"_

(ByValhWndAsLong,_

ByValxAsLong,_

ByValyAsLong,_

ByValnWidthAsLong,_

ByValnHeightAsLong,_

ByValbRepaintAsLong)AsLong

"user32"_

(ByValhWndAsLong,_

lpRectAsRECT)AsLong

"user32"_

Alias"SendMessageA"_

(ByValhWndAsLong,_

ByValwMsgAsLong,_

ByValwParamAsLong,_

lParamAsAny)AsLong

"user32"_

Alias"FindWindowA"_

(ByVallpClassNameAsLong,_

ByVallpWindowNameAsString)AsLong

PublicFunctionFARPROC(ByValpfnAsLong)AsLong

FARPROC=pfn

EndFunction

PublicFunctionOFNHookProc(ByValhWndAsLong,_

ByValuMsgAsLong,_

ByValwParamAsLong,_

ByVallParamAsLong)AsLong

DimhwndParentAsLong

DimrcAsRECT

DimnewLeftAsLong

DimnewTopAsLong

DimdlgWidthAsLong

DimdlgHeightAsLong

DimscrWidthAsLong

DimscrHeightAsLong

SelectCaseuMsg

CaseWM_INITDIALOG

hwndParent=GetParent(hWnd)

IfhwndParent<>0Then

CallGetWindowRect(hwndParent,rc)

dlgWidth=rc.Right-rc.Left

dlgHeight=rc.Bottom-rc.Top

CallMoveWindow(hwndParent,newLeft,newTop,dlgWidth,dlgHeight,True)

OFNHookProc=1

EndIf

CaseElse:

EndSelect

EndFunction

PublicSubShowFolder(hWndAsLong,Mypath$)

DimsFiltersAsString

DimposAsLong

DimbuffAsString

DimsLongnameAsString

DimsShortnameAsString

WithOFN

.nStructSize=Len(OFN)

.hWndOwner=hWnd

.sFilter=sFilters

.nFilterIndex=2

.sFile=Space$(1024)&vbNullChar&vbNullChar

.nMaxFile=Len(.sFile)

.sDefFileExt="bas"&vbNullChar&vbNullChar

.sFileTitle=vbNullChar&Space$(512)&vbNullChar&vbNullChar

.nMaxTitle=Len(OFN.sFileTitle)

.sInitialDir=Mypath&vbNullChar&vbNullChar

.sDialogTitle=Mypath&vbNullChar&vbNullChar

.flags=OFS_FILE_OPEN_FLAGSOr_

OFN_ALLOWMULTISELECTOr_

OFN_EXPLOREROr_

OFN_ENABLEHOOK

.fnHook=FARPROC(AddressOfOFNHookProc)

EndWith

GetOpenFileNameOFN

EndSub

PublicSubCloseFolder(MypathAsString)

DimhWndAsLong

hWnd=FindWindow(0,Mypath)

CallSendMessage(hWnd,WM_CLOSE,0&,ByVal0&)

EndSub

PublicSubShowMyDialog(MyhWndAsLong,TextStartRowAsInteger,TextStartColAsInteger)

Dimrow,col

Dimi

DimhWndAsLong

hWnd=MyhWnd

i=1:row=TextStartRow:col=TextStartCol

DoWhileCells(i+row,col)<>""

Shell"C:Windowsexplorer.exe"&Cells(i+row,col)

'ShowFolderhWnd,Cells(i+row,col)

'hWnd=FindWindow(0,Cells(i+row,col))

i=i+1

Loop

EndSub

PublicSubCloseMyDialog(TextStartRowAsInteger,TextStartColAsInteger)

Dimrow,col

Dimi

i=1:row=TextStartRow:col=TextStartCol

DoWhileCells(i+row,col)<>""

CloseFolderpathToName(Cells(i+row,col))

i=i+1

Loop

EndSub

PrivateFunctionpathToName(Mypath$)AsString

Dimstr()AsString

str=Split(Mypath,"")

pathToName=str(UBound(str))

EndFunction

❹ 如何用VBA遍历指定目录下的所有子文件夹Excel文件的所有工作表

vba麻烦点，dir最快，excelhome论坛上有大量现成代码：

SubOpiona()'//函数实例
FileArr=FileAllArr(ThisWorkbook.Path,"*.xls",ThisWorkbook.Name,False)
Fori=0ToUBound(FileArr)
MsgBoxFileArr(i)
'SetWB=Workbooks.Open(FileArr(I))'//打开工作簿
'你的代码
'WB.Closetrue'//保存
Next
EndSub
'*******************************************************************************************************
'功能：查找指定文件夹含子文件夹内所有文件名或文件夹名（含路径）
'函数名：FileAllArr
'参数1：Filename需查找的文件夹名不含最后的""
'参数2：FileFilter需要过滤的文件名，可省略，默认为：[*.*]
'参数3：Liwai剔除例外的文件名，可省略，默认为：空，一般为：ThisWorkbook.Name
'参数4：Files是否只要文件夹名，可省略，默认为：FALSE
'返回值：一个字符型的数组
'使用方法：FileArr=FileAllArr(ThisWorkbook.Path,"*.xls",ThisWorkbook.Name,false)
'作者:北极狐工作室QQ：14885553
'*******************************************************************************************************
PublicFunctionFileAllArr(ByValFilenameAsString,="*.*",OptionalByValLiwaiAsString="",OptionalByValFilesAsBoolean=False)AsString()
SetDic=CreateObject("Scripting.Dictionary")'创建一个字典对象
SetDid=CreateObject("Scripting.Dictionary")
Dic.Add(Filename&""),""
i=0
DoWhilei<Dic.Count
Ke=Dic.keys'开始遍历字典
MyName=Dir(Ke(i),vbDirectory)'查找目录
DoWhileMyName<>""
IfMyName<>"."AndMyName<>".."Then
If(GetAttr(Ke(i)&MyName)AndvbDirectory)=vbDirectoryThen'如果是次级目录
Dic.Add(Ke(i)&MyName&""),""'就往字典中添加这个次级目录名作为一个条目
EndIf
EndIf
MyName=Dir'继续遍历寻找
Loop
i=i+1
Loop
Dimarrx()AsString
i=0
IfFiles=TrueThen'//是否只输出文件夹名

ForEachKeInDic.keys'以查找总表所在文件夹下所有excel文件为例
ReDimPreservearrx(i)
IfKe<>Filename&""Then'//自身文件夹除外
arrx(i)=Ke
i=i+1
EndIf
Next
FileAllArr=arrx
Else
ForEachKeInDic.keys'以查找总表所在文件夹下所有excel文件为例
MyFileName=Dir(Ke&FileFilter)'过滤器：EXCEL2003为：*.xls,excel2007为：*.xlsx
DoWhileMyFileName<>""
IfMyFileName<>LiwaiThen'排除例外文件
ReDimPreservearrx(i)
arrx(i)=Ke&MyFileName
i=i+1
EndIf
MyFileName=Dir
Loop
Next
FileAllArr=arrx
EndIf
EndFunction
'****************************************************************

❺ 如何用VBA遍历指定目录下的所有子文件夹Excel文件的所有工作表

下面的代码是手工码的，不晓得有没有问题。

subtest()
dimfasstring,mPathasstring,Wbasworkbook,ShasworkSheet
ifworkbooks.count>1thenmsgbox"关闭其他工作簿！":exitsub
mPath="D:临时文件夹"'指定路径，注意分层标记
f=dir(mPath&"*.xls*")
dowhilef<>""
iff<>thisworkbook.namethen
setWb=workbooks.open(mPath&f)'只读方式打开
withWb
foreachShin.workSheets
'对工作表进行操作的代码段，自己写。
next
endwith
wb.close0'关闭文件
endif
f=dir'枚举,以访问下一个工作簿。
loop
endsub