vba - 我的 VBA 代码每次运行时都会导致 Excel 崩溃

Question

有谁知道每次运行宏时哪一行或多行代码使我的 Excel 崩溃？当我注释掉从不同工作簿的复制并粘贴到当前工作簿时，它运行良好，所以我猜测它与这些行有关，但我不明白为什么复制和粘贴会导致它崩溃。 ..

该代码应该遍历文件夹中专门为 .xlsb 文件类型的所有文件，并从该 .xlsb 文件中复制选择内容并将其粘贴到当前 .xlsm 文件中。

Option Explicit

Sub OpenFiles()

    Dim objFs As Object
    Dim objFolder As Object
    Dim file As Object

    Set objFs = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFs.GetFolder(Application.ThisWorkbook.Path)

    Dim lastCol As Integer
    lastCol = 2

    For Each file In objFolder.Files
        If file Like "*.xlsb" Then

            Dim src As Workbook
            Set src = Workbooks.Open(file.Path, True, True)

            src.Worksheets("Rates").Range("C5", "C29").Copy
            ThisWorkbook.Worksheets("Sheet4").Cells(3, lastCol).PasteSpecial xlPasteValues

            src.Close False
            Set src = Nothing
            lastCol = lastCol + 1
        End If
    Next
End Sub

Answer 1

将 Dim 移出循环，检查以确保您没有尝试重新打开 ThisWorkbook 并传输值，而不是使用剪贴板进行复制。

...
Dim src As Workbook

For Each file In objFolder.Files
    If file Like "*.xlsb" and file not like ThisWorkbook.name Then

        Set src = Workbooks.Open(file.Path, UpdateLinks:=True, readonly:=True)

        with src.Worksheets("Rates").Range("C5:C29")
            ThisWorkbook.Worksheets("Sheet4").Cells(3, lastCol).resize(.rows.count, .columns.count) = .value
        end with

        src.Close False
        Set src = Nothing
        lastCol = lastCol + 1
    End If
Next file
...