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"

I recommend giving [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) a read through. You almost never need to Select or Activate anything. It would be better to use a workbook object and work through that. — Warcupine, May 20 '20 at 12:44

score 0 · Accepted Answer · answered May 20 '20 at 12:45

0

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

answered May 20 '20 at 12:45

FunThomas

23,043
3
18
34

Hi, i did the changes but now it says "Run-time Error '-2147221080 (800401a8)': Method 'Worksheets' of objects '_Workbook' Failed: Dim NewWorkbook As Workbook Dim i As Integer, Row As Integer If MsgBox("Seguro que desea exportar en excel?", vbYesNo + vbQuestion) = vbYes Then Application.ScreenUpdating = False Set NewWorkbook = Workbooks.Add With NewWorkbook.Worksheets(1) the *With newWorkbook.worksheets(1)* is getting highlighted – Luis Eduardo Rosa May 20 '20 at 13:21
Have no clue, never saw that error. Change the line creating the Workbook to `Application.Workbooks.Add` and see if that helps. Also, put a break point to the statement after the creation and if code stops there, inspect the content of the `newWorkbook` variable in the *Locals Window* (View->Locals) – FunThomas May 20 '20 at 14:07
Hi, thank you @FunThomas , i fixed the error with `Dim WbName as string` ,`WbName = ActiveWorkbook.Name` and `Application.Workbooks(WbName).Worksheets(1)` – Luis Eduardo Rosa May 20 '20 at 14:27

score 0 · Answer 2 · answered May 20 '20 at 14:26

0

Try to declare the object as Excel Workbook:

Dim objexcel As Excel.Workbook
'
Set objexcel = Workbooks.Add

answered May 20 '20 at 14:26

Eugene Astafiev

47,483
3
24
45

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

2 Answers2