【VBA研究】利用ADO让普通人用excel读取oracle数据库表的通用办法
来源:互联网 发布:淘宝crm系统架构 编辑:程序博客网 时间:2024/06/05 18:36
作者:iamlaosong
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 SubErrMsg: 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
- 【VBA研究】利用ADO让普通人用excel读取oracle数据库表的通用办法
- 【VBA研究】用VBA实现excel与Oracle数据库交互
- 【VBA研究】Excel VBA利用ADODB访问数据库使用小结
- 【VBA研究】Excel VBA利用ADODB访问数据库使用小结
- 利用ADO读取Excel表
- 【VBA研究】利用ADO实现VBA连接Oracle并执行存储过程
- 【VBA研究】用SQL语句读取EXCEL数据例程
- Excel VBA 连接Oracle数据库
- 【VBA研究】如何将Excel工作表的内容更新到数据库
- 利用ADO打造Excel数据库
- VBA 通用的导入数据库
- VBA陈旧的代码:Access 2 Excel (VBA+ADO)
- C#利用ADO.NET技术读取Excel
- 利用ADO.NET的体系架构打造通用的数据库访问通用类
- 利用ADO.NET的体系架构打造通用的数据库访问通用类 .
- 利用ADO.NET的体系架构打造通用的数据库访问通用类
- 利用ADO.NET的体系架构打造通用的数据库访问通用类
- 利用ADO.NET的体系架构打造通用的数据库访问通用类
- 如何查看sql server数据库连接数
- c++ 字符串反转方法 UNICODE 和 ANSI 版本
- 惯性导航系统
- android in practice_Making sure files are saved with sync
- 二叉树的遍历与建立
- 【VBA研究】利用ADO让普通人用excel读取oracle数据库表的通用办法
- 设计模式之访问者模式(visitor)
- 你比六年级的小学生聪明吗?—— Java数组的初始化
- Javascript笔记
- sql 上一条、下一条记录再次改进(文章底部红色字体)及如何在子查询中使用limit语法!我这个脑子啊
- 献给正在郁闷的人
- 距离保护中的几个重要概念
- mac上修改文件的显示与隐藏
- S5PV210 GPIO 操作