利用VBA在Excel和中完成复杂的数据引用

1、利用VBA在Excel和Word中完成复杂的数据引用ComplicatedDataReferenceBasedonVBAinExcelandWordWANGGui,SHIXian(ErtanHydropowerDevelopmentCompany,Ltd.ErtanHydropowerStation,Panzhihua617000,China)：Inthewayofprograming,howtocross-referenceandintegratedatainthecommonlyusedExcelandWordwithVBAlanguagesinOffice-basedworkitemwi

2、llbediscussed,andanalyzetheimportantanddifficult,thenmakeachievementthatcomplicated,repetitivedailyworkrunautomaticallyandimprovetheefficiencyofwork.在日常的Office办公中，经常出现标准命令无法满足用户需求或者标准命令的操作过程过于繁琐等方面的实际问题，VBA作为嵌入在Office套件中的程序开发语言，可以使Office应用程序得到扩展或者将Office应用程序及数据集成到其它应用程序中，解决用户的诸多难题，使得Office更加实用和智能化，从

3、而提高用户的工作效率。用户无论是在Excel中还是在Word中，建立和管理VBA都使用统一的方法和标准。Application是Excel、Word对象模型中最高级别的对象，表示Excel、Word程序自身1。Workbooks、Worksheet等又为Application对象，Excel应用程序在后台运行时，可以通过他们来访问具体的工作表等。WordVBA也提供了很多对象，在它的Application对象中有个Documents集合，其中包含每个打开文档的Document对象，利用这个对象可以访问任何打开的文档。Tables对象用来表示文档中的一个表格，Cell对象表示表格中的一个单元格2

4、。1 问题描述某公司在每个月结束之后都要对该月每天以及全月的重要的生产数据进行统计和分析，所需数据格式样表如表1所示。该问题的复杂性在于表1中的数据来自不同的Excel报表和Word报表中，数据文件的结构如图1所示。由于该工作涉及文档繁多，数据量庞大，每次依靠人工抄写和统计都耗费大量的人力和时间，工作繁琐重复，且经常难以保证数据的正确性，本文就针对这个复杂问题，讨论在Office中利用VBA编程如何实现自动提取和生成所有数据的功能。2 VBA程序2.1 程序准备由上可知，我们需要做的是从Excel的角度用VBA程序来进行数据综合。要顺利读取各种所需要的数据，必须先做好一些准备工作：准备一定义变

5、量Dimstrdir,strflAsString'定义地址变量为字符串变量。DimiAsInteger'定义将用到的循环变量。DimaxlAppAsExcel.Application'将Axlapp指向Excel应用程序本身。DimaxlBookAsExcel.Workbook'将axlBook定义为工作薄类型。Dimsheet,asheetAsExcel.Worksheet'将sheet,asheet定义为工作表类型。由于涉及到的工作薄、工作表、数据量巨大，所以我们必须要定义足够的变量来满足程序流程的要求，这将使得后续的程序语言组织起来较为灵活，提取数

6、据的方法也可以多样。准备二计算当月的天数Dimy,m,d,dsAsIntegerSetasheet=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。Fori=1Tods-1asheet.Cells(3+i,1)=asheet.Cells(3,1)+i'在第一列的中填入日期Next单元格

7、cell(3,1)是第一个日期所在位置，如表1所示，上述程序通过year、month、day函数从该单元格中分别提取年、月、日的数据。DateSerial函数可以返回表示已指定年月日的Date值，DateSerial(y,m+1,1)的返回值即为次月1日date值，用它减去当月1日得date值即为当月的天数。For()Next循环的作用是在第一列中依次输出当月每天的日期值，如2011年2月，则日期一直到2011年2月28日。以上一段程序的功能是，用户可根据实际需要自定义需要分析的月份，只需要在cell(3,1)中输入起始日期，如2011/02/01，程序就会自动识别日期，计算当月天数，并将日期

8、依次写入写入第一列中。在后续的编程中，日期将是一个非常重要的数据地址标识，而且也会大量用到ds这个值。准备三定义数据文件所在地址If(Right(ActiveWorkbook.Path,1)"")Thenstrdir=ActiveWorkbook.Path+""Elsestrdir=ActiveWorkbook.PathEndIf以上一段If()曰se判断语句将strdir地址变量指向本报表所在的文件夹地址，在提取数据时，只要把所有数据文档都放到本报表所在的文件夹地址下即可对其进行识别，这样有利于对文档的统一管理和操作。2.2 读取数据2.2.1 读取A

9、列数据A列数据从Excel表格中获得，其名称格式为“yyyy年mm月dd日报表”：SetaxlApp=NewExcel.Application'新建一个Excel.EXE的后台实例，专用来打开数据所在的工作薄。Fori=0Tods-1strfl=strdir+"Excel报表"+Format(ActiveWorkbook.Sheets(3).Cells(3,1)+i,"yyyy")+"年"+Format(ActiveWorkbook.Sheets(3).Cells(3,1)+i,"mm")+"月&

10、quot;+Format(ActiveWorkbook.Sheets(3).Cells(3,1)+i,"dd")+"日报表.xls"SetaxlBook=axlApp.Workbooks.Open(strfl)'打开报表。Setsheet=axlBook.Worksheets("今日生产日报")'指向数据所在工作表。ActiveWorkbook.Sheets(3).Cells(3+i,2)=sheet.Cells(38,27)'读取A列数据。axlBook.Close(False)'关闭打开的报表。Ne

11、xtFormat()在对时间进行格式化输出时的意义在于返回一定格式的字符串时间量，Format(datetime,"#")表示将datetime以#的格式输出。利用一个For()Next循环，随着整型变量i的变化，便可以将strfl这个变量指向与第一列日期相吻合的生产日报表。axlApp.Workbooks.Open()是一个将工作薄打开的常用语句格式，并将sheet指向了每天的生产日报中名为“今日生产日报”的工作表，然后将对应单元格的数据提取到当前的数据报表。这里axlBook.Close(False)是一个很重要的语句，它作用在于将打开的工作薄关闭，用于防止打开的工作薄

12、过多造成程序积压，最终导致程序运行缓慢或者电脑死机等现象。总结以上方法，主要以日期为名称标识，按照“文件夹一文件名一工作薄一工作表一单元格”的顺序依次找到所需要的数据，然后逐个提取并在目标报表的指定单元格输出，完成繁琐、复杂但有规律的数据提取功能。2.3 提取B列数据B列数据来源于“strdirWord报表生产日报yyyymmdd.doc”DimmmWordAsDocumedt定义一个Word的对象类型。DimappAsWord.Application'将app指向Word应用程序。Setapp=CreateObject("Word.application")新建一

13、个Word.EXE后台实例。Fori=0Tods-1n=y*10000+m*100+d+i'随着i的变化，求出对应“yyyymmdd格式的值。strfl=strdir+"Word报表"+"生产日报"+CStr(n)+”.doc"'将strfl指向所要访问的Word文档。SetmmWord=app.Documents.Open(strfl)打开这个文档。ActiveWorkbook.Sheets(3).Cells(3+i,3)=mmWord.Tables(2).Cell(3,2)'提取数据。mmWord.CloseFals

14、eNextSetmmWord=Nothingapp.Quit'关闭Word后台实例。CStr()函数用于将数字转换成字符串类型，便于与其他字符类型相加。非常值得一提的是，通常很多方法从Word中把数据提取到Excel中后，所输出的数据都为字符串类型，而且还会有多余的字符一同输出，如“372.64”输出为“372.64”，导致该数据不能直接与其他单元格的数据进行数学运算，所以需要一个附加程序来对其进行转换。仅删除多余字符的方法很多，但处理后数据仍然为字符串类型，不满足要求。相关资料中也很少有提及如何处理这种情况的程序模板，尤其是处理这种数字位数不定，且带有小数点的模板更是少之又少，经过调

15、试和检验，如下程序可以准确无误地将数据中的数字包括小数点，从带有任何附加符号的字符串中提取出来，完全还原数据的本来面目，并且能够直接参与到数学运算中去。具体如下：Dimp&,arr'定义两个变体型变量Setasheet=ActiveWorkbook.Sheets(3)arr=Range(asheet.Cells(3,3),asheet.Cells(2+ds,3)'定义arr指向的单元格范围。WithCreateObject("VBSCRIPT.REGEXP")'建立正则表达式。.Global=True'设置全程性质。.Pattern="八0-9,"'设置搜索模式，只搜索除小数点及0-9以外的字符。Forp=1ToUBound(arr)arr(p,1)=.Replace(arr(p,1),"")'将搜索到的字符替换为空。NextEndWithRange(asheet.Cells(3,3),asheet.Cells(2+ds,3)=arr'返回替换后的数值。在上述程序中，Vbscript.Regexp对象可以提供简单的正则表达式功能，Global、Pattern为Regexp对象的属性，GlobalFalse 。属性的值为True表示搜索应用于整个字符串，