窗体顶端
利用VBA在Excel和Word中完成复杂的数据引用
作者:王贵 石仙
摘要：文章以编程为基础，介绍了在基于Office的生产办公中，如何利用VBA语言在常用的Excel和Word中进行数据交叉引用和综合，并分析其中的重点和难点，以实现将复杂、重复的日常工作自动化，从而提高用户的工作效率。
关键词：VBA编程；数据引用；Excel；Word
中图分类号：TP311文献标识码：A文章编号：1009-3044(2011)24-5908-03
Complicated Data Reference Based on VBA in Excel and Word
WANG Gui, SHI Xian
(Ertan Hydropower Development Company,Ltd. Ertan Hydropower Station, Panzhihua 617000, China)
Abstract：In the way of programing,how to cross-reference and integrate data in the commonly used Excel and Word with VBA languages in Office-based work item will be discussed, and analyze the important and difficult,then make achievement that complicated,repetitive daily work run automatically and improve the efficiency of work.
Key words: VBA programing; data reference; Excel; Word
在日常的Office办公中，经常出现标准命令无法满足用户需求或者标准命令的操作过程过于繁琐等方面的实际问题，VBA作为嵌入在Office套件中的程序开发语言，可以使Office应用程序得到扩展或者将Office应用程序及数据集成到其它应用程序中，解决用户的诸多难题，使得Office更加实用和智能化，从而提高用户的工作效率。
用户无论是在Excel中还是在Word中，建立和管理VBA都使用统一的方法和标准。Application是Excel、Word对象模型中最高级别的对象，表示Excel、Word程序自身[1]。Workbooks 、Worksheet 等又为Application对象，Excel应用程序在后台运行时，可以通过他们来访问具体的工作表等。Word VBA也提供了很多对象，在它的Application对象中有个Documents集合，其中包含每个打开文档的Document对象，利用这个对象可以访问任何打开的文档。Tables对象用来表示文档中的一个表格，Cell对象表示表格中的一个单元格[2]。
1 问题描述
某公司在每个月结束之后都要对该月每天以及全月的重要的生产数据进行统计和分析，所需数据格式样表如表1所示。该问题的复杂性在于表1中的数据来自不同的Excel报表和Word报表中，数据文件的结构如图1所示。由于该工作涉及文档繁多，数据量庞大，每次依靠人工抄写和统计都耗费大量的人力和时间，工作繁琐重复，且经常难以保证数据的正确性，本文就针对这个复杂问题，讨论在Office中利用VBA编程如何实现自动提取和生成所有数据的功能。
2 VBA程序
2.1 程序准备
由上可知，我们需要做的是从Excel的角度用VBA程序来进行数据综合。要顺利读取各种所需要的数据，必须先做好一些准备工作：
准备一 定义变量
Dim strdir, strfl As String'定义地址变量为字符串变量。
Dim i As Integer'定义将用到的循环变量。
Dim axlApp As Excel.Application'将Axlapp指向Excel应用程序本身。
Dim axlBook As Excel.Workbook'将axlBook定义为工作薄类型。
Dim sheet,asheet As Excel.Worksheet '将sheet,asheet定义为工作表类型。
由于涉及到的工作薄、工作表、数据量巨大，所以我们必须要定义足够的变量来满足程序流程的要求，这将使得后续的程序语言组织起来较为灵活，提取数据的方法也可以多样。 
准备二 计算当月的天数
Dim y,m,d,ds As Integer
Set asheet =ActiveWorkbook.Sheets(3)'将asheet指向当前工作薄的第三个工作表。
y=Year(asheet.Cells(3,1))
m=Month(asheet.Cells(3,1))
d=Day(asheet.Cells(3,1))
ds=DateSerial(y,m+1,1)-DateSerial(y,m,1)'计算当月天数ds。
For i=1 To ds-1
asheet.Cells(3+i,1)=asheet.Cells(3,1)+i '在第一列的中填入日期
Next
单元格cell(3,1)是第一个日期所在位置，如表1所示，上述程序通过year、month、day函数从该单元格中分别提取年、月、日的数据。DateSerial函数可以返回表示已指定年月日的Date值，DateSerial(y,m+1,1)的返回值即为次月1日date值，用它减去当月1日得date值即为当月的天数。For(……)Next循环的作用是在第一列中依次输出当月每天的日期值，如2011年2月，则日期一直到
以上一段程序的功能是，用户可根据实际需要自定义需要分析的月份，只需要在cell(3,1)中输入起始日期，如
准备三 定义数据文件所在地址
If (Right(ActiveWorkbook.Path,1)<>"\")
Then strdir=ActiveWorkbook.Path+"\"
Else strdir=ActiveWorkbook.Path
End If
以上一段If（…）Else…判断语句将strdir地址变量指向本报表所在的文件夹地址，在提取数据时，只要把所有数据文档都放到本报表所在的文件夹地址下即可对其进行识别，这样有利于对文档的统一管理和操作。
2.2 读取数据
A列数据从Excel表格中获得，其名称格式为“yyyy年mm月dd日报表”：
Set axlApp=New Excel.Application '新建一个Excel.EXE的后台实例，专用来打开数据所在的工作薄。
Fori=0 To ds-1
strfl=strdir+"Excel报表\"+Format(ActiveWorkbook.Sheets(3).Cells(3,1)
+i,"yyyy")+"年"+Format(ActiveWorkbook.Sheets(3).Cells(3,1)+i,"mm")
+"月" +Format(ActiveWorkbook.Sheets(3).Cells(3,1)+i,"dd")+"日报表.xls"
Set axlBook=axlApp.Workbooks.Open(strfl)'打开报表。
Set sheet=axlBook.Worksheets("今日生产日报")'指向数据所在工作表。
ActiveWorkbook.Sheets(3).Cells(3+i,2)=sheet.Cells(38,27)'读取A列数据。
axlBook.Close(False)'关闭打开的报表。
Next
Format()在对时间进行格式化输出时的意义在于返回一定格式的字符串时间量，Format(date time,"####")表示将date time以####的格式输出。利用一个For(……)Next循环，随着整型变量i的变化，便可以将strfl这个变量指向与第一列日期相吻合的生产日报表。
axlApp.Workbooks.Open()是一个将工作薄打开的常用语句格式，并将sheet指向了每天的生产日报中名为“今日生产日报”的工作表，然后将对应单元格的数据提取到当前的数据报表。这里axlBook.Close(False)是一个很重要的语句，它作用在于将打开的工作薄关闭，用于防止打开的工作薄过多造成程序积压，最终导致程序运行缓慢或者电脑死机等现象。
总结以上方法，主要以日期为名称标识，按照“文件夹→文件名→工作薄→工作表→单元格”的顺序依次找到所需要的数据，然后逐个提取并在目标报表的指定单元格输出，完成繁琐、复杂但有规律的数据提取功能。
2.3 提取B列数据
B列数据来源于“strdir\Word报表\生产日报yyyymmdd.doc”
Dim mmWord As Document’定义一个Word的对象类型。
Dim app As Word.Application’将app指向Word应用程序。
Set app=CreateObject("Word.application")’新建一个Word.EXE后台实例。
Fori=0 To ds-1
n=y*10000+m*100+d+i'随着i的变化，求出对应“yyyymmdd”格式的值。
strfl=strdir+"Word报表\"+"生产日报"+CStr(n)+".doc"’将strfl指向所要访问的Word文档。
Set mmWord=app.Documents.Open(strfl)’打开这个文档。
ActiveWorkbook.Sheets(3).Cells(3+i,3)=mmWord.Tables(2).Cell(3,2)'提取数据。
mmWord.Close False
Next
Set mmWord = Nothing
app.Quit'关闭Word后台实例。
CStr()函数用于将数字转换成字符串类型，便于与其他字符类型相加。非常值得一提的是，通常很多方法从Word中把数据提取到Excel中后，所输出的数据都为字符串类型，而且还会有多余的字符一同输出，如“372.64”输出为“372.64”，导致该数据不能直接与其他单元格的数据进行数学运算，所以需要一个附加程序来对其进行转换。仅删除多余字符的方法很多，但处理后数据仍然为字符串类型，不满足要求。相关资料中也很少有提及如何处理这种情况的程序模板，尤其是处理这种数字位数不定，且带有小数点的模板更是少之又少，经过调试和检验，如下程序可以准确无误地将数据中的数字包括小数点，从带有任何附加符号的字符串中提取出来，完全还原数据的本来面目，并且能够直接参与到数学运算中去。具体如下：
Dim p&,arr ' 定义两个变体型变量
Set asheet=ActiveWorkbook.Sheets(3)
arr=Range(asheet.Cells(3,3),asheet.Cells(2+ds,3))'定义arr指向的单元格范围。
With CreateObject("VBSCRIPT.REGEXP")'建立正则表达式。
.Global=True'设置全程性质。
.Pattern="[^.0-9,]" '设置搜索模式，只搜索除小数点及0-9以外的字符。
For p=1 To UBound(arr)
arr(p,1)=.Replace(arr(p,1),"")'将搜索到的字符替换为空。
Next
End With
Range(asheet.Cells(3,3),asheet.Cells(2+ds,3))=arr'返回替换后的数值。
在上述程序中，Vbscript.Regexp对象可以提供简单的正则表达式功能，Global、Pattern为Regexp对象的属性，Global属性的值为True表示搜索应用于整个字符串，否则为False。Pattern属性设置或返回被搜索的正则字符串表达式，它包含对应表格中要搜索的正则表达式字符，[^m-z]表示否定的字符区间，与不在m-z区间的字符匹配。经过上述的方法替换后，所有单元格数据都可以成功转型。
3 结语[3]
本程序亮点在于实现繁琐数据的超快捷综合功能，在实际工作中可表现出了非常高的便捷性和实用性，便于广泛的推广和应用，使用者可以在此基础上根据需要利用Excel的标准功能进行任何形式的统计分析，也可以继续利用VBA编写宏实现特定的分析功能，可扩展性和灵活性都非常高，便于后期的持续开发和研究。
参考文献：
[1] 刘铭.基于VBA对Excel 2007二次开发应用研究[J].现代电子技术,2010.10:55-57.
[2] 魏胜利.VBA在Office的办公自动化(OA)及数据通信中的应用[J].办公自动化,2010.02:53-54.
[3] 刘辉.基于VBA的学生管理信息系统的开发设计[J].电脑编程技巧与维护,2009.16:52-53.
注：本文中所涉及到的图表、注解、公式等内容请以PDF格式阅读原文
