学以致用——Excel连接Oracle生成iKB报告——Part3(功能优化)
来源:互联网 发布:Tensorflow doc 编辑:程序博客网 时间:2024/05/20 08:26
作为一个consultant,其价值就在于做一个problem solver,解决具体问题。
自己写程序的好处就是,对细节的把控程度更高,更容易实现精益求精。
今天,又熬夜优化了一下代码,现在,可以做到从Excel一键提取Oracle数据库iKB汇总数据,进行数据分析及完成自动化日报了。
可以说,自动化程度已经相当之高了
分享代码:
Sub initialize()'声明定义VBA语句中需要使用到的各个变量类型Dim AdoConn As New ADODB.Connection '定义变量AdoConn为连接数据库对象(ADODB是数据库访问组件,Connection是其中的一个对象),用于实现连接数据库连接等操作Dim D1 As Date '定义D1为日期型变量,用于业务日期赋值Dim D2 As Date '定义D2为日期型变量,用于业务日期赋值Dim i As Integer '定义i为循环变量,用于初始化时基于数据库数据循环赋值'定义SQL语句所需要的4个字符串变量Dim strSQL1 As StringDim strSQL2 As StringDim strSQL3 As StringDim strSQL4 As Stringi = 2 '表头占用一行,数据从第二行开始'需在工程中手动添加引用:工具-引用-Microsoft ActiveX Data Objects 2.8 'Library,Microsoft ActiveX Data Objects Recordset 2.8 Library'建立数据库连接'AdoConn.Open ("provider=msdaora; data source=orcl; user id=C##SCOTT;password=scott") 'Oracle 12.1.0.2.0-64 版本+Excel2016专业增强版,使用此种连接方式不可行AdoConn.Open ("provider=OraOLEDB.Oracle.1; data source=orcl; user id=C##SCOTT;password=scott")'从第二行开始循环赋值Do While ActiveSheet.Cells(i, "B").Value <> "" D1 = ActiveSheet.Cells(i, "B") D2 = D1 + 1 '设置SQL查询语句 strSQL1 = "SELECT count(termid) FROM ikb" '当日词条总数 strSQL2 = "SELECT count(distinct phase) FROM ikb" 'Phase词条数(去重不为空,下同) strSQL3 = "SELECT count(distinct type) FROM ikb" 'type词条数 strSQL4 = "SELECT count(distinct subtype) FROM ikb" 'subtype词条数 strSQL5 = "SELECT count(termid) FROM ikb WHERE date_updated < to_date('" & D2 & "') AND date_updated >= to_date('" & D1 & "')" '更新日期为该日期的词条总数 strSQL6 = "SELECT count(distinct en) FROM ikb" 'EN词条数 strSQL7 = "SELECT count(distinct cn) FROM ikb" 'CN词条数 strSQL8 = "SELECT count(distinct jp) FROM ikb" 'JP词条数 strSQL9 = "SELECT count(distinct ed) FROM ikb" 'ed词条数 strSQL10 = "SELECT count(distinct cnd) FROM ikb" 'cnd词条数 strSQL11 = "SELECT count(distinct jpd) FROM ikb" 'jpd词条数 '执行查询并赋值 ActiveSheet.Cells(i, 12).CopyFromRecordset AdoConn.Execute(strSQL1) ActiveSheet.Cells(i, 3).CopyFromRecordset AdoConn.Execute(strSQL2) ActiveSheet.Cells(i, 4).CopyFromRecordset AdoConn.Execute(strSQL3) ActiveSheet.Cells(i, 5).CopyFromRecordset AdoConn.Execute(strSQL4) ActiveSheet.Cells(i, 13).CopyFromRecordset AdoConn.Execute(strSQL5) ActiveSheet.Cells(i, 6).CopyFromRecordset AdoConn.Execute(strSQL6) ActiveSheet.Cells(i, 7).CopyFromRecordset AdoConn.Execute(strSQL7) ActiveSheet.Cells(i, 8).CopyFromRecordset AdoConn.Execute(strSQL8) ActiveSheet.Cells(i, 9).CopyFromRecordset AdoConn.Execute(strSQL9) ActiveSheet.Cells(i, 10).CopyFromRecordset AdoConn.Execute(strSQL10) ActiveSheet.Cells(i, 11).CopyFromRecordset AdoConn.Execute(strSQL11) i = i + 1LoopAdoConn.CloseSet AdoConn = NothingMsgBox "数据提取完毕!"End SubSub update()'声明定义VBA语句中需要使用到的各个变量类型Dim AdoConn As New ADODB.Connection '定义变量AdoConn为连接数据库对象(ADODB是数据库访问组件,Connection是其中的一个对象),用于实现连接数据库连接等操作Dim D1 As Date '定义D1为日期型变量,用于业务日期赋值Dim D2 As Date '定义D2为日期型变量,用于业务日期赋值Dim N As Integer'Dim i As Integer '定义i为循环变量,用于初始化时基于数据库数据循环赋值'定义SQL语句所需要的4个字符串变量Dim strSQL1 As StringDim strSQL2 As StringDim strSQL3 As StringDim strSQL4 As StringDim strSQL5 As StringDim strSQL6 As StringDim strSQL7 As StringDim strSQL8 As StringDim strSQL9 As StringDim strSQL10 As StringDim strSQL11 As StringD1 = DateD2 = D1 + 1'需在工程中手动添加引用:工具-引用-Microsoft ActiveX Data Objects 2.8 'Library,Microsoft ActiveX Data Objects Recordset 2.8 Library'建立数据库连接'AdoConn.Open ("provider=msdaora; data source=orcl; user id=C##SCOTT;password=scott") 'Oracle 12.1.0.2.0-64 版本+Excel2016专业增强版,使用此种连接方式不可行 'AdoConn.Open ("provider=OraOLEDB.Oracle.1; data source=orcl; user id=C##SCOTT; password=scott") 'AdoConn.Open ("Driver={Microsoft ODBC for Oracle}; server=orcl; user id=C##SCOTT; password=scott") AdoConn.Open ("provider=OraOLEDB.Oracle.1; data source=orcl; user id=C##SCOTT;password=scott") ' Dim conn As New ADODB.Connection' 'Dim rs As ADODB.Recordset' Dim strConn As String' 'Dim strconn1 As String' Set conn = CreateObject("ADODB.Connection")' 'Set rs = CreateObject("ADODB.recordset")' strConn = "Driver=D:\app\client\pande\product\12.1.0\client_1\oledb\lib\OraOLEDB12.lib;Dbq=orcl;Uid=C##SCOTT;Pwd=scott;" 'OraClient11g_home1为oracle安装目录,Dbq为数据库名' conn.Open strConn '打开数据库N = ActiveSheet.Range("B1").End(xlDown).Row + 1 '获取下一业务日期 If (ActiveSheet.Cells(N - 1, 2) < Date) Then '最新一条记录对应的业务日期小于系统日期时,新增记录 '设置SQL查询语句 strSQL1 = "SELECT count(termid) FROM ikb" '当日词条总数 strSQL2 = "SELECT count(distinct phase) FROM ikb" 'Phase词条数(去重不为空,下同) strSQL3 = "SELECT count(distinct type) FROM ikb" 'type词条数 strSQL4 = "SELECT count(distinct subtype) FROM ikb" 'subtype词条数 strSQL5 = "SELECT count(termid) FROM ikb WHERE date_updated < to_date('" & D2 & "') AND date_updated >= to_date('" & D1 & "')" '更新日期为该日期的词条总数 strSQL6 = "SELECT count(distinct en) FROM ikb" 'EN词条数 strSQL7 = "SELECT count(distinct cn) FROM ikb" 'CN词条数 strSQL8 = "SELECT count(distinct jp) FROM ikb" 'JP词条数 strSQL9 = "SELECT count(distinct ed) FROM ikb" 'ed词条数 strSQL10 = "SELECT count(distinct cnd) FROM ikb" 'cnd词条数 strSQL11 = "SELECT count(distinct jpd) FROM ikb" 'jpd词条数 '执行查询并赋值 ActiveSheet.Cells(N, 1).Value = ActiveSheet.Cells(N - 1, 1).Value + 1 ActiveSheet.Cells(N, 2).Value = ActiveSheet.Cells(N - 1, 2).Value + 1 ActiveSheet.Cells(N, 12).CopyFromRecordset AdoConn.Execute(strSQL1) ActiveSheet.Cells(N, 3).CopyFromRecordset AdoConn.Execute(strSQL2) ActiveSheet.Cells(N, 4).CopyFromRecordset AdoConn.Execute(strSQL3) ActiveSheet.Cells(N, 5).CopyFromRecordset AdoConn.Execute(strSQL4) ActiveSheet.Cells(N, 13).CopyFromRecordset AdoConn.Execute(strSQL5) ActiveSheet.Cells(N, 6).CopyFromRecordset AdoConn.Execute(strSQL6) ActiveSheet.Cells(N, 7).CopyFromRecordset AdoConn.Execute(strSQL7) ActiveSheet.Cells(N, 8).CopyFromRecordset AdoConn.Execute(strSQL8) ActiveSheet.Cells(N, 9).CopyFromRecordset AdoConn.Execute(strSQL9) ActiveSheet.Cells(N, 10).CopyFromRecordset AdoConn.Execute(strSQL10) ActiveSheet.Cells(N, 11).CopyFromRecordset AdoConn.Execute(strSQL11) ActiveSheet.Cells(N, 14).Value = ActiveSheet.Cells(N, 12).Value - ActiveSheet.Cells(N - 1, 12).Value ActiveSheet.Cells(N, 15).Value = ActiveSheet.Cells(N, 13).Value - ActiveSheet.Cells(N - 1, 13).Value ActiveSheet.Cells(N, 16).Value = ActiveSheet.Cells(N, 14).Value + ActiveSheet.Cells(N - 1, 16).Value End If If (ActiveSheet.Cells(N - 1, 2) = Date) Then '最新一条记录对应的业务日期等于系统日期时,仅更新最后一条记录 (即,刷新当前数据) '设置SQL查询语句 strSQL1 = "SELECT count(termid) FROM ikb" '当日词条总数 strSQL2 = "SELECT count(distinct phase) FROM ikb" 'Phase词条数(去重不为空,下同) strSQL3 = "SELECT count(distinct type) FROM ikb" 'type词条数 strSQL4 = "SELECT count(distinct subtype) FROM ikb" 'subtype词条数 strSQL5 = "SELECT count(termid) FROM ikb WHERE date_updated < to_date('" & D2 & "') AND date_updated >= to_date('" & D1 & "')" '更新日期为该日期的词条总数 strSQL6 = "SELECT count(distinct en) FROM ikb" 'EN词条数 strSQL7 = "SELECT count(distinct cn) FROM ikb" 'CN词条数 strSQL8 = "SELECT count(distinct jp) FROM ikb" 'JP词条数 strSQL9 = "SELECT count(distinct ed) FROM ikb" 'ed词条数 strSQL10 = "SELECT count(distinct cnd) FROM ikb" 'cnd词条数 strSQL11 = "SELECT count(distinct jpd) FROM ikb" 'jpd词条数 '执行查询并赋值 ActiveSheet.Cells(N - 1, 12).CopyFromRecordset AdoConn.Execute(strSQL1) ActiveSheet.Cells(N - 1, 3).CopyFromRecordset AdoConn.Execute(strSQL2) ActiveSheet.Cells(N - 1, 4).CopyFromRecordset AdoConn.Execute(strSQL3) ActiveSheet.Cells(N - 1, 5).CopyFromRecordset AdoConn.Execute(strSQL4) ActiveSheet.Cells(N - 1, 13).CopyFromRecordset AdoConn.Execute(strSQL5) ActiveSheet.Cells(N - 1, 6).CopyFromRecordset AdoConn.Execute(strSQL6) ActiveSheet.Cells(N - 1, 7).CopyFromRecordset AdoConn.Execute(strSQL7) ActiveSheet.Cells(N - 1, 8).CopyFromRecordset AdoConn.Execute(strSQL8) ActiveSheet.Cells(N - 1, 9).CopyFromRecordset AdoConn.Execute(strSQL9) ActiveSheet.Cells(N - 1, 10).CopyFromRecordset AdoConn.Execute(strSQL10) ActiveSheet.Cells(N - 1, 11).CopyFromRecordset AdoConn.Execute(strSQL11) ActiveSheet.Cells(N - 1, 14).Value = ActiveSheet.Cells(N - 1, 12).Value - ActiveSheet.Cells(N - 2, 12).Value ActiveSheet.Cells(N - 1, 15).Value = ActiveSheet.Cells(N - 1, 13).Value - ActiveSheet.Cells(N - 2, 13).Value ActiveSheet.Cells(N - 1, 16).Value = ActiveSheet.Cells(N - 1, 14).Value + ActiveSheet.Cells(N - 2, 16).Value End IfAdoConn.CloseSet AdoConn = NothingMsgBox "数据提取完毕!"End Sub
更新数据页面:
报表正文页面:
阅读全文
0 0
- 学以致用——Excel连接Oracle生成iKB报告——Part3(功能优化)
- 学以致用——Excel连接Oracle生成iKB报告——Part1(使用VBA从数据库提取汇总数据)
- 学以致用——Excel连接Oracle生成iKB报告——Part2(Excel日报自动化的数据转换及用户界面)
- 学以致用——ikb知识库英文词条词频分析-Part3-使用Excel制作高频词标签云(VBA)
- 学以致用——iKB知识库日报(改进版)
- 学以致用——ikb知识库英文词条词频分析-Part1-数据提取(VBA)
- 学以致用——ikb知识库英文词条词频分析-Part2-大数据中高频词分析(Spotfire)
- 学以致用——将iMovie数据批量添加到iKb数据库中
- Java桌面应用程序开发——iKB知识管理系统的第一次功能增强
- 学以致用——微博文章内容统计分析之一(Excel+GraphLab)
- 学以致用——利用直方图分析个股涨幅(Excel+Spotfire)
- 学以致用——微博文章内容统计分析之二——我的电影(Excel+Spotfire)
- 学以致用——英文姓名高词频分析-使用Excel制作高频词标签云(VBA)
- Excel在统计分析中的应用—第六章—抽样与抽样分布-Part3(分层抽样)
- 学以致用—二项分布(伯努利分布)概率分布图的绘制
- 学以致用—股价月涨幅的计算
- CS231n 学习笔记(3)——神经网络 part3 :最优化
- Excel在统计分析中的应用—第二章—描述性统计-Part3-偏度(偏斜度和矩偏度系数)
- Excel使用经验——Excel连接Oracle,发生3706错误的解决办法
- springMVC+spring data jpa的使用
- 如何在Cygwin中使用Z-shell
- 继续逆向youdaodict
- VBA编码经验——日期类型的比较
- 学以致用——Excel连接Oracle生成iKB报告——Part3(功能优化)
- 他山定时 v1.0.7源码, 使用OHUI开发
- Activity相关笔记
- unity 加载图片文件
- [Amazon] Partion Array
- 个人第一篇博客 库文件 使用 教训
- UVA11645 Bits【位运算+大数】
- 17_7_13:合并两个有序链表。实现1+2+3+...+n,不使用常规方法
- SVN使用