VBA Oracleへ接続してselect文を実行する方法 -Excel

VBAでOracleへ接続してselect文を実行する方法です。

VBAでOracleへ接続してselect文を実行する方法

ある案件でExcel VBAからOracle接続する必要が出てきた。正直、VBAもOracleも得意な方だけど、両方を組み合わせて利用したことはこれまで一度もなかったなっと。

「ADO」を使ったら簡単に接続できたので記事に残しておきます。Oracle ClientはインストールしてもしなくてもOKです。

ここでは VBAでOracleへ接続してselect文を実行する方法 を紹介します。

ADOとは？

ADOとは「ActiveX Data Objects」の略で、プログラムから外部データベース接続するための手段です。今回はOracleへの接続に利用しますが、SQLServerやAccess、Excel、テキストファイルへも接続できます。

ADOオブジェクトを使えるようにするためには「参照設定」を追加する方法が一般的なようですが、今回は参照設定なしでVBAコードで設定を書く方法で実施します。これにより余計な設定が不要となり、環境依存しないで利用できます。

環境情報

今回、検証で接続したOracleのバージョンは「Oracle 12c」及び「Oracle 19c」です。

Oracle Clientがインストールされている必要はありません。

Oracle側へテーブルとデータを準備する

今回例題で使うテーブルです。データは適当に準備してください。

無理にこのテーブルを使う必要はありません。すでにテーブルがあればそれを使ってください。


CREATE TABLE EMPLOYEES (
  ID          VARCHAR2(255) NOT NULL,
  NAME        NVARCHAR2(255) NOT NULL,
  EMAIL       VARCHAR2(255),
  SEX         VARCHAR2(1),
  SALARY      NUMBER,
  CREATED_AT  DATE NOT NULL,
  UPDATED_AT  DATE,
  CONSTRAINT PK_EMPLOYEES PRIMARY KEY (ID) USING INDEX
);

Excelへボタンを配置する

Excelへボタンを配置します。

ヘッダーメニュー「開発」から「挿入」を選んで「ボタン（フォームコントロール）」を選択します。

ヘッダーメニュー開発から挿入を選んでボタン（フォームコントロール）を選択する

ボタンの名前はなんでもいいですが、ここでは「Oracle 接続」としています。セルA2あたりに配置しましょう。

セルA2あたりにボタンを配置する

標準モジュールを追加する

ヘッダーメニュー「開発」から「Visual Basic」を開いて、標準モジュールを追加します。

標準モジュールを追加する

以下のコードを貼り付けます。


'**
'* Oracle 12cへ接続・データ取得・表示
'**
Sub OracleConnection()

  Dim SQL As String
  Dim i As Long
  Dim row As Long: row = 5
  Dim ws As Worksheet

  ' Oracle接続先情報
  Const Provider = "OraOLEDB.Oracle"
  'Const DataSource = "oracle" ' tnsnames.oraを使用する場合
  Const DataSource = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=サーバ名)(PORT=ポート番号))(CONNECT_DATA=(SERVICE_NAME=サービス名)))" ' 接続情報を直接記載する場合
  Const UserId = "scott"
  Const Password = "tiger"

  ' インスタンス作成
  Dim cn As Object
  Dim rs As Object
  Set cn = CreateObject("ADODB.Connection")
  Set rs = CreateObject("ADODB.Recordset")

  ' Oracle接続
  cn.ConnectionString = _
          "Provider=" & Provider & ";" & _
          "Data Source=" & DataSource & ";" & _
          "User Id=" & UserId & ";" & _
          "Password=" & Password & ";"
  cn.Open

  ' SQL文の作成
  SQL = "select * from EMPLOYEES Order by ID"

  ' SQL文の実行
  rs.ActiveConnection = cn
  rs.Source = SQL
  rs.Open

  Set ws = ActiveSheet
  With ws
    '列名の表示
    For i = 0 To rs.Fields.Count - 1
      .Cells(row, i + 1) = rs(i).Name
    Next i
    '値の表示
    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

  ' Oracle接続解除
  rs.Close
  cn.Close
  Set rs = Nothing
  Set conn = Nothing
End Sub

コードの説明

最初にOracleの接続先情報を設定します。tnsnames.oraの設定情報を参照する方法と、ソースコード内に直接記載するの2種類があります。環境に合わせて変更してください。


' Oracle接続先情報
Const Provider = "OraOLEDB.Oracle"
'Const DataSource = "oracle" ' tnsnames.oraを使用する場合
Const DataSource = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=サーバ名)(PORT=ポート番号))(CONNECT_DATA=(SERVICE_NAME=サービス名)))" ' 接続情報を直接記載する場合
Const UserId = "scott"
Const Password = "tiger"

まあ、後はコメント読めばわかるでしょうってことで省略します。（雑っ！

追加したボタンへマクロを埋め込む

追加したボタンを右クリックして「マクロの登録」を選択します。

ボタンを右クリックしてマクロの登録を選択する

マクロ名に追加した関数を設定すれば完了です。

関数を設定する

実行結果

実行結果です。

VBAでOracle接続とselect文実行した結果

ふむふむ、うまくできました。

まとめ

VBAでOracleへ接続してselect文を実行する方法を紹介しました。

筆者の記憶だと「ADO」ってWin95とかWin98の時からあったような気がする。VBAでRDBを操作できる便利なCOMコンポーネントの一つで、当時はAccessの開発でOracle接続していた記憶があるなー。

サクッとOracleへ接続できて、ピョピョっとデータ表示できてしまうなんて、めっちゃ便利なんじゃもん。

皆さんもADOと戯れてみてください。

おつかれさまでした。

この記事がお役に立ちましたら シェア をお願いいたします。