|
excel表格生成和公式设置十分强大便利，是一个强有力的信息分析与处理工具。visual basic是一套可视化、面向对象、事件驱动方式的结构化高级程序设计语言，正成为高效率的windows应用程序开发工具。由于微软的努力，visual basic应用程序版可作为一种通用宏语言被所有微软可编程应用软件共享。
excel面始之初带有表格处理类软件中功能最强的宏语言，通过单击“工具”菜单中的“宏”，选择宏名来调用宏过程。随后发展至visual basic for application专用版，可制作按钮、复选框、单选钮等控件，赋控件以宏名，单击控件运行宏，事件驱动方式就click（单击）一种。新近推出的office97套件中的excel97,在“工具”菜单中选择“宏”后，就会发现增加了“visual basic编辑器”功能。运用这个新增功能，就完全与visual basic编程无异了。在菜单栏上单击鼠标右键，选择弹出式菜单中的“控件工具箱”，在“控件工具箱”工具条上，单击待添加的控件按钮，在工作表中将控件拖曳到所需位置和大小，单击鼠标右键选中“属性”设置控件属性后，双击控件就会出现visual basic编辑器。选择该控件的一个事件如click或change,编写程序。在工作表中操作该控件，如鼠标单击、键入字符等，则触发相应事件，执行相应程序。
图1
笔者在excel97平台，采用visual basic应用程序版开发了一套“通用报表分析系统”(界面如图1)。该系统用于拥有众多子公司的母公司的每月财务报表合并汇总。所有子公司的统计报表如资产负债表、损益表是由foxbase编制的财务软件生成的dbf文件，取名为atv001xx.dbf----xx月份资产负债表，atv002xx.dbf----xx月份损益表等。一个子公司的所有dbf文件放在一个单独的目录中，如c:\t\palm1，c:\t\palm2等。母公司每月份生成的汇总报表为tttyymm.xls(yy----年份，mm----月份)，它有“资产负债表”、“损益表”等若干工作表组成。每张工作表是由所有子公司相应的dbf文件的相应项目的数据相加而成。只要将dbf文件逐一转化到tttyymm.xls中去，很容易利用excel的公式设置功能生成母公司的每张汇总报表。
这套系统的关键在于如何将所有dbf文件转换到同一个excel工作簿中。直接通过“文件”菜单中的“打开”项, 选择文件类型为dbase文件（*.dbf）, 可将dbf文件转换到excel工作簿中,但这工作簿只存转换而来的一张工作表，其他表都自动关闭了。另外,通过“工具”菜单中的“向导”，选择“文件转换”后， 只是将一系列dbf文件转换为一系列xls文件而已。于是采用建立odbc数据源获取外部数据的办法, 将dbf文件逐一转换到一个excel工作簿内, 且用visual basic for application将转换过程自动化。只要按一下图1中的“生成报表”按钮, 就能完成所有dbf 文件的转换, 且利用excel公式自动计算功能完成所有报表的汇总计算。按“显示报表”按钮，选择表名，可以浏览报表数据。
具体的方法是：
一、 建立odbc数据源
(1) 打开“数据”菜单, 选择“获取外部数据”, 然后单击“新建查询”;
(2) 在“选择数据源”对话框中, 双击“<新数据源>”;
(3) 出现“创建新数据源”对话框,输入数据源名称, 选择驱动程序如microsoft dbase driver(*.dbf), 单击“连接”;
(4) 在“odbc dbase安装”对话框中, 单击“使用当前工作目录”前的复选框, 去掉缺省( , 单击“选定目录(s)”, 选择子公司存放dbf文件的目录如c:\t\palm1, 连按“确定”；
(5) 当出现microsoft quary对话框时, 单击“关闭”, 退出。不要理会出现的警示信息，因为此时只需建立数据源, 并不需要用microsoft query查询数据;
(6) 重复上述步骤, 在(4)中改换另一家子公司的目录, 就为另一家子公司建立一个数据源。必须建立所有子公司的数据源。
二、手动获取外部数据
(1) 单击“数据”，选取“获取外部数据”，单击“新建查询”；
(2) 出现“选取数据源”对话框，点中“使用查询向导创建/ 编辑查询”前的复选框，然后双击数据源名，如palm1;
(3) 在“查询向导——选择列”对话框中选择一个查询表名，单击 > 键，“查询中用到的列”框内会出现表中所有列名，单击“下一步”；
(4) 出现“查询向导——过滤数据”，单击“下一步”；
(5) 出现“查询向导——排序顺序”，单击“下一步”；
(6) 出现“查询向导——完成”，点中“将数据返回microsoft excel”前的单选钮，单击“完成”；
(7) 出现“将外部数据返回到excel”对话框，选中“新建工作表”，按“确定”；
(8) 在建立查询的工作簿内新建工作表，并放入转换好的数据。这样就将一个 dbf 文件转换好了。
(9)重复上述过程，所有子公司的dbf文件转换到同一个工作簿中。
三、 使用vb实现excel自动获取外部数据
(1) 进行手动获取外部数据(1)步骤前，单击“工具”菜单中的“宏”，选择“录制新宏”，在“宏名”的编辑框中键入宏名dbftoxls,按“确定”键；
(2) 完成手动获取外部数据(1)-(8)步骤；
(3) 单击“工具”菜单中的“宏”，选择“停止录制”。这样就将获取外部数据的过程记录为宏。
(4) 编辑dbftoxls宏，加以修改，使它作为visual basic模块表中的一个子程序，并设置调用参数。
提供的程序如下：
`设置初值
const apppath = "c:\my documents\palmxls\"
const modulefile = apppath + "module.xls"
const staticspre = "ttt"
const dbfpre = "atv00"
`调用dbftoxls的模块
private sub cmdgeneratetable_click()
dim staticsfile as string
dim s1 as string
dim s2 as string
dim s3 as string
dim idyes as integer
dim dbfstring as string
on error goto errhandler1
idyes = 6
s1 = txtyear.text
s1 = mid(s1, 3, 2)
s2 = txtmonth.text
if len(s2) = 1 then
s2 = "0" + s2
end if
staticsfile = apppath + staticspre + s1 + s2 + ".xls"
if filelen(staticsfile) > 0 then
choice = msgbox("该年月报表已存在，是否重新生成？", vbyesno + vbexclamation + vbdefaultbutton1, "")
if choice = idyes then
workbooks.open filename:=staticsfile
for i = 0 to companynum - 1
for j = 0 to tablenum - 1
dbfstring = dbfpre + trim(str$(j + 1)) + s2
sqlstring = sqlstringfunc(dbfstring, fieldlist(), tablefieldnum(j))
call dbftoxls(s(i, j), sqlstring)
next j
next i
activeworkbook.save
activeworkbook.close
end if
end if
exit sub
errhandler1:
select case err
case 53
workbooks.open filename:=modulefile
s3 = s1 + "年" + s2 + "月"
sheets("资产负债表").range("e4").formular1c1 = "'" + s3
activeworkbook.saveas filename:=staticsfile, fileformat _
:=xlnormal, password:="", writerespassword:="", readonlyrecommended:= _
false, createbackup:=false
for i = 0 to companynum - 1
for j = 0 to tablenum - 1
dbfstring = dbfpre + trim(str$(j + 1)) + s2
sqlstring = sqlstringfunc(dbfstring, fieldlist(), tablefieldnum(j))
call dbftoxls(s(i, j), sqlstring)
next j
next i
activeworkbook.save
activeworkbook.close
end select
end sub
`dbftoxls子程序
sub dbftoxls(activesheetname, sqlstring)
sheets(activesheetname).activate
cells.select
selection.clear
range("a1").select
with activesheet.querytables.add(connection:=array(array( _
"odbc;collatingsequence=ascii;dbq=c:\t\palm1;defaultdir=c:\t
\palm1;deleted=1;driver={microsoft dbase driver (*.dbf)};driverid=533;fil" _
), array( _
"=dbase iii;implicitcommitsync=yes;maxbuffersize=512;maxscanrows=
8;pagetimeout=600;safetransactions=0;statistics=0;threads=3;use" _
), array("rcommitsync=yes;")), destination:=range("a1"))
.sql = array( sqlstring)
.fieldnames = true
.refreshstyle = xlinsertdeletecells
.rownumbers = false
.filladjacentformulas = false
.refreshonfileopen = false
.hasautoformat = true
.backgroundquery = true
.tablesonlyfromhtml = true
.refresh backgroundquery:=false
.savepassword = true
.savedata = true
end with
end sub |
