Excel VBA - I'm getting run-time error 424: object required when opening another excel file using workbooks.open

Question

I'm trying to copy a few columns of data from one excel file to another. I've tried debugging and I get the Run-Time Error 424: Object required on the line that opens the workbook - the other excel file does open with this code and then the error pops up.

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

SAPNum (eg) is a 2D array, not an object - it doesn't have a Value property. Likewise for the other variables. Just remove the .Value. Note also that your ranges aren't the same size. you're picking up from NRows-2 cells and setting the values into NRows cells — Tim Williams
Thanks Tim. Your suggestions worked. I completely missed the NRows not being the size of the range that I wanted to paste. I have added 2 other variables to handle the start row and array size. — user2620359

simple-solution simple-solution · Accepted Answer · 2019-02-23T15:47:23

Creating a Minimal, Complete, and Verifiable example (see https://stackoverflow.com/help/mcve) increases your chance to get a fast response and get good answers.

Solution
- Change of datatype from Variant to Range
- adapt range size

helpful:
By adding something like Sub SampleData() you make our life easier and we can see your problem without spending a lot of time to recreate your problem.

The Run-Time Error 424 did occur during my test with your code prior to creating a clear reference to the worksheet with the source data.
By adding actWs to Set SAPNum = actWs.Range(Cells(3, 2), Cells(NRows, 2)) this error disappeared even when the source data worksheet was not selected durint the start of 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

Excel VBA - I'm getting run-time error 424: object required when opening another excel file using workbooks.open

1 Answers