在Excel中利用ADO連接Oracle資料庫

Dim myCon As ADODB.Connection
Dim myRS As ADODB.Recordset

Dim conSTR As String            '連線字串
Dim GetDataSQL As String    'SQL Statement
Dim myOracleServer As String '資料庫
Dim myUser As String                 '使用者
Dim myPassword As String        '密碼

myOracleServer = "orcl"
myUser = "scott"
myPassword = "password"




'設定連線字串
conSTR = "Provider=MSDAORA.1" & _                             '資料庫類型
                  ";Data Source=" & myOracleServer & _            '資料庫名稱
                  ";User ID=" & myUser & _                                    '使用者
                  ";Password=" & myPassword                             '密碼

Set myCon = New ADODB.Connection         '建立ADODB.Connection物件
Set myRS = New ADODB.Recordset            '建立ADODB.Recordset物件

myCon.Open conSTR      '開啟連線


With myRS
    .ActiveConnection = myCon
    .Source = GetDataSQL
    .Open
End With

Range("B2").CopyFromRecordset myRS

Set myRS = Nothing
myCon.Close
Set myCon = Nothing