运用VBA对MySql+EPR系统的二次开发.doc

1、摘要 本论文主要阐述了利用VBA对ERP系统进行二次开发的其中一种方法，详细介绍了从如何获取SQL语句、VBA连接数据库到代码实现、维护的全过程。目录引言7第一章ERP二次开发和Excel VBA7第一节 二次开发涵义7第二节 VBA涵义7第二章总体设计8第一节设计思想8第三节提取SQL语句并分析8第三章详细设计与实现11第一节系统主界面11第二节“生成预审表”对话框设计12第三节“类似”对话框设计17第四章二次开发的管理重点20第一节全面了解用户需求20第二节判断二次开发要求的合理性21第三节区分及处理二次开发上线前、后阶段21结束语21谢辞23参考文献24引言随着信息技术的进步及激烈市场竞

2、争的多元化，引进ERP这种代表了世界上最先进的管理思想和方法的管理信息系统是大势所趋。许多国内外著名公司基于自身的信息技术并根据企业整合的方法论开发出完整且复杂的ERP系统，堪称是企业参考模型的最佳典范。但是企业的业务流程与企业的文化特质是互不相同的，因此在导人ERP的过程中经常会产生ERP无法满足企业本身需求或与企业产生不合适的状况，因而产生了ERP用户化和二次开发的问题。所以在实施ERP系统的过程中用户化和二次开发就成为了必不可少的组成部分。第一章ERP二次开发和Excel VBA当企业需求与ERP系统发生不合适状况时，我们有多种解决方法，而当企业需求不能做出让步时则只能改变ERP系统了。

3、用户化和二次开发是两个容易搞混的概念，客户在项目的实施中对此往往不能很好的区分。但是如果不加以区分地将它们混为一谈则会出现很多弊端，从工作量和难易度看，用户化远远小于二次开发，而且它们各自适用的原则、步骤都是不同的。 第一节 二次开发涵义 通常把改动程序的工作称为二次开发，即“以成熟套装软件方式针对某用户实施ERP时，对于软件功能不适用处进行的修改行为”。虽然系统通过参数可调的形式可以部分满足不同用户的需求，但很多情况下这种“轻度”灵活会失效。当客户的业务不能通过简单的用户化实现时，就需要通过改动程序的二次开发来完成，如图l所示。图1 ERP系统用户化和二次开发第二节 VBA涵义VBA(Vis

4、ual Basic For Applications)是微软开发出来在其桌面应用程序中执行通用的自动化(OLE)任务的编程语言。VBA是Visual Basic的一个子集,VBA不同于VB，原因是VBA要求有一个宿主应用程序才能远行（需要在EXCEL等软件的运行下才能运行），而且不能用于创建独立应用程序。而VB可用于创建独立的应用程序。VBA可使常用的过程或者进程自动化，可以创建自定义的解决方案，最适用于来定制已有的桌面应用程序。 通常意义上的VBA就是在Office中包含着的一种加强Office功能的Basic语言。经过发展，在Office中，Word、Excel、Access、PowerP

5、oint等个软件都有了自己的程序设计语言，分别称为WordBasic、ExcelBasic、AccessBasic、PowerPointBasic (在Outlook中的开发语言为Visual Basic Scripting Edition)。通常统一称为VBA（VB for Application）。 第二章总体设计第一节设计思想对于ERP二次开发，最理想的是，有数据库的数据字典和开发文档，但除非是你开发的或者是您公司很有钱，买断了原码，否则都不会有。那怎么做呢？我们可以用一些简单的方法在数据库日志中提取部分有用的信息，进行分析和运用，所以开发过程可分用户的需求分析、提取相应若干的SQL语句

6、、用Excel VBA把用户需求呈现第二节用户需求分析用户需求分析很重要，因为这是涉及可行性和开发方法的方方面面，在这里我作了一个需求的例子：在原有的MySql+ERP系统提取数据，生成一个按一定格式的合同的预审表在生成前，程序自动提供没审核的合同给用户选择，用户也可以自行输入合同号用户可以指定一个编码，在生成预审表时如果有相对应的编码，那一单元格变颜色等等一些小细节以后内容都以这个例子进行设计和说明。第三节提取SQL语句并分析首先，提取语句必须开启mysql数据库日志，打开配置文件my.ini，在mysqld下加入一句“log”，然后重新启动mysql服务，然后在数扰库的文件夹下就多了一个

7、.log 的日志文件（如图2.31所示），这个文件就是我们要分析的重点。 图2.31打开原有的ERP系统，打开“合同订单”模块，随便查寻一个合同号，例如：EX1110040（如图2.32）图2.32好了，当进行查寻的同时，数据库日志也记录下了这些一连串的操作。用word打开日志文件，查找字符串“EX1110040”，然后很容易找到了一个完整的SQL语句（如图2.33），在mysql控制台运行一次得出记录数和数据内容，如果跟ERP系统的结果基本一至，就可以确认就是这一句了。图2.33然后再经过认真的研究，跟据用户的需要进行修改得到一个sql语句:SELECT SUM(IF(pre.name LI

8、KE %锁头%,mr.total,0) AS 锁头数,SUM(IF(pre.name LIKE %球%,mr.total,0) AS 球数,SUM(IF(pre.name LIKE %执手%,mr.total,0) AS 执手数,SUM(IF(pre.name LIKE %线%,mr.total,0) AS生产线数,mb.prop4,mb.cpremtotal23,mb.dm AS 产品类别,mb.prop3AS中文衬充,mb.prop6 AS扣板,ame AS 加工部门,a.adtdate AS 审核日期,a.khdhno AS 本厂合同,a.pjno AS 客户订单,a.date AS 登

9、记日期,a.xsdhdate AS 订货日期,ame AS 客户,e.name AS 业务员,g.currency AS 币别,a.hll AS 汇率,a.je AS 金额合计,a.package AS 合计件数,a.shpnaAS 审批人,a.shhna AS 审核人,h.username AS 制单人,a.rem1 AS RefNo,a.rem2 AS 合同交期,b.ptnno AS 客户代号,m.jhfsname AS SHIPPING,a.jhdate AS 排产交期,a.yjzlfindate AS 厂内交期,a.sf_bpAS 报批,a.bpnaAS 报批人,a.total AS

10、数量合计,a.newptnlxr AS 客户联系人,a.sqm AS 合计净重,z.ocaddr AS 发货地址,a.cbm AS 合计体积,a.exadate AS 审批日期,a.totalleft AS 销售出仓剩余量,a5.name AS PAYMENT,a.yjzlstartdate AS 预审交期,a.sf_pf_processed AS PFOK,a.doccnt AS 附件数,a.sf_onlyoso AS 库存订单,mb.gono AS 新编码,ame AS 旧编码,mb.gg AS 规格,mb.cd AS 工程号,ma.dw AS 单位,ma.price AS 单价,ma.j

11、gje AS 金额,ma.totalfin AS 出货完成量,ma.totalleft AS 出货剩余量,ma.custno AS Tariff,ma.rem AS 备注,ma.rem1 AS RefNo,ma.rem2 AS 合同交期,ma.rem3 AS 托盘号,ma.rem5 AS 箱号,ma.package AS 箱数,ma.weight AS 合计毛重,ma.finrate AS 完成率,ma.totaljcfin AS 产成量,ma.totaljcleft AS 剩余量,ma.cgtotal AS 外购数量,ma.sctotal AS 订货数量,ma.lmttotal AS 限制数

12、量,ma.ord AS 序号,mb.length AS 外箱长,mb.width AS 外箱宽,mb.height AS 外箱高,mb.prop1 AS E包装方式,mb.prop2 AS C包装方式,mb.prop3 AS 中文补充说明,mb.prop4 AS 锁匙备注,mb.prop5 AS 衬板备注,mb.prop6 AS 扣板备注,ma.jyjhdate AS 预审交期,ma.srckhdhtotalleft AS 原订单剩余数量,ma.rem9 AS 自定义列1,ma.rem10 AS 自定义列2,mb.rem1 AS 面饰,mb.rem2 AS 客户,mb.cpremtotal6

13、AS 毛重箱,mb.cpremtotal7 AS 净重箱,mb.cpremtotal9 AS 内箱数,mb.cpremtotal10 AS 客货号,mb.cpremtotal11 AS 系列,mb.cpremtotal12 AS 商标,mb.cpremtotal23 AS 锁头备注,mb.cpremtotal24 AS 客货号B,ma.plantotal AS 计划数量,ame1 AS 产品电脑纹,ame2 AS 中箱电脑纹,ame3 AS 外箱电脑纹,mb.markmemo1 AS 面饰印刷注释,mb.markmemo2 AS 卡纸吸塑罩彩盒类型,mb.markmemo3 AS 货品印刷注释

14、,mb.markmemo4 AS 产品描述,mb.markmemo6 AS 托盘电脑纹,mb.byname AS 合并装说明,ma.needdate AS 排产交期,ma.srctotal AS 原市场数量,mb.outergono AS 厂型号,ma.jhdetail AS 交货明细,ma.sqm AS 合计净重,wgt AS 单位净重,mb.packages1 AS 装箱数,ma.cbm AS 体积,mb.con20y AS 单位体积,mb.vendorgono AS 客户型号,mb.sf_bomok AS BOMOK,mb.ename AS 品名FROM ord_khdhmain a I

15、NNER JOIN cod_partner b ON a.ptnid=b.ptnid INNER JOIN hrm_employee e ON a.ywyid=e.id INNER JOIN cod_hb g ON a.hbid=g.hbid INNER JOIN sys_user h ON a.czyid=h.userid INNER JOIN cod_jhfs m ON a.jhfsid=m.jhfsid LEFT OUTER JOIN cod_ptnclntshipto z ON a.shiptoid=z.id INNER JOIN cod_paytime a5 ON a.payid=a

16、5.payid INNER JOIN ord_khdhmini ma ON a.khdhid=ma.khdhidINNER JOIN cp_good mb ON ma.goid=mb.goidINNER JOIN cod_bm mc ON mb.bmno=mc.bmnoLEFT OUTER JOIN mrp_pre_prd mr ON mb.dm=mr.nameINNER JOIN mrp_pre_wc pre ON mr.prewcid=pre.idWHERE a.khdhno= EX1110040 AND a.tempid=1008 GROUP BY ma.id ORDER BY a.kh

17、dhno,ma.ord;用同样的方法可以得出其它有用的sql语句第四节VBA对MySql数据库的连接vba连接mysql方法很多，最常用的是通过odbc连接。首先在网上下载一个连接驱动: mysql-connector-odbc-3.51.27-win32，在mysql官网是免费提供，安装好。然后在编写代码前先引用“Microsoft ActiveX DataObjects 2.5Library”，在编写代码时使用下面的一段代码连接数据库： Dim strCn As String Dim cn As New ADODB.Connection strCn = driver=mysql odbc

18、3.51 driver; & _ server=192.168.100.1; & _ database=hf; & _ uid=test; & _ pwd=test & _ ;Stmt=set names gb2312 连接字符串 cn.ConnectionString = strCncn.Open 打开数据库第三章详细设计与实现第一节系统主界面跟据用户的要求，初步设计出一个Excel表格，用来输出数据，一个用于启动“生成预审表“的按钮，一个用户可以输入编码的单元格，表“setup”是用来存放程序要用的sql语句（在交给用户时可以隐藏）（如图3.1）图3.1第二节“生成预审表”对话框设计在这个

19、模块里，当窗口加载时，自动在列表框添加没有审核的合同的合同号，可以手动输入合同号，点增加合同，,然后选择合同号成蓝色生成预审表（如图3.2）图3.2具体代码如下：Option Explicit按下生成预审表按钮Private Sub CommandButton1_Click() Dim iA As Integer Dim sA As String 组合成 (ex123,ex466) 的字符串，给下面的sql语句用 sA = ( For iA = 0 To Me.ListBox1.ListCount - 1 If Me.ListBox1.Selected(iA) Then 列表不包含“”，下面一

20、句就出错。在手动输入时就有可能不包括客户项 If InStr(Me.ListBox1.List(iA), ) 0 Then sA = sA & & Trim(Mid(Me.ListBox1.List(iA), 1, InStr(Me.ListBox1.List(iA), ) - 1) & , Else sA = sA & & Trim(Me.ListBox1.List(iA) & , End If End If Next iA sA = IIf(sA = (, sA, Mid(sA, 1, Len(sA) - 1) sA = sA & ) Me.Hide If sA = () Then Exi

21、t Sub 输出数据 getDate sA Unload MeEnd Sub点取消按钮Private Sub CommandButton3_Click() Unload MeEnd Sub点击添加按钮Private Sub CommandButton4_Click() If Me.ComboBox1.Text Then Me.ListBox1.AddItem Me.ComboBox1.Text End IfEnd Sub加载窗口时Private Sub UserForm_Activate() Dim strCn As String Dim cn As New ADODB.Connection

22、strCn = driver=mysql odbc 3.51 driver; & _ server=192.168.100.1; & _ database=hf; & _ uid=test; & _ pwd=test & _ ;Stmt=set names gb2312 连接字符串 cn.ConnectionString = strCn cn.Open 打开数据库 Dim sSql As String Dim Rs1 As ADODB.Recordset 已经报批但没审核 sSql = SELECT a.khdhno AS 合同号,b.ptnno & _ FROM ord_khdhmain a

23、 LEFT OUTER JOIN cod_partner b ON a.ptnid=b.ptnid & _ WHERE a.shpid=0 AND a.shhid=0 AND a.sf_bp=Y AND (a.tempid=1008 OR a.tempid=1045) & _ ORDER BY a.xsdhdate DESC ; Set Rs1 = cn.Execute(sSql) 把数据库得出的数,在列表框中显示 Me.ListBox1.Clear Do Until Rs1.EOF UserForm1.ListBox1.AddItem Rs1(合同号) & & Rs1(ptnno) & Rs

24、1.MoveNext Loop Rs1.Close cn.CloseEnd Sub输出数据Function getDate(ByVal sSqlin As String) As Boolean SQL语句 Dim sSql As String sSql = Sheets(setup).Cells(1, 1).Comment.Text & _ WHERE a.khdhno IN & sSqlin & AND (a.tempid=1008 OR a.tempid=1045) GROUP BY ma.id ORDER BY a.khdhno,ma.ord; Dim strCn As String D

25、im cn As New ADODB.Connection strCn = driver=mysql odbc 3.51 driver; & _ server=192.168.100.1; & _ database=hf; & _ uid=test; & _ pwd=test & _ ;Stmt=set names gb2312 连接字符串 cn.ConnectionString = strCn cn.Open cn.CursorLocation = adUseClient Dim max_r As String Sheets(订单预审表).Activate max_r = Sheets(订单

26、预审表).UsedRange.Rows.Count If max_r = 5 Then Sheets(订单预审表).Range(Cells(5, 1), Cells(max_r + 1, 1).EntireRow.Delete End If 运行sql 语句 Dim Rs1 As ADODB.Recordset Set Rs1 = cn.Execute(sSql) 提取数据 Dim iA As Integer iA = 5 If Rs1.RecordCount 0 Then Rs1.MoveFirst Do Until Rs1.EOF With Sheets(订单预审表) .Cells(iA,

27、 1) = iA - 4 .Cells(iA, 2) = 1 .Cells(iA, 3) = Rs1(业务员) .Cells(iA, 4) = Rs1(客户) .Cells(iA, 5) = Rs1(本厂合同) .Cells(iA, 6) = Rs1(客户订单) .Cells(iA, 7) = Rs1(订货日期) .Cells(iA, 9) = Rs1(排产交期) .Cells(iA, 13) = Rs1(客货号) .Cells(iA, 14) = Rs1(旧编码) If Rs1(旧编码) = .Cells(2, 5) Then .Cells(iA, 14).Interior.ColorInd

28、ex = 44 .Cells(iA, 15) = recenLB(Rs1(产品类别) .Cells(iA, 16) = Rs1(产品类别) .Cells(iA, 17) = Rs1(加工部门) .Cells(iA, 18) = Rs1(C包装方式) .Cells(iA, 19) = Rs1(订货数量) .Cells(iA, 20) = Rs1(订货数量) * Rs1(生产线数) .Cells(iA, 21) = Rs1(订货数量) * Rs1(锁头数) .Cells(iA, 22) = Rs1(订货数量) * Rs1(执手数) .Cells(iA, 23) = Rs1(订货数量) * Rs1(

29、球数) End With iA = iA + 1 Rs1.MoveNext Loop Rs1.Close cn.CloseEnd FunctionFunction recenLB(ByVal sLB As String) As String 去除+后字符- If sLB Like *+* Then sLB = Mid(sLB, 1, InStr(sLB, +) - 1) Else sLB = sLB End If 去除前面的数字- Dim iA As Integer For iA = 1 To Len(sLB) If Not (Asc(Mid(sLB, iA) = 48 And Asc(Mid

30、(sLB, iA) 1 Then sLB = Mid(sLB, iA) End If - Dim C As Range Set C = Sheets(产品类别码规律).Columns(9).Find(what:=sLB, lookat:=xlWhole, LookIn:=xlValues) If Not C Is Nothing Then recenLB = Sheets(产品类别码规律).Cells(C.Row, 13) Else recenLB = End IfEnd Function 图3.22还有一个人性化的功能，当手动输入合同号时可以只输入合同号的前几个字符，点下拉框，程序会自动列出

31、以所输入的字符开头的所有合同号，给用户选择（如图3.22），所以要增加下面的代码。点击下拉箭头时Private Sub ComboBox1_DropButtonClick() Dim sInput As String sInput = Me.ComboBox1.Text 输入内容不能为空 AND 没有在下拉框选中项（-1）,因为收起下拉框时也发生这事件 If sInput And Me.ComboBox1.ListIndex = -1 Then Dim strCn As String Dim cn As New ADODB.Connection strCn = driver=mysql odb

32、c 3.51 driver; & _ server=192.168.100.1; & _ database=hf; & _ uid=test; & _ pwd=test & _ ;Stmt=set names gb2312 连接字符串 cn.ConnectionString = strCn cn.Open 打开数据库 Dim sSql As String Dim Rs1 As ADODB.Recordset 查看类似输入的合同号 sSql = SELECT a.khdhno AS 合同号,b.ptnno & _ FROM ord_khdhmain a LEFT OUTER JOIN cod_p

33、artner b ON a.ptnid=b.ptnid & _ WHERE a.khdhno LIKE & sInput & % AND (a.tempid=1008 OR a.tempid=1045) & _ ORDER BY a.xsdhdate LIMIT 50; Set Rs1 = cn.Execute(sSql) 把数据库得出的数,在列表框中显示 Me.ComboBox1.Clear Do Until Rs1.EOF Me.ComboBox1.AddItem Rs1(合同号) & & Rs1(ptnno) & Rs1.MoveNext Loop Rs1.Close cn.Close

34、End IfEnd Sub第三节“类似”对话框设计这个也是用户要求的人性化功能，添加一个右键菜单项，当手动输入编码时可以只输入编码的前几个字符，点右键，选择新增的右键菜单项，程序弹出一个窗口（如图3.31），自动列出以所输入的字符开头的所有编码，给用户选择（如图3.32） 图3.31图3.32要在Excel添加右键菜单，就要在ThisWorkbook添加下面代码：Option Explicit-激活工作簿Private Sub Workbook_Activate() AddMenu_BOMEnd Sub-不激活工作簿Private Sub Workbook_Deactivate() MyMen

35、u_Delete_BOMEnd Sub-生成快捷菜单Private Sub AddMenu_BOM() 加载快捷菜单 With ThisWorkbook.Worksheets.Application.CommandBars(Cell) .Reset .Controls.Add(Type:=msoControlButton, Before:=1, Temporary:=True).Caption = 类似. .Controls.Item(1).OnAction = CheckLike .Controls(2).BeginGroup = True End WithEnd Sub-删除快捷菜单Pri

36、vate Sub MyMenu_Delete_BOM() On Error Resume Next ThisWorkbook.Application.CommandBars(Cell).FindControl(msoControlButton).DeleteEnd Sub然后再新建一个模块，用于执行右键菜单项的功能，模块代码：Option ExplicitPrivate Declare Function GetCursorPos Lib user32 (lppoint As POINTAPI) As LongPrivate Type POINTAPI X As Long Y As LongEn

37、d Type类似Private Sub CheckLike() Dim FrmCheck As New likeForm1 Dim strIn As String Dim cn As New ADODB.Connection Dim Rs1 As ADODB.Recordset Dim strCn As String Dim strSql As String Dim strTypeA, strTypeB As String strTypeA = ActiveSheet.Cells(1, Selection.Column) strTypeB = ActiveSheet.Cells(2, Sele

38、ction.Column) strIn = Trim(Selection.Value) 读入选择的单元格的数到变量 If strIn Then If InStr(strTypeA, 合同) 0 Or InStr(strTypeB, 合同) 0 Then 合同号 strSql = select a.khdhno,b.ptnno,a.tempid from ord_khdhmain a LEFT OUTER JOIN cod_partner b on a.ptnid=b.ptnid where khdhno like & strIn & % group by khdhno order by khd

39、hno limit 300 ElseIf InStr(strTypeA, 新) 0 Or InStr(strTypeB, 新) 0 Then strSql = select a.gono,a.ename from cp_good a where a.gono like & strIn & % and (a.sf_agent=n) and a.gdkdno like R% and a.stopuse=N order by gono limit 300 ElseIf InStr(strTypeA, 旧) 0 Or InStr(strTypeB, 旧) 0 Then strSql = select ame