excel的ADO读取ORACLE,【VBA研究】利用ADO让普通人用excel读取oracle数据库表的通用办...

先交代下写此文的背景 其一，上次举办HUSA技术交流会时，有高人在台下踩场说我对开源的认识太肤浅了。被鄙视过后，一直想找机会好好学学。 其二，最近为了“蓝云

作者：iamlaosong

日常工作中需要查询各种数据，，而且不断变化，处理这些数据的人不是技术人员，不会连接数据库自己查询，通过下面的办法就可以让技术人员编辑好包含查询语句的excel文件，让管理人员自己输入条件取数了。

我的方法是编辑需要的SQL语句保存在单元格中，并在查询条件需要参数值的地方用问号“？”代替，再在其他单元格中保存查询条件所需的参数值，在“宏”中用参数值替换掉SQL语句中的问号，最后执行查询语句并将结果保存到excel表中。

相关数据的步骤：

引用

Microsoft

ActiviteX Data Objects 2.8 Library

2、建立读取数据的过程：

Public Sub get_data()

'根据工作表中的查询语句读取数据

On Error GoTo ErrMsg:

Dim cnn As Object, rst As Object

Dim name, stat, sqls, field As String

Dim pn(4), pm(4) As String

Dim i, j, kk, pmkk, lineno As Integer

Dim OraOpen As Boolean

Set cnn = CreateObject("ADODB.Connection")

Set rst = CreateObject("ADODB.Recordset")

sqls = "connect database"

cnn.Open "Provider=msdaora;Data Source=dl580;User Id=sxjkuser;Password=sxjkpasswd;"

OraOpen = True '成功执行后，数据库即被打开

If OraOpen Then lineno = [D65536].End(xlUp).Row Else lineno = 0

'行数

Application.Calculation = xlManual

For i = 3 To lineno

stat = Trim(Cells(i, 3))

If stat = "Y" Or stat = "y" Then

name = Cells(i, 2)

field = Cells(i, 4)

pn(1) = Cells(i, 5)

pm(1) = Cells(i, 6)

pn(2) = Cells(i, 7)

pm(2) = Cells(i, 8)

pn(3) = Cells(i, 9)

pm(3) = Cells(i, 10)

pn(4) = Cells(i, 11)

pm(4) = Cells(i, 12)

pmkk = Cells(i, 13)

sqls = Cells(i, 15)

'MsgBox sqls

For kk = 1 To pmkk '用于参数多次使用，如联合SQL语句中每个子句都需要日期参数

For j = 1 To 4

If pn(j) <> "" Then

sqls = Replace(sqls, "?", pm(j), 1, 1)

'MsgBox sqls

End If

Next j

Next kk

MsgBox sqls

Set rst = cnn.Execute(sqls)

sqls = "clear sheets"

maxrow = Sheets(name).UsedRange.Rows.Count

Sheets(name).Range("a2:" & field & maxrow).ClearContents

sqls = "CopyFromRecordset"

Sheets(name).Range("a2").CopyFromRecordset rst

Cells(i, 3) = "成功"

'MsgBox i

End If

Next i

'rst.Close

'Set rst = Nothing

cnn.Close

Set cnn = Nothing

Application.Calculation = xlAutomatic

'Sheets("分析").PivotTables("数据透视表1").PivotCache.Refresh

Worksheets("系统参数").Select

msg = MsgBox("数据读取完毕!", vbOKOnly, "iamlaosong")

Exit Sub

ErrMsg:

OraOpen = False

MsgBox sqls, vbCritical, "操作失败 ，请检查！"

End Sub

3、SQL语句实例

这是一个简单的语句：

SELECT * FROM zdgc_sn_sj_gfl t

WHERE t.CLCT_DATE = to_date('?', 'yyyy-mm-dd')

AND t.JSBZ = '1'

ORDER BY t.CITY, t.SSXS

这是一个复杂的语句：

select aa.zj_code,

aa.zj_mc,

aa.clct_date,

aa.sjzl,

aa.jyqsjzl,

nvl(bb.wgfsl, 0),

nvl(bb.jyqwgfsl, 0)

from (select b.ssxs,

b.zj_code,

b.zj_mc,

a.clct_date,

count(*) sjzl,

sum(case

when to_char(a.clct_time, 'hh24mi') <= '?' then

1

else

0

end) jyqsjzl

from tb_evt_mail_clct a, sncn_zd_jg b

where a.clct_bureau_org_code = b.zj_code

and a.time_limit_code <> '6'

and a.mail_kind_code <> '10401'

and a.addi_service_code <> '1'

and (a.rcv_area like '23%' or a.rcv_area like '24%')

group by b.ssxs, b.zj_code, b.zj_mc, a.clct_date) aa

left join (select b.ssxs,

b.zj_code,

b.zj_mc,

a.clct_date,

count(*) wgfsl,

sum(decode(jybz, 'b', 1, 0)) jyqwgfsl

from sncn_zd_jg b, zdgc_sn_sj_errfc a

where a.zj_code = b.zj_code

and a.jsbz = '1'

and a.jybz = 'b'

group by b.ssxs, b.zj_code, b.zj_mc, a.clct_date) bb on aa.ssxs =

bb.ssxs

and aa.zj_code =

bb.zj_code

and aa.clct_date =

bb.clct_date

where aa.clct_date = to_date('?', 'yyyy-mm-dd')

and aa.ssxs = '?'

order by aa.zj_code, aa.zj_mc

4、操作界面

这是一个：

这是另一个：

5、说明

1)使用者需要安装Oracle客户端并进行本地服务名配置(运行客户端程序Net Configuration Assistant配置，本例配置的服务名是DL580)，实际就是配置tnsnames.ora文件。也可以安装简易oracle客户端，并用记事本修改tnsnames.ora文件，本例就是需要在该文件中增加如下内容(本例Oracle数据库服务器地址是10.178.10.197，SID是ORCL)：

DL580 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.178.10.197)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = orcl)

)

)

通过工作表保护使使用者只能修改参数值和状态，其他不能修改，防止破坏相关设置。