时间戳

首页 > 解决方案 > Excel VBA - 我收到运行时错误 424：使用 workbooks.open 打开另一个 Excel 文件时需要对象

excel - Excel VBA - 我收到运行时错误 424：使用 workbooks.open 打开另一个 Excel 文件时需要对象

问题描述

我正在尝试将几列数据从一个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:\BURNABY\SAP 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

标签： excelvba

解决方案

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

解决方案
- 将数据类型从 Variant 更改为 Range
- 调整范围大小

有帮助：
通过添加像Sub SampleData()你这样的东西让我们的生活更轻松，我们可以看到你的问题，而无需花费大量时间来重现你的问题。

在使用源数据创建对工作表的清晰引用之前，在我对您的代码进行测试期间确实发生了运行时错误 424。
通过添加actWs到此Set SAPNum = actWs.Range(Cells(3, 2), Cells(NRows, 2))错误消失，即使在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:\temp\dumpfile.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:\BURNABY\SAP Templates (Parts Upload & Batch PR Entry)\SAP Material Dump - Test.xlsx")
    Set DumpFile = Workbooks.Open(Filename:="c:\temp\dumpfile.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

推荐阅读