Excel VBA技术在中小制造企业信息管理系统中的应用

王俊飞, 唐克岩, 向 渝,陈 伟

(1.成都理工大学工程技术学院，四川 乐山 614000) (2.山西中谷科贸有限公司，山西 太原 030024)

随着企业信息化技术应用的不断深入，管理信息化已成为企业信息化的重要内容。对中小制造企业而言，由于资金短缺，信息基础设施薄弱，严重限制了其信息化的实现。中小制造企业业务流程具有多变性、灵活性、动态性、柔性化的特点，其信息管理更为繁杂，通用商业管理软件很难适应这类企业的需求[1]。故目前我国大部分中小制造企业只在各种单项业务中应用信息管理软件，缺乏集成化，数据不能在相关部门间实现实时共享，造成企业信息分布散乱、信息滞后，致使管理者不能及时合理作出正确决策[2]，给企业造成巨大的经济损失。

针对此情况，本文以企业实际应用为目标，在通用办公软件Excel 2007平台上利用其内嵌VBA进行二次开发，并综合应用数据库技术和Web网络技术，构建一站式集成化自动信息管理系统，有效改变目前中小制造企业信息分布散乱、信息滞后的现状，实现了企业信息自动化、集成化、网络化管理。

1 系统设计目标及总体构架

本系统以中小制造企业实际需求为背景，采用模块化结构，使用XML语言对通用办公软件Excel2007的功能区进行定制开发，将供应商与客户管理、生产管理、人事管理、财务管理、产品管理、市场销售管理等功能集中在一个平台下。利用VBA可视化编程技术、VBA访问数据库技术及Excel与Web网络交互技术对Excel 2007进行二次开发，在有效利用Excel自身强大的数据处理与分析功能的基础上，通过VBA编程对Excel 2007功能进行拓展，使企业信息管理实现了自动化、集成化、网络化，保证企业各部门之间的信息同步共享，企业与客户之间能实时沟通。系统整体构架如图1所示。

图1 系统总体结构框图

由图1可知，系统以数据库为核心，将各功能模块连接成一个有机整体，保证了系统各功能模块间数据的同步。利用Excel与Web网络交互技术实现了系统数据与网页数据的交互，使用户在Excel平台上可通过网络获取所需数据及发布信息，实现企业各部门及与客户之间的信息共享。

2 系统功能实现策略

为了保证系统的安全性，系统运行后，首先弹出用户登录界面，如图2所示，此时Excel 2007转为后台运行，用户输入正确的验证信息后进入系统主界面，如图3所示。功能模块的选择界面采用Excel办公软件的功能导航界面，用户操作鼠标点击所选的功能按钮即可进入相应的模块。

图2 用户登录界面

图3 系统功能主界面

2.1 系统功能主界面设计开发

本文综合应用WinRAR、记事本以及OfficeCustomUIEditor软件，使用XML语言对Excel 2007功能区进行定制开发，形成了功能导航的系统主界面，并为各功能按钮指定了相应的VBA回调函数来实现相应的功能。具体开发步骤如下[3-5]：

1)将“面向中小制造企业信息管理系统Excel 2007文件”保存为“Excel启用宏的工作薄”，即.xlsm文件格式。

2)利用Office 2007 Custom UI Editor打开“面向中小制造企业信息管理系统.xlsm”，输入XML代码对Excel 2007功能区进行定制开发，此时定制开发的功能区以英文显示。

3)右击“面向中小制造企业信息管理系统.xlsm”，在【打开方式】中选择“winRAR压缩文件管理器”，将CustomUI文件夹中customUI.xml拖至系统所在文件夹。

4)利用记事本打开系统所在文件夹中的customUI.xml，利用中文对显示的英文进行替换，然后再拖至用“winRAR压缩文件管理器”打开的CustomUI文件夹中，再次打开文件，定制的功能区便以中文显示(如图3所示)。

5)在VBA标准模块中编写功能按钮相应的回调过程函数Ribbon_OnAction，通过XML代码“onAction=Ribbon_OnAction”将回调过程函数Ribbon_OnAction与功能按钮绑定。至此，运用鼠标点击功能按钮就可进行相应的操作。

2.2 各功能模块的可视化实现

系统采用可视化的VBA编程技术开发出适合中小企业数据管理的各种信息模板及窗体界面，结合定制的功能导航界面，有效地实现了以人机交互对话的方式来完成对各功能模块的自动管理。用户通过操作鼠标点击系统主界面功能按钮，即可弹出相应的交互窗体界面或Excel信息模板界面(如图4为物料入库单模板及操作界面)。系统采用ADO数据库访问技术使用户界面的数据与系统数据库实现交互，用户可快速地完成有关数据的输入、查询及修改等。

2.3 各功能模块间数据交互

2.3.1各功能模块间Excel数据同步交互企业的各种数据信息总是不断动态变化的(比如员工工作部门的调动)，相关的数据需要在企业各部门之间进行整合统一[6]，以保证各功能模块间相关数据信息的同步。本文采用与office办公软件集成的小型数据库系统Access作为中间存储空间，来实现各部门之间的数据同步。系统利用ADO数据库访问技术并结合VBA编程实现了Excel用户与企业信息数据库Access的数据交互。以人事管理模块中新增员工信息时，财务管理模块 中工资核算报表相关信息如何实现同步为例来说明。其实现的过程及主要VBA代码如下：

图4 物料入库单模板及操作界面

1)前期准备。在VBE编辑器中点击“工具/引用”菜单项，勾选Microsoft ActiveX Data Objects 2.8 Library，添加ADO库文件。

2)程序设计。前期准备完成后，进入Excel VBA编程环境进行程序设计，编写VBA代码实现Excel用户与Access数据库之间的数据交互。通过ADO技术访问系统数据库的过程代码如下(只说明其访问过程)。

Dim cnn As New ADODB.Connection′建立数据库连接

Dim rst As New ADODB.Recordset′建立记录集

Dim mydata As String′定义数据库名称变量

Dim mytable As String′定义数据表名称变量

Private Sub CommandButton4_Click()

mydata = ThisWorkbook.Path & ″中小制造企业基本信息.accdb″

mytable = ″员工基本信息″

cnn.Open ″Provider=Microsoft.ACE.OLEDB.12.0; Data source =″& mydata

rst.Open mytable, cnn, adOpenDynamic, adLockOptimistic

rst.AddNew

rst.Fields(″工号″) = Me.TextBox8.Text

rst.Fields(″姓名″) = Me.TextBox1.Text

rst.Update

End Sub

通过以上VBA代码可将新增员工信息添加至Access数据库中的“员工基本信息”表中，再编写从数据库读取信息的VBA代码可将新增信息输出到财务模块相应窗体界面，实现数据的同步更新。

在图5所示的界面中点击新增按钮，将新增员工信息自动存入Access数据库，当操作财务模块中工资核算功能按钮时，再自动将数据库中新员工的信息输出到员工工资信息窗体中，如图6所示。

图5 员工信息管理窗体

2.3.2Excel数据表自动生成Word报告

为了将各部门的Excel表格数据自动生成企业管理者所需的Word报告，系统通过VBA编程建立Excel与Word之间的连接，实现了自动将Excel表格数据输出到Word，形成企业的各种Word分析报告。Excel平台上建立与Word之间连接的VBA代码如下(只说明Excel与Word的建立过程)。

Sub ExcelToWord()

Dim objWordApp As Word.Application ′定义Word应用程序

Dim objWord As Word.Document ′定义Word文档Set objWordApp = New Word.Application Set objWord = objWordApp.Documents.Add ′建立一个空白Word文档

图6 员工工资信息管理窗体

objWord.Application.Visible = True

With objWord.Application ′将Excel数据自动填入新建空白Word文档

.Selection.Style = .ActiveDocument.Styles(″标题 1″)

.Selection.TypeText Range(″B1″)

End With

objWord.SaveAsFilename:=″C:ExcelToWord.docx″;FileFormat:=wdFormatXMLDocument ′保存Word文档

objWord.Close ′关闭Word文档

objWordApp.Quit ′退出Word文档

End Sub

在Excel表格功能按钮的Click事件中调用上述过程代码实现功能按键与VBA程序的绑定，绑定后单击功能按钮即可将Excel中的数据信息自动输出到Word文档。

2.4 Excel与网页数据交互

典型的基于Excel的应用程序数据获取与发送均是依靠用户手动操作[5]，本系统通过对工作表与用户窗体中的控制按钮指定VBA代码模块，采用OLE自动化技术实现对相关Internet网页的自动访问。同时应用Excel内置的Web查询功能及Web服务器实现Excel与Internet网页之间的数据交互，使用户在Excel平台上可通过互联网获取数据、发布数据信息，实现企业各部门之间及与客户之间的数据共享。

2.4.1采用OLE自动化技术访问相关网页

在Excel工作表与用户窗体界面中为相应的控件指定特定功能的VBA模块，通过鼠标点击按钮即可进入相关网页，实现在Excel平台上方便地访问相关网页。文中采用OLE自动化技术对InternetExplorer对象进行创建与控制，实现对相关网页的自动化访问。在如图7所示的材料供应商管理窗体中，点击“进入公司网页”按钮，即可方便地访问相关公司网页。实现步骤如下：

1)前期准备。在VBE编辑器中点击“工具/引用”菜单项，选中【Microsoft Internet Controls】和【Microsoft HTML Object Library】库文件。

2)编制自动登录网页VBA模块代码如下。

Private Sub CommandButton3_Click()

Dim ie As InternetExplorer

Dim strURL As String ′用来指定网页地址

Set ie = CreateObject(″InternetExplorer.Application″) ′使用CreateObject创建对象

strURL = ″http://www.shanxizhonggu.com/″

ie.Navigate strURL ′使用Navigate打开指定网页

ie.Visible = True ′设置对象可见，否则后台运行，用户无法看到

End Sub

其中网页地址需要通过ADO访问数据库中材料供应商信息表来获取，ADO访问数据库方法前面已介绍，此处为了简化程序而直接给出网页地址。

2.4.2应用Web查询功能获取网页数据

当企业需要对网页表格数据进行相关分析时(例如对产品销售情况进行分析，图8)，可通过Excel内置的Web查询功能QueryTable从网页中获得包含所需信息的表格，并将其读入Excel工作薄，同时设置在打开工作薄时自动更新数据[5]，通过对工作薄中的操作按钮绑定相关VBA程序代码， 实现对数据的自动化分析，并自动绘制各种分析图表。如图8中输入产品型号、年份、季度，操作数据获取按钮自动从公司网页获取产品销售数据，主要VBA代码如下。

Sub DownloadSalesdata ()

Dim strQuery As String, i As Integer ′定义查询字符串

Dim iYear As Integer, iQtr As Integer ′定义查询年与季度的变量

Dim iRow As Integer, iCol As Integer

iYear = Cells(2, 3) ′将输入的年赋值给年变量

iQtr = Cells(3, 3) ′将输入的季度赋值给季度变量

strQuery = ″URL; _

http://www.shanxizhonggu.com /trade/lsjysj_″ & Cells(1, 3)

strQuery = strQuery & ″.html?year=″ & iYear

strQuery = strQuery & ″&season=″ & iQtr ′ 将产品型号、查询时间赋值给查询字符串

Range(″4:″ & 2 ^ 20).ClearContents ′ 导入数据前清空工作表的内容

With ActiveSheet.QueryTables.Add_(Connection:=strQuery ,Destination:=Range(″A4″)) ′新建查询表

.Name = ″history″ ′定义查询名词

.RefreshOnFileOpen = True ′设置打开工作薄时自动更新数据

.BackgroundQuery = True ′设置查询表的查询在后台执行

.SaveData = True′设置与工作薄一起保存查询数据

.PreserveFormatting = True′设置保留单元格格式

.AdjustColumnWidth = False′禁止自动调整列宽

.RefreshPeriod = 0 ′设置定时刷新无效

.WebSelectionType = xlSpecifiedTables′表示选择指定的表格

.WebFormatting = xlWebFormattingNone ′忽略网页表格格式

.WebTables = ″4″ ′指定要导入的表格

.Refresh BackgroundQuery:=False ′更新查询表

End With

End Sub

2.4.3通过Internet发布企业信息

将企业的相关信息通过Internet发布在网页上，供客户和企业员工通过网页方便地获得相关信息。系统具体实现的过程分两步，第一步以Microsoft中集成的IIS5.1构建Web服务器作为发布的平台；第二步将要发布的结果工作表保存为网页格式进行发布[5]。文中首先创建一个工作薄模板，然后将应用程序生成的结果复制到摸板中相应的位置，并将该HTML文件保存到网络服务器。实现发布的主要程序如下。

Sub publishResultsToWeb()

Dim oBk As Workbook

Dim osht As Worksheet

Set oBk =Workbooks.Add _ (″C:mydirWebTemplate.xlsx″)′ 创建Web模板工作薄

Set osht = oBk.Worksheets(1) ′ 获取工作薄的第一个工作表

Osht.Range(″Profits″).Value=Workbooks(″Re_sults.xlsx″) _

.Worksheets(″Financials″).Range(″Profits″).Value ′ 填充结果

oSht.SaveAs ″http://locahost/ResultsJuly2016.htm″, xlHtml ′另存为网页格式并保存到服务器

oBk.Close ′关闭工作簿

End Sub

3 结束语

企业的许多数据资料都是以Excel表的形式 输入或保存的。本文的中小制造企业信息管理系统是在Excel 2007平台上进行二次开发的，能够保证数据的准确性，并有效地利用Excel自身强大的数据处理分析功能。通过VBA编程拓展Excel功能，使数据的处理实现自动化、批量化，且通过更为直观的用户窗体来操作，使操作更加直观、方便。将企业各功能模块的数据信息集中在Excel 2007平台上进行统一管理，有效解决了目前中小企业信息分散的状况，实现了信息的集成化。系统采用模块化、组件式构架，使系统具有可维护性与可扩展性，企业可根据实际需要方便地对其功能区进行扩展与维护。

本系统在某中型制造厂的实际应用表明，该系统有效地实现了预期的功能，使企业取得了良好的经济效益，具有一定的实用性，同时为同类软件的开发提供了新的思路与参考。