Excel VBAからOracle DBのデータを取得する(oo4oもODBCも使わない)

はじめに

Excel VBAからデータベースに接続し、データを取得、Excelに反映したいということは業務上とよくあると思います。
こういったとき一般的にはoo4oやODBCを使用すると思います。
しかし、セットアップされているoracle clientが64bit版の場合は上記の手法が使用できません。

そこで今回誰もが思いつく回りくどい方法でExcel VBAからOracle DBのデータを取得、シートに反映してみたいとおもいます。
（32bt版ぐらい自由に入れさせてくれ）

方法

以下のような方法でやりたいこと実現します。

1. VBAで「実行したいSQL文」を記載したテキストファイルを作成する。
2. VBAでsqlplusを叩き、作成したテキストファイルを実行する。
3. sqlplusから吐き出したテキストファイルをExcelに取り込む

ソース

取得で使用するSQLとか、Excelの出力セル・書式等は必要に応じて書き換えて使ってください。

Const tempDirectry = "D:\hoge\"     ' 作業ディレクトリ

Const tempSQLFile = "tempsql.sql"   ' sqlplusのコマンド
Const tempBATFile = "tempbat.bat"   ' 実行するバッチ
Const tempCSVFile = "tempcsv.csv"   ' 取得結果の一時ファイル

Const dbAccess = "hoge@piyo/fuga" ' 接続先の情報（ユーザー@TNS設定/パスワード)

Public Function oracleSelect()
    Dim obj As Object
    Dim sqlQuery As String ' 実行したいSQL文
    Dim csvBuf As String   ' csv出力をする際の一時領域

    ' 実行するSQL文の生成
    sqlQuery = "select * from hogehoge where piyopiyo = 'fugafuga';"

    'コマンドファイルの作成
    Open tempDirectry & tempSQLFile For Output As 1
        Print #1, "SET ECHO OFF"
        Print #1, "SET LINESIZE 32767"
        Print #1, "SET PAGESIZE 50000"
        Print #1, "SET HEADING ON"
        Print #1, "SET UNDERLINE OFF"
        Print #1, "SET NEWPAGE NONE"
        Print #1, "SET FEEDBACK OFF"
        Print #1, "SET COLSEP ','"
        Print #1, "SET TRIMSPOOL ON"
        Print #1, "SPOOL " & tempDirectry & tempCSVFile
        Print #1, sqlQuery
        Print #1, "SPOOL OFF"
        Print #1, "EXIT"
        Print #1, ""
    Close 1

    ' sqlplusを起動
    Set obj = CreateObject("WScript.Shell")
    Call obj.Run("sqlplus " & dbAccess & " @" & tempDirectry & tempSQLFile, WaitOnReturn:=True)
    Set obj = Nothing

    ' コマンドファイルを削除
    Kill tempDirectry & tempSQLFile

    ' CSVをExcelに出力
    Dim row As Integer ' 現在の出力行数。出力先のセル座標を特定するのに使用
    row = 0

    Open tempDirectry & tempCSVFile For Input As #1
        Do Until EOF(1)
            ' CSVの行単位で処理を回す
            Line Input #1, csvBuf

            ' 行をカラム単位に分割
            Dim cols As Variant
            cols = Split(csvBuf, ",")

            ' カラム単位で出力
            Dim col As Integer
            For col = 0 To UBound(cols)
                ' カラムのデータを転記する
                ' ここで書式の設定等も行うとExcelの見栄えが良くなる
                ActiveCell.Offset(row, col).Value = Trim(cols(col))
            Next col
            row = row + 1
        Loop
    Close #1

    ' CSVを削除
    Kill tempDirectry & tempCSVFile
End Function