|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
Sub 合并工作簿()
Application.ScreenUpdating = False
Dim ar As Variant, br As Variant
Dim i As Long
Dim arr()
Dim d As Object
Set d = CreateObject("scripting.dictionary")
Set obmapp = CreateObject("Shell.Application").BrowseForFolder(0, "请选择文件目录:", 0, 0) '选择文件夹对话框
If obmapp Is Nothing Then MsgBox "您没有选择文件夹!": End '如果选择了文件夹
fp = obmapp.Self.Path '把选择的文件夹的路劲赋值给变量fp
Set ww = ThisWorkbook.Worksheets("汇总")
With ww
ww.UsedRange.Clear
f = Dir(fp & "\*.xls*")
Do While f <> ""
If f <> ThisWorkbook.Name Then
m = m + 1
Set wb = Workbooks.Open(fp & "\" & f, 0)
With wb.Worksheets(1)
r = .Cells(Rows.Count, 2).End(xlUp).Row
rs = ww.Cells(Rows.Count, 2).End(xlUp).Row + 1
If m = 1 Then
.Range("a2:d" & r).Copy ww.Cells(rs, 1)
Else
.Range("a3:d" & r).Copy ww.Cells(rs, 1)
End If
End With
wb.Close False
End If
f = Dir
Loop
rs = ww.Cells(Rows.Count, 2).End(xlUp).Row
For i = 3 To rs
.Cells(i, 1) = i - 2
Next i
End With
Application.ScreenUpdating = True
MsgBox "ok!"
End Sub
|
|