|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
请教:
按照教程4.7.10:汇总同文件夹下多工作薄数据的时候,汇总结果只显示了第一个文件的内容,另外两个文件没有汇总!请各位大侠指点!不胜感激!
代码如下:
Sub Hzwb()
Dim r As Long, c As Long, sht As Worksheet
r = 1
c = 27
Range(Cells(r + 1, "A"), Cells(65536, c)).ClearContents
Application.ScreenUpdating = False
Dim FileName As String, wb As Workbook, Erow As Long, fn As String, arr As Variant
FileName = Dir(ThisWorkbook.Path & "\*.xls")
Do While FileName <> ""
If FileName <> ThisWorkbook.Name Then
Erow = Range("B1").CurrentRegion.Rows.Count + 1
fn = ThisWorkbook.Path & "\" & FileName
Set wb = GetObject(fn)
Set sht = wb.Worksheets(4)
arr = sht.Range(sht.Cells(r + 1, "A"), sht.Cells(65536, "B").End(xlUp).Offset(0, 27))
Cells(Erow, "A").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
wb.Close False
End If
FileName = Dir
Loop
Application.ScreenUpdating = True
End Sub |
|