Excel VBA - Application-defined or object-defined error

Question

I'm using the following code to extract some info from an oracle database. I've used this type of Oracle connection and SQL script many times but, this particular script throws up the Application-defined or object-defined error. The report populates 7625 lines before the error appears.

Any ideas?

Sub SQL_CDS()

Sheets("CDS Data").Select

Dim SQL As String
Dim orasession As Object
Dim oradatabase As Object
Dim dyprod As Object
Dim Row As Integer

Application.ScreenUpdating = False

Set orasession = CreateObject("OracleInProcServer.XOraSession") 'Create the OraSession Object. ( Oracle )
Set oradatabase = orasession.DbOpenDatabase("sid_cnded", "bo_bca_rep/bobcarep01", 0&) 'Create the OraDatabase Object by opening a connection to Oracle.

SQL = SQL & "SELECT selection_no as one, "
SQL = SQL & "selection_status as two, "
SQL = SQL & "onhand_cds as three, "
SQL = SQL & "allocated_cds as four, "
SQL = SQL & "nr_date as five, "
SQL = SQL & "last_8_weeks_shipped as six, "
SQL = SQL & "reorder_weeks_shipped as seven, "
SQL = SQL & "reorder_status_shipped as eight "
SQL = SQL & "FROM RPAT.BBC_CDS"

Set dyprod = oradatabase.CreateDynaset(SQL, 0&)

Sheets("CDS Data").Select
Row = 2
        If Not dyprod.EOF And Not dyprod.bof Then
                dyprod.movefirst
                       Do Until dyprod.EOF
                            Sheets("CDS Data").Cells(Row, 1).Select
                            ActiveCell.Value = dyprod.Fields("one").Value
                            Sheets("CDS Data").Cells(Row, 2).Select
                            ActiveCell.Value = dyprod.Fields("two").Value
                            Sheets("CDS Data").Cells(Row, 3).Select
                            ActiveCell.Value = dyprod.Fields("three").Value
                            Sheets("CDS Data").Cells(Row, 4).Select
                            ActiveCell.Value = dyprod.Fields("four").Value
                            Sheets("CDS Data").Cells(Row, 5).Select
                            ActiveCell.Value = dyprod.Fields("five").Value
                            Sheets("CDS Data").Cells(Row, 6).Select
                            ActiveCell.Value = dyprod.Fields("six").Value
                            Sheets("CDS Data").Cells(Row, 7).Select
                            ActiveCell.Value = dyprod.Fields("seven").Value
                            Sheets("CDS Data").Cells(Row, 8).Select
                            ActiveCell.Value = dyprod.Fields("eight").Value
                            dyprod.movenext
                            Row = Row + 1
                        Loop
                End If

    Range("A2").Select

End Sub

Thanks SMORF

Which line does the error occur? Why use ActiveCell? Why not simply put the value? — PaulFrancis
– PaulFrancis, Commented May 12, 2015 at 14:48
The error occurs at line 'ActiveCell.Value = dyprod.Fields("five").Value' ... I've always used ActiveCell ... never had an issue before? — SMORF
– SMORF, Commented May 12, 2015 at 14:51
If you do ?ActiveCell.Value in the VBE Immediate pane, do you get an error? What about ?dyprod.Fields("five").Value? — David Zemens
– David Zemens, Commented May 12, 2015 at 15:11
?ActiveCell.Value = no result, cell is empty - ?dyprod.Fields("five").Value = 31/12/999 — SMORF
– SMORF, Commented May 12, 2015 at 15:16

Tim Williams · Accepted Answer · 2015-05-12 19:55:15Z

Without the selecting (and using a Long as a row counter):

Sub SQL_CDS()

Sheets("CDS Data").Select

Dim SQL As String
Dim orasession As Object
Dim oradatabase As Object
Dim dyprod As Object
Dim Row As Long '<<<<<< avoid integer values when dealing with rows

Application.ScreenUpdating = False

Set orasession = CreateObject("OracleInProcServer.XOraSession") 'Create the OraSession Object. ( Oracle )
Set oradatabase = orasession.DbOpenDatabase("sid_cnded", "bo_bca_rep/bobcarep01", 0&) 'Create the OraDatabase Object by opening a connection to Oracle.

SQL = SQL & "SELECT selection_no as one, "
SQL = SQL & "selection_status as two, "
SQL = SQL & "onhand_cds as three, "
SQL = SQL & "allocated_cds as four, "
SQL = SQL & "nr_date as five, "
SQL = SQL & "last_8_weeks_shipped as six, "
SQL = SQL & "reorder_weeks_shipped as seven, "
SQL = SQL & "reorder_status_shipped as eight "
SQL = SQL & "FROM RPAT.BBC_CDS"

Set dyprod = oradatabase.CreateDynaset(SQL, 0&)

Sheets("CDS Data").Select 'not required

Row = 2
If Not dyprod.EOF And Not dyprod.bof Then
     dyprod.movefirst
     Do Until dyprod.EOF
          Sheets("CDS Data").Cells(Row, 1).Resize(1,8).Value = _
                            Array(dyprod.Fields("one").Value, _
                                  dyprod.Fields("two").Value, _
                                  dyprod.Fields("three").Value, _
                                  dyprod.Fields("four").Value, _
                                  dyprod.Fields("five").Value, _
                                  dyprod.Fields("six").Value, _
                                  dyprod.Fields("seven").Value, _
                                  dyprod.Fields("eight").Value)


         dyprod.movenext
         Row = Row + 1
     Loop
End If

Range("A2").Select

End Sub

Many thanks Tim, I also found that the oracle database holds a non date format where date is expected (31/12/0999 = cell value 31/12/999). This was causing the error at line 7625 - fixed in the SQL ... replace(to_char(nr_date,'dd/mm/yyyy'),'0999','9999')

Collectives™ on Stack Overflow

Excel VBA - Application-defined or object-defined error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related