Ref:http://blog.csdn.net/iamlaosong/article/details/8465177
Excel通过ADO方式连接到Oracle并操作Oracle给那些编程能力不强的人带来很大的福音，结合着Excel的数据处理与图表制作，就能很轻松地处理一些常规工作。
日常工作中需要查询各种数据，而且不断变化，处理这些数据的人不是技术人员，不会连接数据库自己查询，通过下面的办法就可以让技术人员编辑好包含查询语句的excel文件，让管理人员自己输入条件取数了。
我的方法是编辑需要的SQL语句保存在单元格中，并在查询条件需要参数值的地方用问号“？”代替，再在其他单元格中保存查询条件所需的参数值，在“宏”中用参数值替换掉SQL语句中的问号，最后执行查询语句并将结果保存到excel表中。
以下是通过Excel的VBA连接Oracle并读取Oracle相关数据的步骤：
1、引用ADO相关组件：
打开VBA编辑器，在菜单中点选“工具”--》“引用”。确保“Microsoft ActiviteX Data Objects 2.8 Library”和“Microsoft ActiviteX Data ObjectS Recordset 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)
)
)
通过工作表保护使使用者只能修改参数值和状态，其他不能修改，防止破坏相关设置。
2）可以用循环实现参数的多次使用，这在SQL包含多个子查询，参数是日期的情况下很有用，每个子查询多会用到参数中的起止日期。循环中如果某个子查询只用到部分参数，可以用注释语句中加“？”的方式站位。例如：
- select b.city,
- b.ssxs,
- a.clct_bureau_org_code,
- b.zj_mc,
- a.sender_cust_code,
- a.sender_dept_name,
- min(a.clct_date),
- max(a.clct_date),
- count(*) yjzl,
- sum(a.actual_total_fee) yjsr
- from tb_evt_mail_clct a,
- (select * from sncn_zd_jg where jgfl = ‘sd‘) b,
- (select distinct t.sender_cust_code
- from tb_evt_mail_clct t
- where t.clct_date < to_date(‘?‘, ‘yyyy-mm-dd‘)) c
- -- ? 占位
- where a.clct_bureau_org_code = b.zj_code
- and a.clct_date between to_date(‘?‘, ‘yyyy-mm-dd‘) and
- to_date(‘?‘, ‘yyyy-mm-dd‘)
- and length(a.sender_cust_code) = 14
- and a.sender_cust_code = c.sender_cust_code(+)
- and c.sender_cust_code is null
- group by b.city,
- b.ssxs,
- a.clct_bureau_org_code,
- b.zj_mc,
- a.sender_cust_code,
- a.sender_dept_name
- order by b.city,
- b.ssxs,
- a.clct_bureau_org_code,
- b.zj_mc,
- a.sender_cust_code,
- a.sender_dept_name
附：简易客户端（版本9i）资源下载地址：http://download.csdn.net/detail/iamlaosong/5035733
完整的工具包（含Oracle简易客户端）资源下载地址：http://download.csdn.NET/detail/iamlaosong/5307186
利用ADO让普通人用excel读取oracle数据库表的通用办法
原文：http://www.cnblogs.com/lonelydreamer/p/6200456.html
