vba - 将事件工作表复制到另一个工作簿 : Copy Method of Range class failed

Question

我希望我的代码复制整个工作表(SOURCE)并将其粘贴到其他工作簿(WHERE_I_WANNA_PASTE_IT)下的其他工作表(TARGET)中并保存。

我收到此错误:

Run=-time error '1004': Copy Method of Range class failed

在这条线上:

CurrentSheet.Cells.Copy Destination:=oSheet.cells 

编码:

Public Const path1 As String = "C:\Where_I_WANNA_PASTE_IT.xlsb"
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim CurrentSheet As Object

Sub copyNpaste

Set CurrentSheet = ActiveSheet
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open(path1)
Set oSheet = oBook.Worksheets("TARGET")

'Deleting what's on "TARGET" first
oSheet.cells.delete

'This is where the Error happens.
CurrentSheet.Cells.Copy _
Destination:=oSheet.Cells

oBook.Save
oBook.Close

Set oExcel = Nothing
Set oBook = Nothing
Set oSheet = Nothing
Set CurrentSheet = Nothing

End Sub

Answer 1

一些建议，这应该有效(至少它在我的电脑上有效，我能够完美地复制你的错误):

修复 1

不要创建新的 Excel 应用程序，使用相同的线程；换句话说，删除这个:

Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open(path1)

并写下:

Set oBook = Workbooks.Open(path1)

修复 2

您可以在需要时设置事件工作表，但要清楚引用，使用“ThisWorkbook”，这样编译器会很高兴并且您不会冒险引用另一张工作表(这始终是一个好习惯，永远不要完全相信如果您已经知道预期的引用是什么，则默认引用，例如在这种情况下):

Set CurrentSheet = ThisWorkbook.ActiveSheet

和一个建议...

放置一个错误处理程序:如果该方法失败，您会发现一堆开放式 Excel 线程(检查一下，您将拥有与运行代码失败一样多的线程。这是我编写完整代码的方式(建议包括):

Public Const path1 As String = "C:\yourfile.xlsb"
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim CurrentSheet As Object

Sub copyNpaste()

Set oBook = Workbooks.Open(path1)
Set oSheet = oBook.Worksheets("TARGET")
Set CurrentSheet = ThisWorkbook.ActiveSheet

On Error GoTo ESCAPE 'if the code fails, we go to "Escape" and at least we close the file

'Deleting what's on "TARGET" first
oSheet.Cells.Delete

'This is where the Error happens.
CurrentSheet.Cells.Copy _
Destination:=oSheet.Cells

oBook.Save

ESCAPE:

oBook.Close

Set oExcel = Nothing
Set oBook = Nothing
Set oSheet = Nothing
Set CurrentSheet = Nothing

End Sub

注意

打开任务管理器 (Ctrl+Alt+Del) 并转到进程...关闭所有 EXCEL.EXE 进程，每次运行代码失败时，在笔记本电脑爆炸之前关闭您可能留下的所有进程:)