当前位置： 移动技术网 > IT编程>数据库>Oracle > Excel VBA连接并操作Oracle

Excel VBA连接并操作Oracle

2017年12月12日  | 移动技术网IT编程  | 我要评论
以下是通过excel 的vba连接oracle并操作oracle相关数据的示例
excel 通过vba连接数据库需要安装相应的oracle客户端工具并引用ado的相关组件，引用ado相关组件可按如下步骤操作：
　　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 adodb.connection
set conndb = new adodb.connection
dim connstr as string
dim dbrst as adodb.recordset
set dbrst = new adodb.recordset
dim sqlrst as string
dim oraopen as boolean
oraopen = false
oraid="orcl" 'oracle数据库的相关配置
orausr="user"
orapwd="password"
connstr = "provider = msdaora.1;password=" & orapwd & _
";user id=" & orausr & _
";data source=" & oraid & _
";persist security info=true"
conndb.cursorlocation = aduseserver
conndb.open connstr
oraopen = true '成功执行后，数据库即被打开
'msgbox "connect to the oracle database successful!", vbinformation, "connect successful"
dbrst.activeconnection = conndb
dbrst.cursorlocation = aduseserver
dbrst.locktype = adlockbatchoptimistic
sqlrst = "select * from tsttab"
dbrst.open sqlrst, conndb, adopenstatic, adlockbatchoptimistic
dbrst.movefirst
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 adodb.connection
set conndb = new adodb.connection
dim connstr as string
dim dbrst as adodb.recordset
set dbrst = new adodb.recordset
dim sqlrst as string
dim oraopen as boolean
oraopen = false
oraid="orcl" 'oracle数据库的相关配置
orausr="user"
orapwd="password"
connstr = "provider = msdaora.1;password=" & orapwd & _
";user id=" & orausr & _
";data source=" & oraid & _
";persist security info=true"
conndb.cursorlocation = aduseserver
conndb.open connstr
oraopen = true '成功执行后，数据库即被打开
'msgbox "connect to the oracle database successful!", vbinformation, "connect successful"
dbrst.activeconnection = conndb
dbrst.cursorlocation = aduseserver
dbrst.locktype = adlockbatchoptimistic
sqlrst = "select * from tsttab"
dbrst.open sqlrst, conndb, adopenstatic, adlockbatchoptimistic
dbrst.movefirst
exit function
errmsg:
oraopen = false
msgbox "connect to the oracle database fail ,please check!", vbcritical, "connect fail!"
end function

可以根据需要调整sql语句，获取相关数据，并输出到excel完成数据处理
上述代码在windows xp sp3/2003 sp2 + office2003下测试通过.

您可能感兴趣的文章:

如对本文有疑问， 点击进行留言回复！！

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网