|
楼主 |
发表于 2020-1-14 08:57
|
显示全部楼层
自己顶一下,跨工作簿工作表内容复制已有代码,但条件取值不会用,本人基础不够,有老师可否修改下代码。
Option Explicit
Dim fso As Object
'主程序
Sub 测试()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set fso = CreateObject("scripting.filesystemobject")
Range("a1:x65536").ClearContents
recursion ThisWorkbook.Path & "\" '递归,当前工作簿路径
End Sub
'
'查找
Sub recursion(myPath As String)
Dim myFolder As Object, mySubFolder As Object, myFile As Object
Set myFolder = fso.getfolder(myPath) '获取当前路径下的文件夹
For Each myFile In myFolder.Files
If myFile.Name <> ThisWorkbook.Name Then
Call closeWorkbook(myPath & "\", myFile.Name)
Call total(myPath & "\", myFile.Name)
End If
Next
For Each mySubFolder In myFolder.SubFolders '遍历子文件夹
recursion mySubFolder.Path '递归,当前子文件下工作簿路径
Next
End Sub
'关闭要打开的工作簿
Sub closeWorkbook(myPath, myFile)
On Error Resume Next
Workbooks(myFile).Close 0
On Error GoTo 0
End Sub
'逐一导入
Sub total(myPath, myFile)
Dim wk As Workbook, sh As Worksheet, i, j As Long, r As Byte, sht As Worksheet, arr
r = 1
Set wk = Workbooks.Open(myPath & myFile) '打开第二层子文件
For Each sh In wk.Sheets '遍历其下工作表
If sh.Name <> "汇总" Then
i = sh.UsedRange.Rows.Count
j = ThisWorkbook.Sheets("汇总").UsedRange.Rows.Count
sh.Range("a1:x" & i).Copy
ThisWorkbook.Sheets("汇总").Range("a" & j + 1).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End If
Next
wk.Close
End Sub
|
|