通过VBA打开不同文件夹下的excel文件,并将其中一部分复制汇总到一张新表
各位大神,我希望打开不同文件夹下的excel文件,并将其中一部分复制汇总到一张新表,但是到这句“Application.Workbooks.Open Filename:=myfile & Sheets("公司简称").Cells(3, 3) & "\各公司报告\" & Cells(n, 1) & "\*.xlsx"”总会提示“方法open作用于对象workbooks时失败”,代码如下,请各位大神帮忙指导,谢谢!
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
On Error Resume Next
Application.SendKeys "{ENTER}" '回车
End Sub
Sub 非正常满期()
For n = 2 To 25
i = n - 1
myfile = ThisWorkbook.Path & "\"
Application.Workbooks.Open Filename:=myfile & Sheets("公司简称").Cells(3, 3) & "\各公司报告\" & Cells(n, 1) & "\*.xlsx"
Sheets("表1-4 非正常满期给付").Select
Range("A1:J15").Select
Selection.Copy
ActiveWindow.Close False
Windows("VBA表.xlsm").Activate
Sheets.Add After:=ActiveSheet
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets(i).Select
Sheets(i).Name = Sheets("公司简称").Range(B & n)
Next
End Sub