IT Soldier Sakuri !!

Oracle使い。いつのまにかIT戦士になってしまったさくりの可哀想な奮闘記。

Oracle活用 Excelからデータを取得する①

今日はめっちゃ暑い中、東京ビッグサイトの「Maker Faire Tokyo」に行ってきました!
f:id:itsoldiersakuri:20150801212212j:plain

色々面白い発明(?)があったけど、私の中での一番はコレ!
きのこたけのこ判別機!!!
f:id:itsoldiersakuri:20150801212233j:plain
イデア的に天才だった。可愛い。

あと面白かったのはメントスコーラショーもなかなかすごかった!
でも炎天下の中説明が長くてちょっときつかった…。
f:id:itsoldiersakuri:20150801212255j:plain




さて、今日はせっかくOracleClientをインストールしたので、
何かを介してOracleに接続したい!ということで、
oo4oを使用して、ExcelからOracleにアクセスしてみようと思います。

まず新しくExcelのファイルを作成して、
ファイル名は何でもいいけど、拡張子を[xlsm]に変更。
これはマクロ有効ファイルの拡張子
f:id:itsoldiersakuri:20150801214830p:plain

Excelファイルを開いて、[開発]-[挿入]から[ActiveXコントロール]の方の
[コマンドボタン]を選択して、2個ほどボタンを作成。
f:id:itsoldiersakuri:20150801214939p:plain

[デザインモード]をクリックしてから、
各ボタンを右クリック→[プロパティ]を選択して、オブジェクト名を設定。
f:id:itsoldiersakuri:20150801215006p:plain

[デザインモード]がONの状態でダブルクリックすると、
VBAプロジェクト(プログラミング画面)に遷移するので、
ここでそれぞれのボタンに何をさせたいのかを記述する。
f:id:itsoldiersakuri:20150801215203p:plain

今回は基本的なことだけ。こんな感じ。

' ************************************************************************************************
' ***
' ***  ExcelからOracleに接続してデータを抽出する
' ***
' ***   概要:OO4Oを使用。Oracleのデータ取得処理。
' ***
' ***
' ***   作成者: SAKURI
' ***   作成日: 2015/08/01
' ***
' ************************************************************************************************
Option Explicit

'「表示クリア」ボタン押下後の処理
Private Sub btnClear_Click()
    '選択した列の内容をクリア
    Range("A5:XFD1048576").ClearContents
    Range("A5:XFD1048576").ClearFormats
    'セルA5の選択(複数セル選択した選択範囲のクリアのため)
    Range("A5:A5").Select
            
End Sub

'「データ取得」ボタン押下後の処理
Private Sub btnData_Click()
On Error GoTo ERR_HANDLER

    Dim OraSession As Object        'セッション
    Dim OraDatabase As Object       'データベース
    Dim rs As Object                'レコードセット
    Dim i As Long
    Dim rownum As Long
    Dim colnum As Integer
    Dim strSQL As String            'SQL文
    Dim maxrownum As Long
    Dim maxcolnum As Long
    
    'すでに表示されている内容のクリア
    Call btnClear_Click
         
    'セッションオブジェクトの生成
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")
    'データベースオブジェクトの生成(接続先を指定)
    Set OraDatabase = OraSession.OpenDatabase("XE", "hr/hr", 0&)
    
    'SQL文
    strSQL = "select * from EMPLOYEES"

    'SQLを実行してデータを取得
    Set rs = OraDatabase.CreateDynaset(strSQL, 0&)
                                                        
    '列名の表示
    rownum = 5
    colnum = 1
    For i = 0 To rs.Fields.Count - 1
        ActiveSheet.Cells(rownum, colnum) = rs(i).Name
        colnum = colnum + 1
    Next
    
    'データの表示(取得したデータを1行ずつExcelに転記)
    rownum = 6
    Do Until rs.EOF
        colnum = 1
        For i = 0 To rs.Fields.Count - 1
            ActiveSheet.Cells(rownum, colnum) = rs(i).Value
            colnum = colnum + 1
        Next
        rs.MoveNext
        rownum = rownum + 1
    Loop
    
    '何行何列目まで表示したか保存
    maxrownum = rownum - 1
    maxcolnum = colnum - 1
        
    '枠線つける
    Range(Cells(5, 1), Cells(maxrownum, maxcolnum)).Borders.LineStyle = xlContinuous
    
    'セル幅自動調整
    Range(Cells(5, 1), Cells(maxrownum, maxcolnum)).Columns.AutoFit
    
    'タイトルに色付け
    Range(Cells(5, 1), Cells(5, maxcolnum)).Interior.Color = RGB(197, 217, 241)
    
    'フィルタ設定
    Range("A5").AutoFilter
                                
    'オブジェクトのクローズ
    rs.Close
    
    '各種オブジェクトの開放
    Set rs = Nothing
    Set OraDatabase = Nothing
    Set OraSession = Nothing
    
    MsgBox ("ヾ(●´∀`●) デキタヨー♪")

QUIT_OPER:
    'オブジェクト変数用に確保したメモリの開放
    Set rs = Nothing
    Set OraDatabase = Nothing
    Set OraSession = Nothing
    Exit Sub
    
ERR_HANDLER:    'エラー処理
    'エラー番号とエラー内容の表示
    MsgBox Err.Number & ")" & Err.Description
    Err.Clear
    GoTo QUIT_OPER

End Sub

コードが書けたら保存して、そしてボタンをクリックする。
ジャジャーーーン!!!
f:id:itsoldiersakuri:20150801215817p:plain

こんなに簡単にOracleにアクセスできちゃうんです!
どんなSQLでも大丈夫なので、例えば売上表とかを見れるようにして
営業さんとかに渡してあげたら、ボタン一つで今日の売り上げが閲覧可能になっちゃう!

もう少し色々できると思うので、応用させてもみたいと思います。

マンチカンというリポジトリに[OracleBase.xlsm]という名前で保存しました!
マンチカンは今後Oracle関係のプログラムを置いていきたいとお思います。github.com