Excel VBA Oracleに接続してselectするサンプル

Excel VBAのOracleに接続してselectするサンプルです。

設定	参照の設定
コード	接続してselectを実行する

参照の設定

参照の設定を行います。

１．「ツール」→「参照設定」をクリックします。

２．参照可能なライブラリで「Microsoft ActiveX Data Objects 2.8 Library」にチェックをいれOKを押します。

チェックをしないで実行するとADODB.Connectionの行で
「ユーザ定義型は定義されていません。」のコンパイルエラーが表示されます。

接続してselectを実行する

Oracleに接続してselectを実行するサンプルです。

DBは、Oracle Express 11gの64bitでローカルにあります。Excelも64bitです。
tnsnames.oraが必要なコードです。

Sub test1()
    Dim SQL As String
    Dim i As Long
    Dim row As Long

    Const Provider = "OraOLEDB.Oracle" 'Provider
    Const DATA_SOURCE = "XE"           'Data Source
    Const USER_ID = "hr"               'userid
    Const Password = "hr"              'password

    SQL = "Select * from SYAIN"
    
    Dim conn As New ADODB.Connection
        
    conn.ConnectionString = _
                    "Provider=" & Provider & ";" & _
                    "Data Source=" & DATA_SOURCE & ";" & _
                    "USER ID=" & USER_ID & ";" & _
                    "Password=" & Password & ";"
    conn.Open
    
    Dim rs As New ADODB.Recordset

    rs.Source = SQL
    rs.ActiveConnection = conn
    rs.Open
    
    With Worksheets("Sheet1")
        .Cells.Clear
        
        '列名の表示
        For i = 0 To rs.Fields.Count - 1
            .Cells(1, i + 1) = rs(i).Name
        Next i

        '値の表示
        row = 1
        Do Until rs.EOF
            For i = 0 To rs.Fields.Count - 1
                .Cells(row + 1, i + 1) = rs(i).Value
            Next i
            rs.MoveNext
            row = row + 1
        Loop
    End With
    
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub

7行目は、tnsnames.oraにある値です。
11行目は、SQL文の後ろにセミコロン(;)があるとエラーになるので注意が必要です。
27行目は、取得した値をSheet1に出力します。
31行目は、列名を出力します。
37行目からは、取得した値分ループで出力します。

実行結果

以下のように表示されます。

1行目は、DBの列名です。
2行目以降は、値です。

以下は、tnsnames.oraの該当の箇所です。

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

tnsnames.oraなしで記述する場合

tnsnames.oraなしで記述する場合は、上記の7行目を以下のコードに変えます。

Const DATA_SOURCE = "(DESCRIPTION=(ADDRESS=(PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = XE)))"

tnsnames.oraの値の部分を記述します。