Excel VBA - 我收到运行时错误 424：使用工作簿打开另一个 Excel 文件时需要对象。

Question

我正在尝试将一些数据列从一个Excel文件复制到另一个数据。我已经尝试调试，并且在打开工作簿的行上获得了运行时错误424：对象 - 另一个excel文件确实使用此代码打开，然后弹出了错误。

Sub CreateMatDump()
   Dim DumpFile As Workbook 'SAP Material Dump File
   Dim NRows As Long
   Dim SAPNum As Variant, MatType As Variant, MatGroup As Variant, UOM As Variant, MPN As Variant, MatDesc As Variant
   'Count rows
   NRows = Cells(Rows.Count, 14).End(xlUp).Row
   'Copy values to arrays
   SAPNum = Range(Cells(3, 2), Cells(NRows, 2)).Value
   MatType = Range(Cells(3, 6), Cells(NRows, 6)).Value
   MatGroup = Range(Cells(3, 11), Cells(NRows, 11)).Value
   UOM = Range(Cells(3, 10), Cells(NRows, 10)).Value
   MPN = Range(Cells(3, 14), Cells(NRows, 14)).Value
   MatDesc = Range(Cells(3, 9), Cells(NRows, 9)).Value
   'Open SAP Material Dump File
   Set DumpFile = Workbooks.Open(Filename:="R:BURNABYSAP Templates (Parts Upload & Batch PR Entry)SAP Material Dump - Test.xlsx")
   'Print arrays to SAP Material Dump File
   DumpFile.Sheets("Sheet1").Range("A2").Resize(NRows, 1).Value = SAPNum.Value
   DumpFile.Sheets("Sheet1").Range("B2").Resize(NRows, 1).Value = MatType.Value
   DumpFile.Sheets("Sheet1").Range("C2").Resize(NRows, 1).Value = MatGroup.Value
   DumpFile.Sheets("Sheet1").Range("D2").Resize(NRows, 1).Value = UOM.Value
   DumpFile.Sheets("Sheet1").Range("E2").Resize(NRows, 1).Value = MPN.Value
   DumpFile.Sheets("Sheet1").Range("F2").Resize(NRows, 1).Value = MatDesc.Value
End Sub

Answer 1

创建一个最小，完整和可验证的示例（请参阅https://stackoverflow.com/help/mcve）增加了您获得快速响应并获得好答案的机会。

解决方案
- 将数据类型从变体更改为范围
- 适应范围大小

有帮助：
通过添加诸如Sub SampleData()之类的东西，您可以使我们的生活更轻松，我们可以看到您的问题而无需花费大量时间来重现您的问题。

在我的测试期间使用您的代码进行了运行时错误424，然后使用源数据清楚地引用工作表。
通过将actWs添加到Set SAPNum = actWs.Range(Cells(3, 2), Cells(NRows, 2))中，即使未选择源数据工作表durint，此错误也会消失 CreateMatDump。

Option Explicit
Sub SampleData()
    Dim actCell As Range
    For Each actCell In Sheets(1).Range("A1:R15")
        actCell.Value = actCell.Address
    Next actCell
    On Error Resume Next
    Workbooks.Add
    If Sheets("Dump").Name <> "Dump" Then
        Worksheets.Add After:=Sheets(1)
        Sheets(2).Name = "Dump"
    End If
    On Error GoTo 0
    With Sheets("Dump")
        .Range("A1").Value = "SAPNum"
        .Range("B1").Value = "MatType"
        .Range("C1").Value = "MatGroup"
        .Range("D1").Value = "UOM.Value"
        .Range("E1").Value = "MPN.Value"
        .Range("F1").Value = "MatDesc"
        .Range("A:F").ColumnWidth = 14
    End With
    ActiveWorkbook.SaveAs "C:tempdumpfile.xlsx"
End Sub
Sub CreateMatDump()
    Dim DumpFile As Workbook 'SAP Material Dump File
    Dim actWb As Workbook
    Dim actWs As Worksheet
    Dim NRows As Long
    Dim SAPNum As Range, MatType As Range, MatGroup As Range, UOM As Range, MPN As Range, MatDesc As Range
    Set actWb = ThisWorkbook
    Set actWs = ThisWorkbook.Sheets(1)
    actWb.Activate
    actWs.Select
    'Count rows
    NRows = actWs.Cells(Rows.Count, 14).End(xlUp).Row
    'Copy values to arrays
    Set SAPNum = actWs.Range(Cells(3, 2), Cells(NRows, 2))
    Set MatType = actWs.Range(Cells(3, 6), Cells(NRows, 6))
    Set MatGroup = actWs.Range(Cells(3, 11), Cells(NRows, 11))
    Set UOM = actWs.Range(Cells(3, 10), Cells(NRows, 10))
    Set MPN = actWs.Range(Cells(3, 14), Cells(NRows, 14))
    Set MatDesc = actWs.Range(Cells(3, 9), Cells(NRows, 9))
    Debug.Print "SAPNum   : "; SAPNum.Address
    Debug.Print "MatType  : "; MatType.Address
    Debug.Print "MatGroup : "; MatGroup.Address
    Debug.Print "UOM      : "; UOM.Address
    Debug.Print "MPN      : "; MPN.Address
    Debug.Print "MatDesc  : "; MatDesc.Address
    'Open SAP Material Dump File
    'Set DumpFile = Workbooks.Open(Filename:="R:BURNABYSAP Templates (Parts Upload & Batch PR Entry)SAP Material Dump - Test.xlsx")
    Set DumpFile = Workbooks.Open(Filename:="c:tempdumpfile.xlsx")
    'Set DumpFile = ActiveWorkbook
    'Print arrays to SAP Material Dump File
    With DumpFile.Sheets(2)
        SAPNum.Copy    'to:
                            .Range("A2").PasteSpecial Paste:=xlPasteValues
        MatType.Copy   'to:
                            .Range("B2").PasteSpecial Paste:=xlPasteValues
        MatGroup.Copy  'to:
                            .Range("C2").PasteSpecial Paste:=xlPasteValues
        UOM.Copy       'to:
                            .Range("D2").PasteSpecial Paste:=xlPasteValues
        MPN.Copy       'to:
                            .Range("E2").PasteSpecial Paste:=xlPasteValues
        MatDesc.Copy   'to:
                            .Range("F2").PasteSpecial Paste:=xlPasteValues
    End With
End Sub