424 Object Required (1 Viewer)

[alexfwalker81]

I'm getting a 424 Object Required error when I try to run this. Attempting to debug by Google, but I can't see where I'm making an error - guessing it's in the syntax around rs.fields.

Public Function WriteToExcel()

Dim rs
Dim db As DAO.Database

Dim oExcel As Excel.Application
Dim oExcelWrkBk As Excel.Workbook
Dim oExcelWrSht As Excel.Worksheet

Set db = CurrentDb
Set rs = CurrentDb.OpenRecordset("qry_latest_entry")
    
'Start Excel
On Error Resume Next
Set oExcel = GetObject(, "Excel.Application")
If Err <> 0 Then
    Err.Clear
    On Error GoTo Error_Handler
    Set oExcel = CreateObject("Excel.Application")
Else
    On Error GoTo Error_Handler
End If

oExcel.ScreenUpdating = False
oExcel.Visible = False 'This is false by default anyway

Set oExcelWrkBk = oExcel.Workbooks.Open("Y:\alextest.xlsx")
Set oExcelWrSht = oExcelWrkBk.Sheets("tbl_BACS_entry")

If rsID.EOF = True Then

Else
    Do Until rs.EOF = True

oExcelWrSht.Cells(oExcelWrSht.Rows.Count, 1).End(xlUp).Offset(1, 0) = rs.Fields("ID")
oExcelWrSht.Cells(oExcelWrSht.Rows.Count, 2).End(xlUp).Offset(1, 0) = rs.Fields("DepositType")
oExcelWrSht.Cells(oExcelWrSht.Rows.Count, 3).End(xlUp).Offset(1, 0) = rs.Fields("DateReceived")
oExcelWrSht.Cells(oExcelWrSht.Rows.Count, 4).End(xlUp).Offset(1, 0) = rs.Fields("ChequeDate")
oExcelWrSht.Cells(oExcelWrSht.Rows.Count, 5).End(xlUp).Offset(1, 0) = rs.Fields("ChequeNumber")
oExcelWrSht.Cells(oExcelWrSht.Rows.Count, 6).End(xlUp).Offset(1, 0) = rs.Fields("PayeeName")
oExcelWrSht.Cells(oExcelWrSht.Rows.Count, 7).End(xlUp).Offset(1, 0) = rs.Fields("PayeeReference")
oExcelWrSht.Cells(oExcelWrSht.Rows.Count, 8).End(xlUp).Offset(1, 0) = rs.Fields("AmountDeposited")
oExcelWrSht.Cells(oExcelWrSht.Rows.Count, 9).End(xlUp).Offset(1, 0) = rs.Fields("Comments")

        rs.MoveNext
    Loop
End If

rs.Close
Set rs = Nothing

oExcelWrSht.Range("A1").Select

oExcelWrkBk.Save


oExcel.ScreenUpdating = True
oExcel.Visible = False

Exit_Point:
Set oExcelWrSht = Nothing
Set oExcelWrkBk = Nothing
Set oExcel = Nothing
Exit Function

Error_Handler:
MsgBox Err & " - " & Err.Description
GoTo Exit_Point

End Function

 

[theDBguy]

Hi. To troubleshoot which line is causing the error, try disabling the error handler temporarily.

 

[The_Doc_Man]

You guess it is around a use of rs.fields but you have several similar references. Does the debugger stop you and indicate a specific line? It would really help to know exactly which line trips up.

I will also suggest that if you try to debug, if you put a breakpoint in that code and single-step through it, you could use any of several ways to verify that the things you wanted to be open really ARE open. (Because that error tells me that SOMETHING is NOT open that should be.)

However, I'm a little curious. Are you trying to set values in the bottom row of the existing sheet (plus the offset)? Mightn't it be easier to catch a variable outside the loop to identify the last row, then inside the loop keep adding one to it? Just thinking out loud. It would be less typing and far be it from me to say that what you did is wrong, because it isn't, but I just like to type less.

I also agree with theDBguy's suggestion. I'm thinking something ELSE didn't open correctly, and the object that is missing is earlier than your rs.fields lines.

 

[MajP]

If rsID.EOF = True Then

 

[alexfwalker81]

If rsID.EOF = True Then

Yep, that was the problem!

 

[alexfwalker81]

However, I'm a little curious. Are you trying to set values in the bottom row of the existing sheet (plus the offset)? Mightn't it be easier to catch a variable outside the loop to identify the last row, then inside the loop keep adding one to it? Just thinking out loud. It would be less typing and far be it from me to say that what you did is wrong, because it isn't, but I just like to type less.

Funnily enough, I've been thinking exactly the same. Part of my problem is that I don't really know how to do any of this stuff, so I have to nick sections of code that I find on the internet and attempt to repurpose them as best I can!

I've tidied it up a bit;

Public Function WriteToExcelBACS()

Dim rs
Dim db As DAO.Database

Dim oExcel As Excel.Application
Dim oExcelWrkBk As Excel.Workbook
Dim oExcelWrSht As Excel.Worksheet

Set db = CurrentDb
Set rs = CurrentDb.OpenRecordset("Query2")
    
'Start Excel
On Error Resume Next
Set oExcel = GetObject(, "Excel.Application")
If Err <> 0 Then
    Err.Clear
    On Error GoTo Error_Handler
    Set oExcel = CreateObject("Excel.Application")
Else
    On Error GoTo Error_Handler
End If

oExcel.ScreenUpdating = False
oExcel.Visible = False 'This is false by default anyway

Set oExcelWrkBk = oExcel.Workbooks.Open("Y:\alextest.xlsx")
Set oExcelWrSht = oExcelWrkBk.Sheets("tbl_BACS_entry")

While Not rs.EOF

oExcelWrSht.Cells(oExcelWrSht.Rows.Count, 1).End(xlUp).Offset(1, 0) = rs.Fields("DateReceived")
oExcelWrSht.Cells(oExcelWrSht.Rows.Count, 2).End(xlUp).Offset(1, 0) = rs.Fields("PayeeName")
oExcelWrSht.Cells(oExcelWrSht.Rows.Count, 3).End(xlUp).Offset(1, 0) = rs.Fields("PayeeReference")
oExcelWrSht.Cells(oExcelWrSht.Rows.Count, 4).End(xlUp).Offset(1, 0) = rs.Fields("AmountDeposited")
oExcelWrSht.Cells(oExcelWrSht.Rows.Count, 12).End(xlUp).Offset(1, 0) = rs.Fields("Comments")
oExcelWrSht.Cells(oExcelWrSht.Rows.Count, 15).End(xlUp).Offset(1, 0) = rs.Fields("AgentAllocated")
oExcelWrSht.Cells(oExcelWrSht.Rows.Count, 14).End(xlUp).Offset(1, 0) = "Y"

rs.MoveNext
Wend

rs.Close
Set rs = Nothing

oExcelWrSht.Activate

oExcelWrSht.Range("A1").Select

oExcelWrkBk.Save


oExcel.ScreenUpdating = True
oExcel.Visible = False

Exit_Point:
Set oExcelWrSht = Nothing
Set oExcelWrkBk = Nothing
Set oExcel = Nothing
Exit Function

Error_Handler:
MsgBox Err & " - " & Err.Description
GoTo Exit_Point

End Function

Which element would I change to just find the last row and put all values in the respective columns (1,2,3,4,12,15,14) along that? One problem with this script is that if there's an empty cell in the row above, then it populates that, which is not really what I want...

 

[alexfwalker81]

Figured it out;

While Not rs.EOF

oExcelWrSht.Cells(oExcelWrSht.Rows.Count, 1).End(xlUp).Offset(1, 0) = rs.Fields("DateReceived")
oExcelWrSht.Cells(oExcelWrSht.Rows.Count, 1).End(xlUp).Offset(0, 1) = rs.Fields("PayeeName")
oExcelWrSht.Cells(oExcelWrSht.Rows.Count, 1).End(xlUp).Offset(0, 2) = rs.Fields("PayeeReference")
oExcelWrSht.Cells(oExcelWrSht.Rows.Count, 1).End(xlUp).Offset(0, 3) = rs.Fields("AmountDeposited")
oExcelWrSht.Cells(oExcelWrSht.Rows.Count, 1).End(xlUp).Offset(0, 11) = rs.Fields("Comments")
oExcelWrSht.Cells(oExcelWrSht.Rows.Count, 1).End(xlUp).Offset(0, 14) = rs.Fields("AgentAllocated")
oExcelWrSht.Cells(oExcelWrSht.Rows.Count, 1).End(xlUp).Offset(0, 13) = "Y"

rs.MoveNext
Wend

 

[The_Doc_Man]

Outside the loop, have a LONG (integer) variable.

RowVar = oExcelWrSht.Rows.Count

That puts the last row number in the variable.

Now in those places where you have the RS.Fields references,

While Not Rs.EOF

  RowVar = RowVar + 1

  oExcelWrSht.Calls(RowVar, 1) = rs.Fields(...
  oExcelWrSht.Calls(RowVar, 2) = rs.Fields(...

I'm pretty sure you can do away with the Offset entirely in that case. It LOOKS like you are defining a range so you can get to a cell and "simulate" pressing an up-arrow to imply a direction. But that is overkill. You can just directly address the cell knowing the row and the column.