用Excel自动获取网页数据
利用Excle可以自动获取网页数据,譬如从网页中自动获取基金净值数据,取得所关注基金的净值,列出当日涨幅最高的基金。本文用简单的W eb查询结合Excel公式的方法完成上述需求,当然也可以用VBA编写功能更加丰富的自动查询网页数据工具。
一:
1.创建和编辑Web 查询
Excel2003
身份证号码的数字代表什么数据- 导入外部数据- 新建web 查询- 在“地址”栏输入URL 地址( 这里我选用的基金净值网址是:fund.eastmon ey/fund.html ) - 转到- 选择表格中的箭头符号- 导入- 外部数据区域属性中可以选择打开工作簿时自动刷新
选择了打开工作簿时自动刷新选项后,每次打开文档便会自动从指定网页中获取数据。Excel2007
数据- 自网站- 在“地址”栏输入URL 地址- 转到... 后面与 excel2003 设置方法一致2. 列出所关注基金的净值
这一步就非常简单了,用 vlookup在更新的表格中查询所关注的基金净值显示在汇总页中3. 列出当日涨幅最高的基金
基金净值的涨幅数据在“基金净值”sheet的K 列,用函数=MA TCH(MAX(基金净
值!K:K),基金净值!K:K,0) 取得涨幅最大值所在的行。用函数= INDEX(基金净值!1:65536,汇总!H6,4) 取得涨幅最大值对应的单元格(基金名),其中“汇总!H6”为计算得到的最大值所在的行。
二:
使用VB实现E xcel自动获取外部数据
Excel表格生成和公式设置十分强大便利,是一个强有力的信息分析与处理工具。VisualBasic 是一套可视化、面向对象、事件驱动方式的结构化高级程序设计语言,正成为高效率的Window s应用程序开发工具。由于微软的努力,VisualBasic应用程序版可作为一种通用宏语言被所有微软可编程应用软件共享。
Excel面始之初带有表格处理类软件中功能最强的宏语言,通过单击“工具”菜单中的“宏”,选择宏名来调用宏过程。随后发展至Vi sual Basic for Applica tion专用版,可制作按钮、复选框、单选钮等控件,赋控件以宏名,单击控件运行宏,事件驱动方式就Click(单击)一种。新近推出的Of fice97套件中的
Ex cel97,在“工具”菜单中选择“宏”后,就会发现增加了“VisualBasic编辑器”功能。运用这个新增功能,就完全与Vis ual Basic编程无异了。在菜单栏上单击鼠标右键,选择弹出式菜单中的“控件工具箱”,在“控件工具箱”工具条上,单击待添加的控件按钮,在工作表中将控件拖曳到所需位置和大小,单击鼠标右键选中“属性”设置控件属性后,双击控件就会出现Visua l Basic编辑器。选择该控件的一个事件如Cl ick或Ch ange,编写程序。在工作表中操作该控件,如鼠标单击、键入字符等,则触发相应事件,执行相应程序。
笔者在Exce l97平台,采用Visua l Basic应用程序版开发了一套“通用报表分析系统”(界面如图1)。该系统用于拥有众多子公司的母公司的每月财务报表合并汇总。所有子公司的统计报表如资产负债表、损益表是由Fo xBase编制的财务软件生成的dbf文件,取名为
ATV001xx.dbf----xx月份资产负债表,ATV002x x.dbf----xx月份损益表等。一个子公司的所有dbf文件放在一个单独的目录中,如C:\T\palm1,C:\T\palm2等。母公司每月份生成的汇总报表为TTTyy mm.xls(yy----年份,mm----月份),它有“资产负债表”、“损益表”等若干工作表组成。每张工作表是由所有子公司相应的dbf文件的相应项目的数据相加而成。只要将dbf文件逐一转化到TTTyym m.xls中去,很容易利用Ex cel的公式设置功能生成母公司的每张汇总报表。
校园安全手抄报资料这套系统的关键在于如何将所有dbf文件转换到同一个Excel工作簿中。直接通过“文件”菜单中的“打开”
项, 选择文件类型为dBase文件(*.dbf), 可将dbf文件转换到Exc el工作簿中,但这工作簿只存转换而来的一张工作表,其他表都自动关闭了。另外,通过“工具”菜单中的“向导”,选择“文件转换”后,只是将一系列d bf文件转换为一系列xl s文件而已。于是采用建立O DBC数据源获取外部数据的办法, 将dbf文件逐一转换到一个Excel工作簿内, 且用Visua l Basic for Applica tion将转换过程自动化。只要按一下图1中的“生成报表”按钮, 就能完成所有d bf 文件的转换, 且利用Exce l公式自动计算功能完成所有报表的汇总计算。按“显示报表”按钮,选择表名,可以浏览报表数据。
具体的方法是:
1、建立ODBC数据源
(1) 打开“数据”菜单, 选择“获取外部数据”,然后单击“新建查询”;
(2) 在“选择数据源”对话框中, 双击“<;新数据源>”;
(3) 出现“创建新数据源”对话框,输入数据源名称, 选择驱动程序如Micros oft dBase
Driver(*.dbf), 单击“连接”;
(4) 在“ODBCdBase安装”对话框中, 单击“使用当前工作目录”前的复选框, 去掉缺省( , 单击“选定目录(s)”,选择子公司存放dbf文件的目录如C:\T\palm1, 连按“确定”;
(5) 当出现Micr osoftQuary对话框时, 单击“关闭”,退出。不要理会出现的警示信息,因为此时只需建立数据源, 并不需要用Mi crosof t Query查询数据;
(6) 重复上述步骤,在(4)中改换另一家子公司的目录,就为另一家子公司建立一个数据源。必须建立所有子公司的数据源。
2、手动获取外部数据
(1) 单击“数据”,选取“获取外部数据”,单击“新建查询”;国庆祝福语祖国的话
(2) 出现“选取数据源”对话框,点中“使用查询向导创建/ 编辑查询”前的复选框,然后双击数据源名,如palm1;
(3) 在“查询向导——选择列”对话框中选择一个查询表名,单击> 键,“查询中用到的列”框内会出现表中所有列名,单击“下一步”;
(4) 出现“查询向导——过滤数据”,单击“下一步”;
(5) 出现“查询向导——排序顺序”,单击“下一步”;
(6) 出现“查询向导——完成”,点中“将数据返回Mi crosof tExcel”前的单选钮,单击“完成”;
(7) 出现“将外部数据返回到Excel”对话框,选中“新建工作表”,按“确定”;
(8) 在建立查询的工作簿内新建工作表,并放入转换好的数据。这样就将一个dbf 文件转换好了。
(9)重复上述过程,所有子公司的d bf文件转换到同一个工作簿中。
3、使用VB实现E xcel自动获取外部数据
(1) 进行手动获取外部数据(1)步骤前,单击“工具”菜单中的“宏”,选择“录制新宏”,在“宏名”的编辑框中键入宏名dbft oxls,按“确定”键;青海旅游
(2) 完成手动获取外部数据(1)-(8)步骤;
(3) 单击“工具”菜单中的“宏”,选择“停止录制”。这样就将获取外部数据的过程记录为宏。
(4) 编辑dbfto xls宏,加以修改,使它作为Vis ual Basic模块表中的一个子程序,并设置调用参数。
提供的程序如下:
设置初值
Const apppath = "c:\my documen ts\palmxls\"
Const modulef ile = apppath + "module.xls"
Const statics pre = "TTT"
Const dbfpre= "A TV00"
`调用dbfto xls的模块
Private Sub Cmdgene rateta ble_Cl ick()
Dim statics file As String
Dim s1 As String
Dim s2 As String
Dim s3 As String
Dim idyes As Integer
Dim dbfstri ng As String
On Error GoTo errhand ler1
idyes = 6
s1 = txtyear.Text
s1 = Mid(s1, 3, 2)
s2 = txtmont h.Text
If Len(s2) = 1 Then
s2 = "0" + s2
End If
statics file = apppath + statics pre + s1 + s2 + ".xls"
If FileLen(statics file) > 0 Then
choice= MsgBox("该年月报表已存在,是否重新生成?", vbYesNo + vbExcla mation +
vbDefau ltButt on1, "")
If choice= idyes Then
Workboo ks.Open FileNam e:=statics file
For i = 0 To company num - 1
For j = 0 To tablenu m - 1
dbfstri ng = dbfpre+ Trim(Str$(j + 1)) + s2
sqlstri ng = sqlstri ngfunc(dbfstri ng, fieldli st(), tablefi eldnum(j))
Call dbftoxl s(s(i, j), sqlstri ng)
Next j
Next i
ActiveW orkboo k.Save
ActiveW orkboo k.Close
End If
End If
Exit Sub
errhand ler1:
SelectCase Err
Case 53
Workboo ks.Open FileNam e:=modulef ile
s3 = s1 + "年" + s2 + "月"
Sheets("资产负债表").Range("e4").Formula R1C1 = "'" + s3
ActiveW orkboo k.SaveAsFileNam e:=statics file, FileFor mat _
:
=xlNorma l, Passwor d:="", WriteRe sPassw ord:="", ReadOnl yRecom mended:= _ False, CreateB ackup:=False
For i = 0 To company num - 1
For j = 0 To tablenu m - 1
dbfstri ng = dbfpre+ Trim(Str$(j + 1)) + s2
sqlstri ng = sqlstri ngfunc(dbfstri ng, fieldli st(), tablefi eldnum(j))
Call dbftoxl s(s(i, j), sqlstri ng)
Next j
Next i
ActiveW orkboo k.Save
ActiveW orkboo k.Close
End Select
End Sub
`dbftoxl s子程序
Sub dbftoxl s(actives heetna me, sqlstri ng)
Sheets(actives heetna me).Activat e
Cells.Select
Selecti on.Clear
Range("a1").Select
With ActiveS heet.QueryTa bles.Add(Connect ion:=Array(Array( _买房子的注意事项
"ODBC;Collati ngSequ ence=ASCII;DBQ=C:\T\palm1;Default Dir=C:\T
\palm1;Deleted=1;Driver={Microso ft dBase Driver(*.dbf)};DriverI d=533;FIL" _ ), Array( _
"=dBase III;Implici tCommi tSync=Yes;MaxBuff erSize=512;MaxScan Rows=
8;PageTim eout=600;SafeTra nsacti ons=0;Statist ics=0;Threads=3;Use" _
), Array("rCommit Sync=Yes;")), Destina tion:=Range("A1"))三线表怎么做
.Sql = Array( sqlstri ng)
.FieldNa mes = True
.Refresh Style= xlInser tDelet eCells
.RowNumb ers = False
.FillAdj acentF ormula s = False
.Refresh OnFile Open = False
.HasAuto Format = True
.Backgro undQue ry = True
.TablesO nlyFro mHTML= True
.
Refresh Backgro undQue ry:=False
.SavePas sword= True
.SaveDat a = True
End With
End Sub
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。
发表评论