excel - 代码在复制/粘贴为宏时运行，但在另存为加载项时不运行

Question

正如标题所示，此代码在 Sheet.Copy After:=ThisWorkbook.Sheets(1) 行失败。运行时错误 1004

为什么这会在作为模块添加时运行，但在保存为加载项时不会运行？

这是代码:

Dim FolderPath As String
Dim Filename As String
Dim Sheet As Worksheet

Application.ScreenUpdating = False
FolderPath = GetFolder() & "\"
Filename = Dir(FolderPath & "*.xls*")
Do While Filename <> ""
 Workbooks.Open Filename:=FolderPath & Filename, ReadOnly:=True
 For Each Sheet In ActiveWorkbook.Sheets
 Sheet.Copy After:=ThisWorkbook.Sheets(1)
 Next Sheet
 Workbooks(Filename).Close
 Filename = Dir()
 Loop

Application.ScreenUpdating = True
End Sub

Answer 1

正如@BruceWayne 所建议的那样，决定正确的工作簿是一个问题。作为 AddIn , ThisWorkbook将是 AddIn工作簿，而 ActiveWorkbook (在打开其他人之前)，将是您正在运行的工作簿 AddIn进入。

简单替换Thisworkbook与 ActiveWorkbook在您的场景中不起作用，因为您只需将工作表从新打开的工作簿复制到相同的位置。

声明变量以保存此信息是一个好主意，然后您可以根据需要打开任意数量的工作簿，并且可以从您想要的位置打开。

见下文:

Application.ScreenUpdating = False

Dim wbDst As Workbook: Set wbDst = ActiveWorkbook 'Can also use Worbooks("book name here")
Dim wbSrc As Workbook
Dim Sht As Worksheet

Dim FolderPath As String: FolderPath = GetFolder() & "\"
Dim FileName As String: FileName = Dir(FolderPath & "*.xls*")

Do While FileName <> ""
    Set wbSrc = Workbooks.Open(FileName:=FolderPath & FileName, ReadOnly:=True)

    For Each Sht In wbSrc.Worksheets
        Sht.Copy After:=wbDst.Sheets(1)
    Next Sht

    wbSrc.Close
    FileName = Dir()
Loop

Application.ScreenUpdating = True