がらぱっぱ

自分用覚え書き中心（モバイル関係中心だったはずが）

トップ > プログラミング-EXCEL > EXCEL（VBA）でなるべく簡単にORACLEアクセス（Oracle Instant Client）

EXCEL（VBA）でなるべく簡単にORACLEアクセス（Oracle Instant Client）

プログラミング-EXCEL プログラミング プログラミング-ORACLE

EXCELからVBA経由でORACLEにアクセスしたいけどなるべくお手軽にやるには？
という事でちょっと試行錯誤した記録

 

前提

やりたい事

  • ExcelからSQL発行してシートに結果を張り付ける
  • なるべくインストールとか設定とか少なくできたらうれしい

結論（これがいいかどうかはわからない）

Oracle Instant Clientをインストール（BASIC＋ODBC）

ADOを使う

ODBC（Oracle社製）ドライバを使ってアクセス

やり方

Oracle Instant Clientのダウンロードとインストール

ここら辺の記事を参考にさせていただきました。

Oracle Instant Client を Windows にインストールする方法|Everything you do is practice

Oracle Instant Client をWindows ODBC データソースとして登録手順メモ – hrendoh's tech memo

ダウンロード

Windowsは64bitですがOfficeは32bitのため32ビット版をダウンロードする。

Instant Client for Windows 32-bit

このエントリーの執筆時点（2019/12/18）でのバージョンは19.5.0.0.0でした。
以下の２つをダウンロードします

  • Basic Package（Basic Light Packageでもよさそうな気もする）
  • ODBC Package

ダウンロードしたファイルは以下

instantclient-basic-nt-19.5.0.0.0dbru.zip

instantclient-odbc-nt-19.5.0.0.0dbru.zip

展開

 2つとも適当なフォルダに展開します。

※私の場合はC:\WinTool\instantclient_19_5

どちらもinstantclient_19_5というフォルダが作られるので同じディレクトリに展開します。

ODBCのインストール

 管理者としてコマンドプロンプトを開き展開したフォルダへ移動してコマンド実行

C:\WinTool\instantclient_19_5>odbc_install.exe JA
なぜかファイルが消えるので

odbc_installを実行すると、sqresus.dllが消されてしまうので、再度ODBCのファイル（instantclient-odbc-nt-19.5.0.0.0dbru.zip）を解凍しなおして再度コピーする必要があります。

※上記記事に助けられましたThanks!

 tnsnames.oraとかPATHとかODBCの設定はしない

EXCEL VBAからお手軽接続するので変な接続情報とかを設定で持たないようにEXCELから接続の時にどうにかするようにします。

EXCELからの接続

ADOオブジェクト生成

参照設定とか面倒なので

    Dim conn As Object              'セッション
    'セッションオブジェクトの生成
    Set conn = CreateObject("ADODB.Connection")

ってな具合でADODBのオブジェクト生成

接続

ここがいろいろ迷ったのですが

Works - データベース接続あれこれ

ここが参考になりました。

基本形

Driver={<ドライバ名>};DBQ=<接続文字列>;UID=<ユーザID>;PWD=<パスワード>
ドライバ名

ODBCのドライバ名を入れます。

ODBCデータソースアドミニストレータでドライバ名がわかりますね。

Window10でコントロールパネルがどこだかわからなくなったので、検索窓でODBCと入力すると「ODBC Data Source(32-bit)」と出てくるので、ODBCデータソースアドミニストレータ(32ビット)でドライバタブを選んでドライバ名を探します。

私のダウンロードしたバージョンであれば

Oracle in instantclient_19_5

でした

DriverString = ="Driver={" & ODBCdriver & "};"
接続文字列

接続文字列はtnsnames.oraの形式で指定するパターンと簡易接続（ホスト:ポート/SIDの形式）パターンがあります。

簡易接続文字列

ホスト:ポート/SIDの形式

ConnectString = "DBQ=" & HOST & ":" & PORT & "/" & SID & ";"

tnsnames.oraの形式

ConnectString = "DBQ=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" & HOST & ")(PORT=" & PORT & "))(CONNECT_DATA=(SID=" & SID & ")));"

 基本どっちでもいい（違いがよくわからないというのが本音）なので簡単なほうで！

接続文字列の例

Driver={Oracle in instantclient_19_5};DBQ=localhost:1521/orcl;UID=system;PWD=manager;

 Excelでのデータ取得

ここからは本エントリーの主題ではないのですがおまけで。

SELECT文を投げる

    Dim rs As Object                'データセット(レコードセット)
    Set rs = conn.Execute(SQLString)

フィールド名（カラム名）を取得する

    'フィールド名の表示
    Const ColStart = 1
    For colnum = 0 To rs.Fields.Count - 1
        sh.Cells(1, colnum + ColStart) = rs(colnum).Name
    Next

 データを取得する

    'データの表示
    rownum = 2
    Do Until rs.EOF
        For colnum = 0 To rs.Fields.Count - 1
            sh.Cells(rownum , colnum + ColStart).Value2 = Format(rs(colnum).Value)
        Next
        rs.MoveNext
        rownum = rownum + 1
    Loop

 

ということでソース

Public Sub selectResult(sh As Worksheet _
                      , ConnectString As String _
                      , SQLString As String)
    Dim conn As Object              'セッション
    Dim rs As Object                'データセット(レコードセット)
    Dim rownum As Long
    Dim colnum As Integer
    Const ColStart = 1
    Const RowStart = 1
        
    'セッションオブジェクトの生成
    Set conn = CreateObject("ADODB.Connection")
    conn.Open ConnectString
    Set rs = conn.Execute(SQLString)
    
    'すでに表示されている内容のクリア
    sh.Cells.Delete Shift:=xlUp
    sh.Cells.NumberFormatLocal = "@"
    With sh.Cells.Font
        .Name = "ＭＳゴシック"
        .FontStyle = "標準"
        .Size = 9
    End With
    'フィールド名の表示
    For colnum = 0 To rs.Fields.Count - 1
        sh.Cells(RowStart, colnum + ColStart) = rs(colnum).Name
    Next
    
    'データの表示
    rownum = RowStart + 1
    Do Until rs.EOF
        For colnum = 0 To rs.Fields.Count - 1
            sh.Cells(rownum, colnum + ColStart).Value2 = _
                Format(rs(colnum).Value)
        Next
        rs.MoveNext
        rownum = rownum + 1
    Loop
    'セル幅の自動調整
    sh.Columns("A:I").EntireColumn.AutoFit
    
    'オブジェクトのクローズ
    rs.Close
    conn.Close
    
    '各種オブジェクトの開放
    Set rs = Nothing
    Set conn = Nothing

End Sub

 呼び出し側

ConnectString = _
    "Driver={" & ODBCdriver & "};" & _
    "DBQ=" & HOST & ":" & PORT & "/" & SID & ";" & _
    "UID=" & user & ";PWD=" & Password & ";"
Call selectResult(Sheets(SheetName), ConnectString, SQLString)