|
本帖最后由 乐乐2006201506 于 2016-6-3 21:58 编辑
主要是文件格式问题,第一个文件夹中的是.xlsx,而第二个文件夹中的都是.xlsm。请解决这个问题,谢谢!
注:两个文件夹中都用总表中的汇总(修改汇总代码)按钮合并其他工作簿中的数据。
汇总程序如下: - Sub 汇总()
- Dim myPath$, ibook As Workbook, myFile$, myName$, iName1$, ibook1 As Workbook
- Dim iSheet As Worksheet
- myPath = ThisWorkbook.Path & ""
- Set ibook = Application.ThisWorkbook
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Application.AskToUpdateLinks = False
- For x = ibook.Sheets.Count To 1 Step -1
- If ibook.Sheets(x).Name <> "总表" Then ibook.Sheets(x).Delete
- Next x
-
- myName = Dir(myPath & "*.xlsm")
- Do While myName <> "" ' 开始循环。
- If myName <> "总表.xlsm" Then
- Workbooks.Open (myPath & myName)
- Set ibook1 = ActiveWorkbook
- i = ActiveWorkbook.Sheets.Count
- For x = 1 To i
- iName1 = Sheets(x).Name
- For Each iSheet In ibook.Sheets
- If iSheet.Name = iName1 Then
- GoTo aa
- End If
- Next
- ibook.Sheets.Add after:=ibook.Sheets(ibook.Sheets.Count)
- With ibook.Sheets(ibook.Sheets.Count)
- .Name = iName1
- .Range("b1") = "分表名"
- .Range("D1") = "时间"
- End With
- aa:
- With ibook.Sheets(iName1)
- If .Range("C1") = "" Then .Range("C1") = iName1
- .Range("B65536").End(xlUp).Offset(1).Value = Replace(myName, ".xlsm", "")
- .Range("B65536").End(xlUp).Offset(, 1) = Sheets(x).Range("A1").Value
- .Cells.EntireColumn.AutoFit
- End With
-
- Next x
- ibook1.Close
- End If
- myName = Dir ' 查找下一个目录。
- Loop
- Application.DisplayAlerts = True
- Application.ScreenUpdating = True
- End Sub
复制代码这里我把更新连接关闭了,如果你要启用的话设置Application.AskToUpdateLinks = True
|
|