ablog

不器用で落着きのない技術者のメモ

Excel 2007 + Oracle Instant Client で Oracleデータベースにアクセスしてみる

Oracle Excel

個人的には興味ないけど、訳あってちょっと調べてみた。

Oracle Instant Client をインストールする

>cd C:\Program Files\instantclient_10_2
>dir /b
classes12.jar
glogin.sql
oci.dll
ocijdbc10.dll
ociw32.dll
ODBCRelnotesJA.htm
ODBCRelnotesUS.htm
ODBC_IC_Readme_Win.html
odbc_install.exe
odbc_uninstall.exe
ojdbc14.jar
orannzsbb10.dll
oraocci10.dll
oraociei10.dll
Orasqlplusic10.dll
sqlplus.exe
sqora.chm
sqora32.dll
sqoraja.chm
sqoras32.dll
sqresja.dll
sqresus.dll

Path: ;C:\Program Files\instantclient_10_2 <--末尾に追記
TNS_ADMIN: C:\Program Files\instantclient_10_2
  • 接続してみる。
(コマンドプロンプトから sqlplus を実行する)
>sqlplus scott/tiger@192.168.0.144:1521/orcl

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Nov 6 18:03:13 2009

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Release 10.2.0.4.0 - Production

ODBC の設定を行う

  • C:\Program Files\instantclient_10_2\tnsnames.ora を作成する。
orcl =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.144)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
  • [スタート]-[設定]-[コントロールパネル]-[管理ツール]-[データソース(ODBC)]-[システムDSN]-[追加] で [Oracle in instantclient10_2] を選択し、以下の通り設定する。
    • Data Source Name: orcl
    • TNS Service Name: orcl
    • User ID: scott
  • [Test Connection] を押下し、[Pasword]を入力して、[OK]を押下すると「Connection Successful」と表示されることを確認する。

EXCEL 2007 から Oracle データベースに接続する

  • EXCEL 2007 を起動する。
  • [Office ボタン]-[Excelのオプション]-[基本設定]-[Excelの使用に関する基本オプション]で[[開発]タブをリボンに表示する]にチェックを入れて、[OK]を押下する。
  • [開発]タブ-[Visual Basic]を選択する。
  • [VBAProject (Book1)]-[右クリック]-[挿入]-[標準モジュール]を選択し、以下のコードをコピペし、保存する。
' 接続先情報の設定
Const oracle_sid = "orcl" 'sid
Const oracle_user = "scott" ' user
Const oracle_password = "tiger" ' pass

Sub Sample001()

    ' 接続オブジェクトの作成
    Set cn = CreateObject("ADODB.Connection")
    ' 接続
    cn.Open "Driver={Oracle in instantclient};" & "Data Source=" & _
            oracle_sid & ";", oracle_user, oracle_password

    ' SQL
    user_sql = "select * from user_tables"
    
    ' SQLの実行
    Set rs = cn.Execute(user_sql)

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

    ' 接続切断
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

End Sub
  • [Microsoft Visual Basic] を閉じる。
  • [開発]タブ-[マクロ]で、「Sample001」を選択し、[実行]を押下する。

参考