Excel自动猎取外部数据.doc

采用Visual Basic应用程序版开发了一套“通用报表分析系统”(界面如图1)。该系统用于拥有众多子公司的母公司的每月财务报表合并汇总。所有子公司的统计报表如资产负债表、损益表是由FoxBase编制的财务软件生成的dbf文件，取名为ATV001xx.dbfxx月份资产负债表，ATV002xx.dbfxx月份损益表等。一个子公司的所有dbf文件放在一个单独的目录中，如C:Tpalm1，C:Tpalm2等。母公司每月份生成的汇总报表为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:Tpalm1, 连按“确定”； (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 documentspalmxls 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