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完成数据处理