Excel VBA连接并操作Oracle
程序员文章站
2023-11-17 10:51:04
以下是通过excel 的vba连接oracle并操作oracle相关数据的示例excel 通过vba连接数据库需要安装相应的oracle客户端工具并引用ado的相关组件，引...
以下是通过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下测试通过.
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下测试通过.