ExcelからOracleのデータを操作するサンプル

ダウンロード(ora_xls.lzh: 36,310 バイト) ダウンロード後、解凍して使用してください


起動後の画面イメージ


' ************************************************************************************************
' ***
' ***   Excelを使用したOracleの操作サンプル
' ***
' ***   概要:OO4OおよびADOを使用したOracleのデータ操作(取得、挿入、更新、削除)のサンプルを示す
' ***         使用するテーブルは標準DBに含まれる emp
' ***
' ***
' ***   作成者: H.Kihara (jin@kihara.net)
' ***   作成日: 2002/10/19
' ***
' ************************************************************************************************
Option Explicit

'「表示のクリア」ボタン押下後の処理 Private Sub btnClear_Click() Call ClearDisp End Sub
'表示データのクリア Private Sub ClearDisp() ActiveSheet.Columns("B:I").Select 'アクティブシートの列B〜Iを選択 Selection.ClearContents '選択した列の内容をクリア Range("A2:A2").Select 'セルA2の選択(複数セル選択した選択範囲のクリアのため) End Sub
' ************************************************************************************************ ' ************ 以下OO4Oを利用した場合 ******************************************************** ' ************************************************************************************************ '「データ取得(OO4O)」ボタン押下後の処理 ' oo4o使用時 Private Sub btnGetDataoo4o_Click() 'oo4o用のオブジェクト変数の宣言 Dim OraSession As Object 'セッション Dim OraDatabase As Object 'データベース Dim rs As Object 'データセット(レコードセット) Dim rownum As Long Dim colnum As Integer 'セッションオブジェクトの生成 Set OraSession = CreateObject("OracleInProcServer.XOraSession") 'oo4oデータベースオブジェクトの生成(DBへUserID:scott、パスワード:tigerで接続) Set OraDatabase = OraSession.OpenDatabase(ActiveSheet.Cells(26, 1), "scott/tiger", 0&) 'oo4oデータセット(レコードセット)の生成(全データを返すレコードセットの生成) Set rs = OraDatabase.CreateDynaset("select * from emp", 0&) 'すでに表示されている内容のクリア Call ClearDisp 'フィールド名の表示 For colnum = 0 To rs.Fields.Count - 1 ActiveSheet.Cells(2, colnum + 2) = rs(colnum).Name Next 'データの表示 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 'セル幅の自動調整 ActiveSheet.Columns("B:I").EntireColumn.AutoFit 'オブジェクトのクローズ rs.Close '各種オブジェクトの開放 Set rs = Nothing Set OraDatabase = Nothing Set OraSession = Nothing End Sub
'「データの挿入」ボタン押下後の処理 ' oo4o使用時 Private Sub btnInsertDataoo4o_Click() On Error GoTo ERR_HANDLER 'oo4o用のオブジェクト変数の宣言 Dim OraSession As Object 'セッション Dim OraDatabase As Object 'データベース Dim rs As Object 'データセット(レコードセット) Dim rownum As Long Dim colnum As Integer 'oo4oセッションオブジェクトの生成 Set OraSession = CreateObject("OracleInProcServer.XOraSession") 'oo4oデータベースオブジェクトの生成(DBへUserID:scott、パスワード:tigerで接続) Set OraDatabase = OraSession.OpenDatabase(ActiveSheet.Cells(26, 1), "scott/tiger", 0&) 'oo4oデータセット(レコードセット)の生成(全データを返すレコードセットの生成) Set rs = OraDatabase.CreateDynaset("select * from emp", 0&) 'データの挿入 For rownum = 3 To 20 If ActiveSheet.Cells(rownum, 13) = "" Then Exit For End If rs.AddNew For colnum = 0 To rs.Fields.Count - 1 Select Case rs(colnum).Type Case 10 'varchar rs(colnum).Value = ActiveSheet.Cells(rownum, colnum + 13) Case 8 'date rs(colnum).Value = CDate(ActiveSheet.Cells(rownum, colnum + 13)) Case Else 'その他 rs(colnum).Value = ActiveSheet.Cells(rownum, colnum + 13) End Select Next rs.Update Next 'オブジェクトのクローズ rs.Close 'データ表示の更新 btnGetDataADO_Click 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
'「データの更新」ボタン押下後の処理 ' oo4o使用時 Private Sub btnUpdateDataoo4o_Click() On Error GoTo ERR_HANDLER 'oo4o用のオブジェクト変数の宣言 Dim OraSession As Object 'セッション Dim OraDatabase As Object 'データベース Dim rs As Object 'データセット(レコードセット) Dim rownum As Long Dim colnum As Integer 'oo4oセッションオブジェクトの生成 Set OraSession = CreateObject("OracleInProcServer.XOraSession") 'oo4oデータベースオブジェクトの生成(DBへUserID:scott、パスワード:tigerで接続) Set OraDatabase = OraSession.OpenDatabase(ActiveSheet.Cells(26, 1), "scott/tiger", 0&) 'oo4oデータセット(レコードセット)の生成(全データを返すレコードセットの生成) Set rs = OraDatabase.CreateDynaset("select * from emp", 0&) 'データの更新 For rownum = 3 To 20 If ActiveSheet.Cells(rownum, 13) = "" Then Exit For End If '更新対象データの検索 rs.Findfirst ("EMPNO=" & ActiveSheet.Cells(rownum, 13)) '編集モードに設定 rs.Edit For colnum = 1 To rs.Fields.Count - 1 Select Case rs(colnum).Type Case 10 rs(colnum).Value = ActiveSheet.Cells(rownum, colnum + 13) Case 8 rs(colnum).Value = CDate(ActiveSheet.Cells(rownum, colnum + 13)) Case Else rs(colnum).Value = ActiveSheet.Cells(rownum, colnum + 13) End Select Next rs.Update Next 'オブジェクトのクローズ rs.Close 'データ表示の更新 btnGetDataADO_Click 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
'「データの削除」ボタン押下後の処理 ' oo4o使用時 Private Sub btnDeleteDataoo4o_Click() On Error GoTo ERR_HANDLER 'oo4o用のオブジェクト変数の宣言 Dim OraSession As Object 'セッション Dim OraDatabase As Object 'データベース Dim rs As Object 'データセット(レコードセット) Dim rownum As Long Dim colnum As Integer Dim sSQL As String '削除対象のみを抽出するSQL文の作成 sSQL = "select * from emp where EMPNO=" & ActiveSheet.Cells(3, 13) For rownum = 4 To 20 If ActiveSheet.Cells(rownum, 13) = "" Then Exit For End If sSQL = sSQL & " OR EMPNO=" & ActiveSheet.Cells(rownum, 13) Next 'oo4oセッションオブジェクトの生成 Set OraSession = CreateObject("OracleInProcServer.XOraSession") 'oo4oデータベースオブジェクトの生成(DBへUserID:scott、パスワード:tigerで接続) Set OraDatabase = OraSession.OpenDatabase(ActiveSheet.Cells(26, 1), "scott/tiger", 0&) 'oo4oデータセット(レコードセット)の生成(削除対象データのみを返すレコードセットの生成) Set rs = OraDatabase.CreateDynaset(sSQL, 0&) 'レコードセット内の全レコードの削除 Do Until rs.EOF rs.Delete rs.MoveNext Loop 'オブジェクトのクローズ rs.Close 'データ表示の更新 btnGetDataoo4o_Click 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 ' ************************************************************************************************ ' ************ 以上OO4Oを利用した場合 ******************************************************** ' ************************************************************************************************
' ************************************************************************************************ ' ************ 以下ADOを利用した場合 ********************************************************** ' ************************************************************************************************ '「データ取得(ADO)」ボタン押下後の処理 ' ADO使用時
Private Sub btnGetDataADO_Click() 'ADO用のオブジェクト変数の宣言 Dim cn As New ADODB.Connection 'コネクション Dim rs As New ADODB.Recordset 'レコードセット Dim rownum As Long Dim colnum As Integer 'ADOコネクションの生成 cn.Open "Provider=OraOLEDB.Oracle;Data Source=" & ActiveSheet.Cells(26, 1) & ";User ID=scott;Password=tiger;", "scott", "tiger" 'ADOレコードセットの生成(全データを返すレコードセットの生成) rs.Open "select * from emp", cn, adOpenDynamic, adLockOptimistic, adCmdText 'すでに表示されている内容のクリア Call ClearDisp 'フィールド名の表示 For colnum = 0 To rs.Fields.Count - 1 ActiveSheet.Cells(2, colnum + 2) = rs(colnum).Name Next 'データの表示 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 ActiveSheet.Columns("B:I").EntireColumn.AutoFit 'オブジェクトのクローズ rs.Close cn.Close 'オブジェクト変数用に確保したメモリの開放 Set rs = Nothing Set cn = Nothing End Sub
'「データの挿入」ボタン押下後の処理 ' ADO使用時 Private Sub btnInsertDataADO_Click() On Error GoTo ERR_HANDLER 'ADO用のオブジェクト変数の宣言 Dim cn As New ADODB.Connection 'コネクション Dim rs As New ADODB.Recordset 'レコードセット Dim rownum As Long Dim colnum As Integer 'ADOコネクションの生成 cn.Open "Provider=OraOLEDB.Oracle;Data Source=" & ActiveSheet.Cells(26, 1) & ";User ID=scott;Password=tiger;", "scott", "tiger" 'ADOレコードセットの生成(全データを返すレコードセットの生成) rs.Open "select * from emp", cn, adOpenDynamic, adLockOptimistic, adCmdText 'データの挿入 For rownum = 3 To 20 If ActiveSheet.Cells(rownum, 13) = "" Then Exit For End If rs.AddNew For colnum = 0 To rs.Fields.Count - 1 Select Case rs(colnum).Type Case 10 'varchar rs(colnum).Value = ActiveSheet.Cells(rownum, colnum + 13) Case 8 'date rs(colnum).Value = CDate(ActiveSheet.Cells(rownum, colnum + 13)) Case Else 'その他 rs(colnum).Value = ActiveSheet.Cells(rownum, colnum + 13) End Select Next rs.Update Next 'オブジェクトのクローズ rs.Close cn.Close 'データ表示の更新 btnGetDataADO_Click QUIT_OPER: 'オブジェクト変数用に確保したメモリの開放 Set rs = Nothing Set cn = Nothing Exit Sub ERR_HANDLER: 'エラー処理 'エラー番号とエラー内容の表示 MsgBox Err.Number & ")" & Err.Description Err.Clear GoTo QUIT_OPER End Sub
'「データの更新」ボタン押下後の処理 ' ADO使用時 Private Sub btnUpdateDataADO_Click() On Error GoTo ERR_HANDLER 'ADO用のオブジェクト変数の宣言 Dim cn As New ADODB.Connection 'コネクション Dim rs As New ADODB.Recordset 'レコードセット Dim rownum As Long Dim colnum As Integer 'ADOコネクションの生成 cn.Open "Provider=OraOLEDB.Oracle;Data Source=" & ActiveSheet.Cells(26, 1) & ";User ID=scott;Password=tiger;", "scott", "tiger" 'ADOレコードセットの生成(全データを返すレコードセットの生成) rs.Open "select * from emp", cn, adOpenDynamic, adLockOptimistic, adCmdText 'データの更新 For rownum = 3 To 20 If ActiveSheet.Cells(rownum, 13) = "" Then Exit For End If '更新対象データの検索 rs.Find ("EMPNO=" & ActiveSheet.Cells(rownum, 13)) For colnum = 1 To rs.Fields.Count - 1 Select Case rs(colnum).Type Case 10 'varchar rs(colnum).Value = ActiveSheet.Cells(rownum, colnum + 13) Case 8 'date rs(colnum).Value = CDate(ActiveSheet.Cells(rownum, colnum + 13)) Case Else 'その他 rs(colnum).Value = ActiveSheet.Cells(rownum, colnum + 13) End Select Next rs.Update rs.MoveFirst Next 'オブジェクトのクローズ rs.Close cn.Close 'データ表示の更新 btnGetDataADO_Click QUIT_OPER: 'オブジェクト変数用に確保したメモリの開放 Set rs = Nothing Set cn = Nothing Exit Sub ERR_HANDLER: 'エラー処理 'エラー番号とエラー内容の表示 MsgBox Err.Number & ")" & Err.Description Err.Clear GoTo QUIT_OPER End Sub
'「データの削除」ボタン押下後の処理 ' ADO使用時 Private Sub btnDeleteDataADO_Click() On Error GoTo ERR_HANDLER 'ADO用のオブジェクト変数の宣言 Dim cn As New ADODB.Connection 'コネクション Dim rs As New ADODB.Recordset 'レコードセット Dim rownum As Long Dim colnum As Integer Dim sSQL As String '削除対象のみを抽出するSQL文の作成 sSQL = "select * from emp where EMPNO=" & ActiveSheet.Cells(3, 13) For rownum = 4 To 20 If ActiveSheet.Cells(rownum, 13) = "" Then Exit For End If sSQL = sSQL & " OR EMPNO=" & ActiveSheet.Cells(rownum, 13) Next 'ADOコネクションの生成 cn.Open "Provider=OraOLEDB.Oracle;Data Source=" & ActiveSheet.Cells(26, 1) & ";User ID=scott;Password=tiger;", "scott", "tiger" 'ADOレコードセットの生成(削除対象データのみを返すレコードセットの生成) rs.Open sSQL, cn, adOpenDynamic, adLockOptimistic, adCmdText 'レコードセット内の全レコードの削除 Do Until rs.EOF rs.Delete rs.MoveNext Loop 'オブジェクトのクローズ rs.Close cn.Close 'データ表示の更新 btnGetDataADO_Click QUIT_OPER: 'オブジェクト変数用に確保したメモリの開放 Set rs = Nothing Set cn = Nothing Exit Sub ERR_HANDLER: 'エラー処理 'エラー番号とエラー内容の表示 MsgBox Err.Number & ")" & Err.Description Err.Clear GoTo QUIT_OPER End Sub ' ************************************************************************************************ ' ************ 以上ADOを利用した場合 ********************************************************** ' ************************************************************************************************