ExcelからOracleに接続し、データ取得する【Oracle＆Excel】
<<   作成日時 ： 2010/02/22 11:42   >>
ブログ気持玉 0 / トラックバック 0 / コメント 3
1.デスクトップの何もないところで右クリックし、新規作成→テキスト文書　を選択する

2.拡張子をudlに変更する

3.2をWクリックする
4.[プロバイダ]タブをクリックし、Oracle Provider for OLE DB　を選択し、[次へ]をクリックします。

5.データソースとユーザ名、パスワードを入力し、[パスワードを保存する]にチェックを入れて接続テストでOKならば、[OK]ボタンをクリックします。

6.2のファイルの拡張子を再びtxtにしてWクリックします。

※1-6の作業を簡単に行う方法はhttp://www.sunvisor.net/db/datalinks_dialogに掲載されていますが
ここでは基本を理解するためにあえて手間はかかりますがわかりやすい手順を記載しました。

7.Excelファイルを開き、[ツール]→[マクロ]→[Visual basic Editor]を開き、標準モジュールを新規作成し、参照設定ではMicrosoft ActiveX Data Object 2.5 Libraryにチェックを入れます。（ライブラリのバージョンはPCによって違います。）

8. 以下の文を貼り付け、sConString=”　”　の“”の間に6のProvider～最後までコピー&ペーストします。
あとは適宜test1-3のようなセット方法を用いたり、その他新たにデータのセット方法を書いてもOKです。

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset

Public Sub ConnectToDB()
Dim sConString As String
Dim sUserID As String
Dim sPassword As String

Set cnn = New ADODB.Connection
sConString = "Provider=OraOLEDB.Oracle.1;Password=c***;Persist Security Info=True;User ID=***f;Data Source=c**s"
cnn.Open sConString
End Sub

Public Sub DisconnectFromDB()
cnn.Close
Set cnn = Nothing
End Sub

Public Sub OpenTable(ASQL As String)
Set rs = New ADODB.Recordset

rs.Open ASQL, cnn, adOpenKeyset, adLockReadOnly
End Sub

Public Sub CloseTable()
rs.Close
Set rs = Nothing
End Sub

'テーブルのデータをすべてセットするパターン
Public Sub test()
ConnectToDB
OpenTable "SELECT * FROM SHOZOKU_MST"
Sheet1.Range("A1").CopyFromRecordset rs
CloseTable
DisconnectFromDB
End Sub

'セットしたいマスターを呼び出して、エクセル上でVLOOKUP等にて参照させるパターン
Public Sub test2()
Dim rsShokuin As ADODB.Recordset
Dim rsShozoku As ADODB.Recordset

ConnectToDB
Set rsShokuin = New ADODB.Recordset
Set rsShozoku = New ADODB.Recordset
rsShokuin.Open "Shokuin_MST", cnn, adOpenStatic, adLockReadOnly
rsShozoku.Open "SHOZOKU_MST", cnn, adOpenStatic, adLockReadOnly
ShowData Sheet1.Range("A1"), rsShokuin
ShowData Sheet2.Range("A1"), rsShozoku
rsShokuin.Close
rsShozoku.Close
Set rsShokuin = Nothing
Set rsShozoku = Nothing
DisconnectFromDB
End Sub

'セットしたいデータを一行ずつなめてセットするパターン
Public Sub test3()
Dim rsShokuin As ADODB.Recordset
Dim rsShozoku As ADODB.Recordset
Dim lOffset As Long

ConnectToDB
Set rsShokuin = New ADODB.Recordset
Set rsShozoku = New ADODB.Recordset
rsShokuin.Open "Shokuin_MST", cnn, adOpenStatic, adLockReadOnly
rsShozoku.Open "SHOZOKU_MST", cnn, adOpenStatic, adLockReadOnly
lOffset = 0
Do While Not rsShokuin.EOF
rsShozoku.MoveFirst
rsShozoku.Find "SHOZOKUCD='" & rsShokuin("K_SHOZOKUCD") & "'"
With Sheet4.Range("A1")
.Offset(lOffset, 0) = rsShokuin("ShokuinCD")
.Offset(lOffset, 1) = rsShokuin("ShokuinM")
.Offset(lOffset, 2) = rsShokuin("K_SHOZOKUCD")
.Offset(lOffset, 3) = rsShozoku("NAME")
End With
rsKyoin.MoveNext
lOffset = lOffset + 1
Loop
rsShokuin.Close
rsShozoku.Close
Set rsShokuin = Nothing
Set rsShozoku = Nothing
DisconnectFromDB
End Sub

Private Sub ShowData(ARange As Range, Ars As ADODB.Recordset)
ARange.CopyFromRecordset Ars
End Sub

これで接続したいテーブルなどを適宜修正して終了です。