Excel VBA 連線操作 Oracle 完整實作：ADO 連線、Recordset、參數化查詢、錯誤處理

Excel VBA 透過 ADO (ActiveX Data Objects) 連線 Oracle，需安裝 Oracle Client、引用 ADO 程式庫。本文提供完整可執行的類別模組程式碼，涵蓋連線字串組建、參數化查詢、結果集匯出工作表、交易控制、錯誤處理與資源釋放。

前置準備

1. 安裝 Oracle Client（Instant Client 或完整 Client），確認 tnsnames.ora 可連線
2. VBA 編輯器 → 工具 → 參考 → 勾選：
   • Microsoft ActiveX Data Objects 2.8 Library
   • Microsoft ActiveX Data Objects Recordset 2.8 Library

標準連線類別模組

' 類別模組：clsOracleDB
Option Explicit

Private m_cn As ADODB.Connection
Private m_connected As Boolean

Private Const CONN_TMPL = "Provider=OraOLEDB.Oracle;Data Source={0};User ID={1};Password={2};Persist Security Info=True;"

' 建立連線
Public Function Connect(ByVal tnsName As String, ByVal userName As String, ByVal pwd As String) As Boolean
    On Error GoTo ErrHandler
    Set m_cn = New ADODB.Connection
    m_cn.ConnectionString = Replace(Replace(Replace(CONN_TMPL, "{0}", tnsName), "{1}", userName), "{2}", pwd)
    m_cn.CursorLocation = adUseClient  ' 客戶端游標，支援絕對定位
    m_cn.Open
    m_connected = True
    Connect = True
    Exit Function
ErrHandler:
    m_connected = False
    Err.Raise vbObjectError + 513, "clsOracleDB.Connect", "Oracle 連線失敗: " & Err.Description
End Function

' 執行無回傳 SQL（INSERT/UPDATE/DELETE/DDL）
Public Function ExecuteNonQuery(ByVal sql As String, ParamArray params()) As Long
    On Error GoTo ErrHandler
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    With cmd
        Set .ActiveConnection = m_cn
        .CommandText = sql
        .CommandType = adCmdText
        .Prepared = True
        Call AppendParams(cmd, params)
        .Execute RecordsAffected:=ExecuteNonQuery
    End With
    Exit Function
ErrHandler:
    Err.Raise vbObjectError + 514, "ExecuteNonQuery", Err.Description
End Function

' 查詢回傳 Recordset
Public Function Query(ByVal sql As String, ParamArray params()) As ADODB.Recordset
    On Error GoTo ErrHandler
    Dim cmd As ADODB.Command, rs As ADODB.Recordset
    Set cmd = New ADODB.Command
    Set rs = New ADODB.Recordset
    With cmd
        Set .ActiveConnection = m_cn
        .CommandText = sql
        .CommandType = adCmdText
        .Prepared = True
        Call AppendParams(cmd, params)
    End With
    rs.CursorLocation = adUseClient
    rs.CursorType = adOpenStatic
    rs.LockType = adLockReadOnly
    rs.Open cmd
    Set Query = rs
    Exit Function
ErrHandler:
    Err.Raise vbObjectError + 515, "Query", Err.Description
End Function

' 參數化查詢輔助
Private Sub AppendParams(ByVal cmd As ADODB.Command, params() As Variant)
    Dim i As Long
    For i = LBound(params) To UBound(params) Step 2
        If i + 1 <= UBound(params) Then
            cmd.Parameters.Append cmd.CreateParameter(params(i), adVarChar, adParamInput, 4000, params(i + 1))
        End If
    Next i
End Sub

' 匯出 Recordset 到工作表（含欄位名）
Public Sub ExportToSheet(ByVal rs As ADODB.Recordset, ByVal ws As Worksheet, Optional ByVal startRow As Long = 1, Optional ByVal startCol As Long = 1)
    On Error GoTo ErrHandler
    Dim f As Long, c As Long
    ' 寫入欄位名
    For f = 0 To rs.Fields.Count - 1
        ws.Cells(startRow, startCol + f).Value = rs.Fields(f).Name
    Next f
    ' 寫入資料
    If Not rs.EOF Then
        rs.MoveFirst
        Do Until rs.EOF
            For f = 0 To rs.Fields.Count - 1
                ws.Cells(startRow + rs.AbsolutePosition, startCol + f).Value = rs.Fields(f).Value
            Next f
            rs.MoveNext
        Loop
    End If
    Exit Sub
ErrHandler:
    Err.Raise vbObjectError + 516, "ExportToSheet", Err.Description
End Sub

' 關閉連線
Public Sub Disconnect()
    On Error Resume Next
    If m_connected Then m_cn.Close
    Set m_cn = Nothing
    m_connected = False
End Sub

' Class_Terminate 確保資源釋放
Private Sub Class_Terminate()
    Disconnect
End Sub

標準模組呼叫範例

' 標準模組：modMain
Option Explicit

Public Sub DemoOracleQuery()
    Dim db As New clsOracleDB
    Dim rs As ADODB.Recordset
    On Error GoTo ErrHandler

    If db.Connect("ORCL", "scott", "tiger") Then
        ' 參數化查詢防注入
        Set rs = db.Query("SELECT empno, ename, sal FROM emp WHERE deptno = ? AND sal > ?", "deptno", 20, "sal", 2000)
        ' 匯出到工作表 Sheet1
        Call db.ExportToSheet(rs, ThisWorkbook.Sheets("Sheet1"))
        rs.Close
        Set rs = Nothing
    End If
    db.Disconnect
    Set db = Nothing
    MsgBox "查詢匯出完成", vbInformation
    Exit Sub
ErrHandler:
    MsgBox "錯誤: " & Err.Description, vbCritical
    If Not rs Is Nothing Then If rs.State = adStateOpen Then rs.Close
    If Not db Is Nothing Then db.Disconnect
End Sub

進階：交易控制

Public Sub TransferFunds(ByVal fromAcc As String, ByVal toAcc As String, ByVal amt As Currency)
    Dim db As New clsOracleDB
    On Error GoTo ErrHandler
    If db.Connect("ORCL", "bank_user", "pwd") Then
        db.m_cn.BeginTrans
        db.ExecuteNonQuery "UPDATE accounts SET balance = balance - ? WHERE acc_no = ?", "amt", amt, "acc", fromAcc
        db.ExecuteNonQuery "UPDATE accounts SET balance = balance + ? WHERE acc_no = ?", "amt", amt, "acc", toAcc
        db.m_cn.CommitTrans
    End If
    Exit Sub
ErrHandler:
    If Not db Is Nothing Then On Error Resume Next: db.m_cn.RollbackTrans
    Err.Raise Err.Number, "TransferFunds", Err.Description
End Sub

常見問題排查

VBA + ADO + Oracle 是輕量級報表自動化、資料匯入匯出的經典組合。**參數化查詢、錯誤處理、資源釋放** 三件套寫好，生產環境才穩定可靠。

測試環境：Windows XP SP3 / 2003 SP2 + Office 2003 + Oracle Client 10g/11g