Run Time Error 424 Object Required while exporting data from a listbox to a new workbook

Question

i am trying to export data from a listbox to a new worksheet. It was working until today, i dont know if i did something and i cant see it. Here is my code:

    Private Sub Boton_Exportar_Click()


    Dim objexcel As Object
    Dim NombreArchivo As String
    Dim i As Integer, Fila As Integer


    If MsgBox("Seguro que desea exportar en excel?", vbYesNo + vbQuestion) = vbYes Then

    Application.ScreenUpdating = False

      Set objexcel = Workbooks.Add
      objexcel.Activate
      NombreArchivo = ActiveWorkbook.Name

    'Asignar los datos del reporte
    Application.Workbooks(NombreArchivo).Worksheets(1).Cells(1, 1) = "Delayed Filter"
    Application.Workbooks(NombreArchivo).Worksheets(1).Cells(1, 2) = Me.ComboBox1

    Application.Workbooks(NombreArchivo).Worksheets(1).Cells(3, 1) = "Vendor"
    Application.Workbooks(NombreArchivo).Worksheets(1).Cells(3, 2) = "PO Number"
    Application.Workbooks(NombreArchivo).Worksheets(1).Cells(3, 3) = "Order Date"
    Application.Workbooks(NombreArchivo).Worksheets(1).Cells(3, 4) = "Part Number"
    Application.Workbooks(NombreArchivo).Worksheets(1).Cells(3, 5) = "Quantity"
    Application.Workbooks(NombreArchivo).Worksheets(1).Cells(3, 6) = "UM"
    Application.Workbooks(NombreArchivo).Worksheets(1).Cells(3, 7) = "Promised Date"
    Application.Workbooks(NombreArchivo).Worksheets(1).Cells(3, 8) = "Due Date"
    Application.Workbooks(NombreArchivo).Worksheets(1).Cells(3, 9) = "Status"


    Fila = 4
    For i = 0 To Me.ListBox1.ListCount - 1

    Application.Workbooks(NombreArchivo).Worksheets(1).Cells(Fila, 1) = Me.ListBox1.List(i, 0)
    Application.Workbooks(NombreArchivo).Worksheets(1).Cells(Fila, 2) = Me.ListBox1.List(i, 1)
    Application.Workbooks(NombreArchivo).Worksheets(1).Cells(Fila, 3) = CDate(Me.ListBox1.List(i, 2))
    Application.Workbooks(NombreArchivo).Worksheets(1).Cells(Fila, 4) = Me.ListBox1.List(i, 3)
    Application.Workbooks(NombreArchivo).Worksheets(1).Cells(Fila, 5) = Format(Me.ListBox1.List(i, 4), "#,###.00")
    Application.Workbooks(NombreArchivo).Worksheets(1).Cells(Fila, 6) = Me.ListBox1.List(i, 5)
    Application.Workbooks(NombreArchivo).Worksheets(1).Cells(Fila, 7) = CDate(Me.ListBox1.List(i, 6))
    Application.Workbooks(NombreArchivo).Worksheets(1).Cells(Fila, 8) = CDate(Me.ListBox1.List(i, 7))
    Application.Workbooks(NombreArchivo).Worksheets(1).Cells(Fila, 9) = Me.ListBox1.List(i, 8)

    Fila = Fila + 1
    Next

    MsgBox "Los datos han sido exportados", vbInformation

End If

End Sub

The line that is getting highlighted is "objexcel.Activate"

Answer 1

No clue why the Activate-statement fails, but it is not necessary at all. A newly created workbook gets active automatically, but it is even not necessary to have it active. The variable objExcel is already set to the new workbook, so you can use it. I would advice to declare it as Workbook (not as object), also the name should be changed (but that's up to you).

You could use something like

Dim newWorkbook As Workbook
Set newWorkbook = Workbooks.Add

newWorkbook.Worksheets(1).Cells(1, 1) = "Delayed Filter"
newWorkbook.Worksheets(1).Cells(1, 2) = Me.ComboBox1
...

or

With newWorkbook.Worksheets(1)
    .Cells(1, 1) = "Delayed Filter"
    .Cells(1, 2) = Me.ComboBox1
    ...
End With