Thinking in Robert

工作上需要，從Excel 裡面，把資料放上 Oracle，

終於找到這個連線範例，真的好用啊 ^^

參考來源

'以下為 Select 範例
'ADO - Query Oracle from Excel via ODBC driver
Private Sub CB_SELECT_Click()
  Dim con As Object, com As Object, rec As Object
  Set con = CreateObject("ADODB.Connection")
  Set com = CreateObject("ADODB.Command")
  Set rec = CreateObject("ADODB.Recordset")
  On Error GoTo ADO_Err
  '星號部分依實際設定自行修改, 即 ORACLE TNS 的設定
  con.Open "DRIVER={Microsoft ODBC for Oracle};UID=XXX;PWD=XXXXX;SERVER=XXXXXX;"
  With com
      .ActiveConnection = con
      .CommandType = 1 '1=adCmdText 4=adCmdStoredProc
      .CommandText = "SELECT TO_CHAR(SYSDATE,'YYYYMMDD') AS TIME1, TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') AS TIME2 FROM DUAL"
  End With
  Set rec = com.Execute
  '整個複製到工作表
  Cells(1, 1).CopyFromRecordset rec
 
  '讀取欄名方式一
  For Each f In rec.Fields
      Debug.Print "1 : " & f.Name
  Next
 
  '讀取欄名方式二　PS. rec(0)為 rec.Fields(0) 之簡化寫法
  Debug.Print "2 : " & rec(0).Name & ", " & rec(1).Name
 
  '直接讀取Recordset資料
  While Not rec.EOF 'Loop: 適用多筆
     Debug.Print "3 : " & rec(0) & ", " & rec(1)
     rec.MoveNext
  Wend
 
  rec.Close: con.Close
 
1 Set con = Nothing: Set com = Nothing: Set rec = Nothing
  Exit Sub
 
ADO_Err:
  MsgBox Err & vbLf & Err.Description, 16
  GoTo 1
End Sub

'以下為 Insert 範例

'ADO - INSERT Oracle from Excel via ODBC driver
Private Sub CommandButton1_Click()
  Dim con As Object, com As Object, rec As Object
  Set con = CreateObject("ADODB.Connection")
  Set com = CreateObject("ADODB.Command")
  Set rec = CreateObject("ADODB.Recordset")
  On Error GoTo ADO_Err
  'X號部分依實際設定自行修改，即 ORACLE TNS 的設定
  con.Open "DRIVER={Microsoft ODBC for Oracle};UID=XXX;PWD=XXXXX;SERVER=XXXXXX;"
  With com
      .ActiveConnection = con
      .CommandType = 1 '1=adCmdText 4=adCmdStoredProc
      .CommandText = "INSERT INTO TB_LOG_TABLE(MSG, KEY, INSERT_USER) VALUES ( '123123', '123123', 'Robert') "
  End With
  Set rec = com.Execute
  con.Commit
 
1 Set con = Nothing: Set com = Nothing: Set rec = Nothing
  Exit Sub
 
ADO_Err:
  MsgBox Err & vbLf & Err.Description, 16
  GoTo 1
End Sub