用Excel自动获取网页数据.doc

用Excel自动获取网页数据利用Excle可以自动获取网页数据，譬如从网页中自动获取基金净值数据，取得所关注基金的净值，列出当日涨幅最高的基金。本文用简单的Web查询结合Excel公式的方法完成上述需求，当然也可以用 VBA 编写功能更加丰富的自动查询网页数据工具。一：1. 创建和编辑 Web 查询Excel2003数据 - 导入外部数据 - 新建 web 查询 - 在“地址”栏输入 URL 地址 ( 这里我选用的基金净值网址是：/fund.html ) - 转到 - 选择表格中的箭头符号 - 导入 - 外部数据区域属性中可以选择打开工作簿时自动刷新选择了打开工作簿时自动刷新选项后，每次打开文档便会自动从指定网页中获取数据。Excel2007数据 - 自网站 - 在“地址”栏输入 URL 地址 - 转到 . 后面与 excel2003 设置方法一致2. 列出所关注基金的净值这一步就非常简单了，用 vlookup 在更新的表格中查询所关注的基金净值显示在汇总页中3. 列出当日涨幅最高的基金基金净值的涨幅数据在“ 基金净值 ” sheet 的 K 列， 用函数 =MATCH(MAX(基金净值!K:K),基金净值!K:K,0) 取得涨幅最大值所在的行。 用函数 = INDEX(基金净值!1:65536,汇总!H6,4) 取得涨幅最大值对应的单元格（基金名），其中“ 汇总!H6” 为计算得到的最大值所在的行。二：使用VB实现Excel自动获取外部数据Excel表格生成和公式设置十分强大便利，是一个强有力的信息分析与处理工具。Visual Basic是一套可视化、面向对象、事件驱动方式的结构化高级程序设计语言，正成为高效率的Windows应用程序开发工具。由于微软的努力，Visual Basic应用程序版可作为一种通用宏语言被所有微软可编程应用软件共享。Excel面始之初带有表格处理类软件中功能最强的宏语言，通过单击“工具”菜单中的“宏”，选择宏名来调用宏过程。随后发展至Visual Basic for Application专用版，可制作按钮、复选框、单选钮等控件，赋控件以宏名，单击控件运行宏，事件驱动方式就Click（单击）一种。新近推出的Office97套件中的Excel97,在“工具”菜单中选择“宏”后，就会发现增加了“Visual Basic编辑器”功能。运用这个新增功能，就完全与Visual Basic编程无异了。在菜单栏上单击鼠标右键，选择弹出式菜单中的“控件工具箱”，在“控件工具箱”工具条上，单击待添加的控件按钮，在工作表中将控件拖曳到所需位置和大小，单击鼠标右键选中“属性”设置控件属性后，双击控件就会出现Visual Basic编辑器。选择该控件的一个事件如Click或Change,编写程序。在工作表中操作该控件，如鼠标单击、键入字符等，则触发相应事件，执行相应程序。笔者在Excel97平台，采用Visual Basic应用程序版开发了一套“通用报表分析系统”(界面如图1)。该系统用于拥有众多子公司的母公司的每月财务报表合并汇总。所有子公司的统计报表如资产负债表、损益表是由FoxBase编制的财务软件生成的dbf文件，取名为ATV001xx.dbf-xx月份资产负债表，ATV002xx.dbf-xx月份损益表等。一个子公司的所有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公式自动计算功能完成所有报表的汇总计算。按“显示报表”按钮，选择表名，可以浏览报表数据。具体的方法是：1、 建立ODBC数据源(1) 打开“数据”菜单, 选择“获取外部数据”, 然后单击“新建查询”;(2) 在“选择数据源”对话框中, 双击“”;(3) 出现“创建新数据源”对话框,输入数据源名称, 选择驱动程序如Microsoft dBase Driver(*.dbf), 单击“连接”;(4) 在“ODBC dBase安装”对话框中, 单击“使用当前工作目录”前的复选框, 去掉缺省( , 单击“选定目录(s)”, 选择子公司存放dbf文件的目录如C:Tpalm1, 连按“确定”；(5) 当出现Microsoft Quary对话框时, 单击“关闭”, 退出。不要理会出现的警示信息，因为此时只需建立数据源, 并不需要用Microsoft Query查询数据;(6) 重复上述步骤, 在(4)中改换另一家子公司的目录, 就为另一家子公司建立一个数据源。必须建立所有子公司的数据源。2、手动获取外部数据(1) 单击“数据”，选取“获取外部数据”，单击“新建查询”；(2) 出现“选取数据源”对话框，点中“使用查询向导创建/ 编辑查询”前的复选框，然后双击数据源名，如palm1;(3) 在“查询向导选择列”对话框中选择一个查询表名，单击 键，“查询中用到的列”框内会出现表中所有列名，单击“下一步”；(4) 出现“查询向导过滤数据”，单击“下一步”；(5) 出现“查询向导排序顺序”，单击“下一步”；(6) 出现“查询向导完成”，点中“将数据返回Microsoft Excel”前的单选钮，单击“完成”；(7) 出现“将外部数据返回到Excel”对话框，选中“新建工作表”，按“确定”；(8) 在建立查询的工作簿内新建工作表，并放入转换好的数据。这样就将一个 dbf 文件转换好了。(9)重复上述过程，所有子公司的dbf文件转换到同一个工作簿中。3、 使用VB实现Excel自动获取外部数据(1) 进行手动获取外部数据(1)步骤前，单击“工具”菜单中的“宏”，选择“录制新宏”，在“宏名”的编辑框中键入宏名dbftoxls,按“确定”键；(2) 完成手动获取外部数据(1)-(8)步骤；(3) 单击“工具”菜单中的“宏”，选择“停止录制”。这样就将获取外部数据的过程记录为宏。(4) 编辑dbftoxls宏，加以修改，使它作为Visual Basic模块表中的一个子程序，并设置调用参数。提供的程序如下：设置初值Const apppath = c:my documentspalmxlsConst modulefile = apppath + module.xlsConst staticspre = TTTConst dbfpre = ATV00调用dbftoxls的模块Private Sub Cmdgeneratetable_Click()Dim staticsfile As StringDim s1 As StringDim s2 As StringDim s3 As StringDim idyes As IntegerDim dbfstring As StringOn Error GoTo errhandler1idyes = 6s1 = txtyear.Texts1 = Mid(s1, 3, 2)s2 = txtmonth.TextIf Len(s2) = 1 Thens2 = 0 + s2End Ifstaticsfile = apppath + staticspre + s1 + s2 + .xlsIf FileLen(staticsfile) 0 Thenchoice = MsgBox(该年月报表已存在，是否重新生成？, vbYesNo + vbExclamation + vbDefaultButton1, )If choice = idyes ThenWorkbooks.Open FileName:=staticsfileFor i = 0 To companynum - 1For j = 0 To tablenum - 1dbfstring = dbfpre + Trim(Str$(j + 1) + s2sqlstring = sqlstringfunc(dbfstring, fieldlist(), tablefieldnum(j)Call dbftoxls(s(i, j), sqlstring)Next jNext iActiveWorkbook.SaveActiveWorkbook.CloseEnd IfEnd IfExit Suberrhandler1:Select Case ErrCase 53Workbooks.Open FileName:=modulefiles3 = s1 + 年 + s2 + 月Sheets(资产负债表).Range(e4).FormulaR1C1 = + s3ActiveWorkbook.SaveAs FileName:=staticsfile, FileFormat _:=xlNormal, Password:=, WriteResPassword:=, ReadOnlyRecommended:= _False, CreateBackup:=FalseFor i = 0 To companynum - 1For j = 0 To tablenum - 1dbfstring = dbfpre + Trim(Str$(j + 1) + s2sqlstring = sqlstringfunc(dbfstring, fieldlist(), tablefieldnum(j)Call dbftoxls(s(i, j), sqlstring)Next jNext iActiveWorkbook.SaveActiveWorkbook.CloseEnd SelectEnd Sub dbftoxls子程序Sub dbftoxls(activesheetname, sqlstring)Sheets(activesheetname).ActivateCells.SelectSelection.ClearRange(a1).SelectWith ActiveSheet.QueryTables.Add(Connection:=Array(Array( _ODBC;CollatingSequence=ASCII;DBQ=C:Tpalm1;DefaultDir=C:Tpalm1;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.