VBA Oracle接続

Sub ExecuteOracleQuery()
Dim conn As Object ' Connectionオブジェクト
Dim rs As Object ' Recordsetオブジェクト
Dim strConn As String ' 接続文字列
Dim strSQL As String ' SQLクエリ

' 接続文字列を設定(tnsnames.ora使用)
'strConn = "Provider=OraOLEDB.Oracle;Data Source=XE;User ID=SCOTT;Password=tiger;"

'接続文字列を設定(接続情報を直接記載)
strConn = "Provider=OraOLEDB.Oracle;Data Source=" & _
"(DESCRIPTION=(ADDRESS=(PROTOCOL = TCP)(HOST = mypcwin)(PORT = 1521))" & _
"(CONNECT_DATA =(SERVICE_NAME = XEPDB1)));User ID=SCOTT;Password=tiger"

'strConn = "Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=サーバ名)(PORT=ポート番号))(CONNECT_DATA=(SERVICE_NAME=サービス名)));User ID=SCOTT;Password=tiger"

' SQLクエリを設定
strSQL = "SELECT * FROM DEPT"

' Connectionオブジェクトを作成
Set conn = CreateObject("ADODB.Connection")

' Recordsetオブジェクトを作成
Set rs = CreateObject("ADODB.Recordset")

' Oracleに接続
conn.Open strConn

' SQLクエリを実行
rs.Open strSQL, conn

' Aシートに結果を表示
With Worksheets("フォーム")
.Cells.ClearContents

'列名の表示
For i = 0 To rs.Fields.Count - 1
.Cells(1, i + 1) = rs(i).Name
Next i

'値の表示
Row = 1
Do Until rs.EOF
For i = 0 To rs.Fields.Count - 1 'レコード数のカウント
.Cells(Row + 1, i + 1) = rs(i).Value
Next i
rs.MoveNext
Row = Row + 1
Loop

End With

' メモリを解放
rs.Close
conn.Close

' オブジェクトを破棄
Set rs = Nothing
Set conn = Nothing
End Sub