Import Excel File into Oracle Db

BoLaFish 4:58 pm on December 29, 2009 | Answers: 1    
Tags: excel (44), import (12), oracle (120)

Rate Knowledge: (1 votes)

 Loading ...

• BoLaFish 4:58 pm on December 29, 2009
  

  =================================================================
  Sub OpenDb()
  Dim wrkODBC As Workspace
  Dim dbsOracle As Database
  Dim strpubmst As String
  Dim rstYourTable As Recordset
  On Error GoTo close_connection

  Set wrkODBC = CreateWorkspace(”ODBCWorkspace”, “system”, “”, dbUseODBC)
  Set dbsOracle = wrkODBC.OpenConnection(”YOUR_DATABASE_SID”, dbDriverComplete, True, “ODBC;DSN=;UID=YOUR_USER_ID;PWD=YOUR_PASSWORD;”)

  strpubmst = “Select * from YOUR_USER.YOUR_TABLE ”

  ‘ Set rstYourTable = dbsOracle.OpenRecordset(strpubmst, dbOpenDynaset, dbSQLPassThrough, dbOptimistic)
  Set rstYourTable = dbsOracle.OpenRecordset(strpubmst, dbOpenDynaset)
  MsgBox “sucess ” & rstYourTable.RecordCount
  With rstYourTable
  rw = 1
  cl = 1
  .MoveFirst
  Do While Not rstYourTable.EOF
  Cells(rw, cl).Value = !t1_code
  Cells(rw, cl + 1).Value = !t1_name
  Cells(rw, cl + 2).Value = !t1_short_name
  Cells(rw, cl + 3).Value = !t1_type
  Cells(rw, cl + 4).Value = !t1_no_prts
  rw = rw + 1
  .MoveNext
  Loop
  End With

  close_connection:
  dbsOracle.Close
  wrkODBC.Close

  End Sub