1 Reply Latest reply on Feb 17, 2003 9:33 PM by 271367

    Automation Error

    271367
      Here is my code to get items from an Excel Spreadsheet to Oracle:

      Sub Insert_Data()

      Dim OraSession As Object
      Dim OraDatabase As Object

      Dim ACellVar As String
      Dim R As Long

      ' *** Change starting address to suit ***

      Set ACellRange = Range("A2")
      R = 1
      Do
      If IsEmpty(ACellRange.Cells(R, 1)) Then Exit Do
      'ACellVar = ACellRange.Cells(R, 1).Text
      PSIMKEY = Val(ACellRange.Cells(R, 1).Text)
      PSCPKEY = Val(ACellRange.Cells(R, 2).Text)
      PSPIECENO = Val(ACellRange.Cells(R, 3).Text)
      PSCODE = Val(ACellRange.Cells(R, 4).Text)
      PSCRDATE = Format$(Now, "dd-mmm-yyyy")
      PSMDDATE = Format$(Now, "dd-mmm-yyyy")
      PSOPNUM = Val(ACellRange.Cells(R, 7).Text)
      PSDIM1 = Val(ACellRange.Cells(R, 8).Text)
      PSDIM2 = Val(ACellRange.Cells(R, 9).Text)
      PSDIM3 = Val(ACellRange.Cells(R, 10).Text)
      PSQTYP = Val(ACellRange.Cells(R, 11).Text)
      PSQTYL = Val(ACellRange.Cells(R, 12).Text)
      PSSRATE = Val(ACellRange.Cells(R, 13).Text)
      PSOFFSET = Val(ACellRange.Cells(R, 14).Text)
      PSESTCOST = Val(ACellRange.Cells(R, 15).Text)
      PSEDATE = Format$(Now, "dd-mmm-yyyy")
      PSDDATE = Format$(Now, "dd-mmm-yyyy")
      PSREV = Val(ACellRange.Cells(R, 18).Text)
      PSTYPE = Val(ACellRange.Cells(R, 19).Text)
      PSECODE = Val(ACellRange.Cells(R, 20).Text)
      PSDESCR = Val(ACellRange.Cells(R, 21).Text)
      PSCERT1 = Val(ACellRange.Cells(R, 22).Text)
      PSCERT2 = Val(ACellRange.Cells(R, 23).Text)
      PSCERT3 = Val(ACellRange.Cells(R, 24).Text)
      PSCERT4 = Val(ACellRange.Cells(R, 25).Text)
      R = R + 1

      'Insert Rows
      Set OraSession = CreateObject("OracleInProcServer.XOraSession")
      Set OraDatabase = OraSession.OpenDatabase("QSDB", "QSDB_USER/plethora", 0&)
      OraDatabase.ExecuteSQL ("INSERT INTO PS (PS_IMKEY,PS_CPKEY,PS_PIECE_NO,PS_CODE" & _
      "PS_CRDATE,PS_MDDATE,PS_OP_NUM,PS_DIM_1,PS_DIM_2,PS_DIM_3,PS_QTY_P,PS_QTY_L" & _
      "PS_S_RATE,PS_OFFSET,PS_EST_COST,PS_E_DATE,PS_D_DATE,PS_REV,PS_TYPE,PS_E_CODE" & _
      "PS_DESCR,PS_CERT1,PS_CERT2,PS_CERT3,PS_CERT4) VALUES ('" & PSIMKEY & "','" & _
      PSCPKEY & "','" & PSPIECENO & "','" & PSCODE & "','" & PSCRDATE & "','" & _
      PSMDDATE & "','" & PSOPNUM & "','" & PSDIM1 & "','" & PSDIM2 & "','" & PSDIM3 & _
      "','" & PSQTYP & "','" & PSQTYL & "','" & PSSRATE & "','" & PSOFFSET & "','" & _
      PSESTCOST & "','" & PSEDATE & "','" & PSDDATE & "','" & PSREV & "','" & PSTYPE & _
      "','" & PSECODE & "','" & PSCERT1 & "','" & PSCERT2 & "','" & PSCERT3 & "','" & _
      PSCERT4 & "')")
      Loop
      End Sub


      I keep getting the "Automation Error" error and it doesn't tell my why or where. Can someone help? Thanks, Jeremy