vba - 多个 Excel 工作簿和 1004 运行时错误

Question

在“Set wbForm1Original = Workbooks.Open...”行遇到运行时错误 1004，非常感谢一些建议。该例程是复制一个原始工作簿，并根据数据工作簿中的一些内容命名工作簿的副本，然后将数据工作簿中的一行内容复制到这个复制的工作簿中。 (对数据工作簿中的每一行执行相同操作:重复、命名、复制内容)

Sub createForm()

Dim dirPath As String
Dim filePath As String
Dim file As String
Dim companyName As String
Dim n As Integer
Dim i As Integer
Dim wbVendor As Workbook
Dim wbForm1Original As Workbook
Dim wbForm1Company As Workbook

Set wbVendor = ThisWorkbook
dirPath = CurDir()
n = 5

For i = 2 To (n + 1)

    companyName = wbVendor.Worksheets("Sheet1").Cells(i, 1).Value

    file = "177_609 i_" & companyName & ".xlsx"
    filePath = dirPath & "\" & file

    Set wbForm1Original = Workbooks.Open("C:\Users\user1\Documents\8. Finance\Vendors\177_609 i.xls")

    wbForm1Original.SaveCopyAs filePath
    Set wbForm1Company = Workbooks.Open(filePath)

    Workbooks("Vendor.xlsm").Worksheets("Sheet1").Range("A" & CStr(i)).Copy _
        Workbooks(file).Worksheets("Sheet1").Range("I13")
    Workbooks("Vendor.xlsm").Worksheets("Sheet1").Range("B" & CStr(i)).Copy _
        Workbooks(file).Worksheets("Sheet1").Range("I14")
    Workbooks("Vendor.xlsm").Worksheets("Sheet1").Range("C" & CStr(i)).Copy _
        Workbooks(file).Worksheets("Sheet1").Range("I15")
    Workbooks("Vendor.xlsm").Worksheets("Sheet1").Range("D" & CStr(i)).Copy _
        Workbooks(file).Worksheets("Sheet1").Range("E16")
    Workbooks("Vendor.xlsm").Worksheets("Sheet1").Range("E" & CStr(i)).Copy _
        Workbooks(file).Worksheets("Sheet1").Range("J16")
    Workbooks("Vendor.xlsm").Worksheets("Sheet1").Range("F" & CStr(i)).Copy _
        Workbooks(file).Worksheets("Sheet1").Range("M16")

Next i
End Sub

Answer 1

您正在循环打开工作簿而不关闭它。因此，它不喜欢它。要么关闭工作簿，然后再次打开它，要么将开口放在循环之外，如下所示:

Set wbForm1Original = Workbooks.Open("C:\...\8. Finance\Vendors\177_609 i.xls")
wbForm1Original.SaveCopyAs filePath
Set wbForm1Company = Workbooks.Open(filePath)

For i = 2 To (n + 1)
'further code...