vba連接oracle方法

方法如下步驟操作：

1、打開VBA編輯器，在菜單中點選“工具”，“引用”

2、確保“Microsoft ActiviteX DAta Objects 2.8 Library”和“Microsoft ActiviteX Data ObjectS Recordset 2.8 Library”被勾選上。

建立連接過程，代碼如下：

複製代碼代碼如下:

Public Sub ConOra()

On Error GoTo ErrMsg:

Dim ConnDB As ection

Set ConnDB = New ection

Dim ConnStr As String

Dim DBRst As rdset

Set DBRst = New rdset

Dim SQLRst As String

Dim OraOpen As Boolean

OraOpen = False

OraID="Orcl" 'Oracle數據庫的相關配置

OraUsr="user"

OraPwd="password"

ConnStr = "Provider = MSDAORA.1Password=" & OraPwd & _

"User ID=" & OraUsr & _

"Data Source=" & OraID & _

"Persist Security Info=True"

orLocation = adUseServer

ConnStr

OraOpen = True '成功執行後，數據庫即被打開

'MsgBox "Connect to the oracle database Successful!"， vbInformation， "Connect Successful"

veConnection = ConnDB

orLocation = adUseServer

Type = adLockBatchOptimistic

SQLRst = "Select * From TstTab"

SQLRst， ConnDB， adOpenStatic， adLockBatchOptimistic

First

Exit Function

ErrMsg:

OraOpen = False

MsgBox "Connect to the oracle database fail ，please check!"， vbCritical， "Connect fail!"

End Function

Public Sub ConOra()

On Error GoTo ErrMsg:

Dim ConnDB As ection

Set ConnDB = New ection

Dim ConnStr As String

Dim DBRst As rdset

Set DBRst = New rdset

Dim SQLRst As String

Dim OraOpen As Boolean

OraOpen = False

OraID="Orcl" 'Oracle數據庫的相關配置

OraUsr="user"

OraPwd="password"

ConnStr = "Provider = MSDAORA.1Password=" & OraPwd & _

"User ID=" & OraUsr & _

"Data Source=" & OraID & _

"Persist Security Info=True"

orLocation = adUseServer

ConnStr

OraOpen = True '成功執行後，數據庫即被打開

'MsgBox "Connect to the oracle database Successful!"， vbInformation， "Connect Successful"

veConnection = ConnDB

orLocation = adUseServer

Type = adLockBatchOptimistic

SQLRst = "Select * From TstTab"

SQLRst， ConnDB， adOpenStatic， adLockBatchOptimistic

First

Exit Function

ErrMsg:

OraOpen = False

MsgBox "Connect to the oracle database fail ，please check!"， vbCritical， "Connect fail!"

End Function

可以根據需要調整SQL語句，獲取相關數據，並輸出到Excel完成數據處理