|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
Sub 汇总多个工作簿()
' https://club.excelhome.net/threa ... tml?_dsign=47bbe63f
' 2024-5-57
' 处理:取消汇总表上的合并单元格。
Range("A2:J21").ClearContents
Dim File As String
Dim i&, j&
Dim wb As Object
'Application.ScreenUpdating = False
File = Dir(ThisWorkbook.Path & "\*.xlsx") '获取文件夹中第一张表的名称,*表示通配符
i = 2
Do 'While Len(File) ' 按文件数量循环,第一次见识
Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & File) ' 打开文件
ThisWorkbook.Sheets(1).Cells(i, 1).Value = i - 1
ThisWorkbook.Sheets(1).Cells(i, 2).Value = wb.Sheets(1).Cells(6, 2).Value
ThisWorkbook.Sheets(1).Cells(i, 3).Value = wb.Sheets(1).Cells(6, 5).Value
ThisWorkbook.Sheets(1).Cells(i, 4).Value = wb.Sheets(1).Cells(7, 2).Value
ThisWorkbook.Sheets(1).Cells(i, 5).Value = wb.Sheets(1).Cells(7, 5).Value
ThisWorkbook.Sheets(1).Cells(i, 6).Value = wb.Sheets(1).Cells(8, 2).Value
ThisWorkbook.Sheets(1).Cells(i, 7).Value = wb.Sheets(1).Cells(9, 2).Value
ThisWorkbook.Sheets(1).Cells(i, 8).Value = wb.Sheets(1).Cells(10, 2).Value
ThisWorkbook.Sheets(1).Cells(i, 9).Value = wb.Sheets(1).Cells(11, 2).Value
ThisWorkbook.Sheets(1).Cells(i, 10).Value = wb.Sheets(1).Cells(12, 2).Value
wb.Close False ' 将打开的文件内容复制到当前工作簿后,不保存退出
i = i + 1
File = Dir()
If Len(File) = 0 Then Exit Do ' 经测试,这样也行
Loop
'Application.ScreenUpdating = False
End Sub
|
|