Oracle活用 Excelからデータを取得する①
今日はめっちゃ暑い中、東京ビッグサイトの「Maker Faire Tokyo」に行ってきました!
色々面白い発明(?)があったけど、私の中での一番はコレ!
きのこたけのこ判別機!!!
アイデア的に天才だった。可愛い。
あと面白かったのはメントスコーラショーもなかなかすごかった!
でも炎天下の中説明が長くてちょっときつかった…。
さて、今日はせっかくOracleClientをインストールしたので、
何かを介してOracleに接続したい!ということで、
oo4oを使用して、ExcelからOracleにアクセスしてみようと思います。
まず新しくExcelのファイルを作成して、
ファイル名は何でもいいけど、拡張子を[xlsm]に変更。
これはマクロ有効ファイルの拡張子。
Excelファイルを開いて、[開発]-[挿入]から[ActiveXコントロール]の方の
[コマンドボタン]を選択して、2個ほどボタンを作成。
[デザインモード]をクリックしてから、
各ボタンを右クリック→[プロパティ]を選択して、オブジェクト名を設定。
[デザインモード]がONの状態でダブルクリックすると、
VBAプロジェクト(プログラミング画面)に遷移するので、
ここでそれぞれのボタンに何をさせたいのかを記述する。
今回は基本的なことだけ。こんな感じ。
' ************************************************************************************************ ' *** ' *** 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
コードが書けたら保存して、そしてボタンをクリックする。
ジャジャーーーン!!!
こんなに簡単にOracleにアクセスできちゃうんです!
どんなSQLでも大丈夫なので、例えば売上表とかを見れるようにして
営業さんとかに渡してあげたら、ボタン一つで今日の売り上げが閲覧可能になっちゃう!
もう少し色々できると思うので、応用させてもみたいと思います。
マンチカンというリポジトリに[OracleBase.xlsm]という名前で保存しました!
マンチカンは今後Oracle関係のプログラムを置いていきたいとお思います。github.com