ExcelVBACh16.docx
- 文档编号:23415310
- 上传时间:2023-05-16
- 格式:DOCX
- 页数:79
- 大小:1.68MB
ExcelVBACh16.docx
《ExcelVBACh16.docx》由会员分享,可在线阅读,更多相关《ExcelVBACh16.docx(79页珍藏版)》请在冰豆网上搜索。
ExcelVBACh16
学习微软 Excel 2002 VBA 编程和XML,ASP技术 - CH16
(2010-11-2817:
28:
38)
转载▼
分类:
Excel
第十六章Excel和因特网
作者:
JulittaKorol翻译:
TigerChenDec12’2005
我们这几年已经见证了因特网的飞速发展,这让我们获取散布在世界各地的无数的知识成为可能。
我们要感谢因特网,我们有了触手可及的数据库,涵盖各行各业和各个知识领域,词典和百科全书,股票查询,地图,天气预报和大量其它类型的储存在无数网页服务器上的信息。
很多情况下,从网页上获取的信息成为电脑程序进一步分析的主题。
由于其结构(行和列),Excel2002是使用这些网页表数据的极佳工具。
本章示范Excel2002里可用的内置工具如何从网页获取数据,或者发布Excel电子表格到网页。
在这里,你将发现许多VB语句,让你使用自己编写的VB过程来获取或者发布数据。
为了能够尽可能从本章受益,你应该链接到因特网(猫,电话线或网线,因特网服务器账号,以及网页浏览器,例如InternetExplorer5.0或更高版本)。
使用VBA创建超链接
和其它Office里的应用软件一样,Excel2002也可以让你在电子表格里创建超链接。
点击一个含有超链接的单元格之后,你可以打开一个网络服务器,局域网或者因特网上的文件。
你既可以直接在用户界面使用插入|超链接(图16-1)插入超链接,也可以通过使用VBA编程来插入超链接。
图16-1使用对话框在Excel里插入超链接
在VBA里面,每个超链接由Hyperlink对象表示,想要创建一个超链接到网页的话,可以使用Hyperlinks集合的Add方法。
该方法如下所示:
Expression.Hyperlinks.Add(Anchor,Address,[SubAddress],[ScreenTip],_[TextToDisplay])
方括号里的参数为可选的。
Expression指定你欲放置超链接的工作表或者单元格区域。
Anchor是要点击的对象,它既可以是一个Range(单元格区域)也可以是Shape(图片)对象。
Address指向当地网络或者网页。
SubAddress是Excel文件里的单元格名称。
ScreenTip让你在屏幕上显示一信息(当光标指向超链接时)。
TextToDisplay是为具体超链接在电子表格单元格里你要显示的友好名称。
我们来看看在工作表单元格里放置一超链接,这一切是如何通过VBA过程完成的。
点击该超链接时,将带你到雅虎网址。
1.打开一新工作簿
2.切换到VB编辑器屏幕,并插入一新模块到当前VBA工程
3.在代码窗口里,输入下面显示的FollowMe过程
4.激活第三步创建的过程(运行)
SubFollowMe()
DimmyRangeAsRange
SetmyRange=Sheets
(1).Range("A1")
myRange.Hyperlinks.Add_
Anchor:
=myRange,_
Address:
="_
ScreenTip:
="SearchYahoo",_
TextToDisplay:
="Clickhere"
EndSub
当你运行FollowMe过程后,第一个工作表的单元格A1将会包含一个超链接“Clickhere”,屏幕提示为“SearchYahoo”(参见图16-2)。
如果你现在链接到了因特网,点击该超链接就将打开你的网页浏览器,并且装载雅虎搜索引擎(图16-3)。
图16-2通过VBA过程将该超链接放置到了工作表里面
图16-3点击放置在工作表单元格上的超链接可以激活雅虎搜索引擎主页
如果你宁愿不在工作表里面放置超链接但是用户仍然可以从Excel工作表里面到达需要的因特网页,那么你可以使用FollowHyperlink方法。
该方法允许你打开需要的网页而不必在工作表里放置超链接。
该方法的格式如下所示:
Expression.FollowHyperlink(Address,[SubAddress],[NewWindow],_
[AddHistory],[ExtraInfo],[Method],[HeaderInfo])
同样,方括号里面的参数是可选的。
Expression返回一个Workbook(工作簿)对象。
Address是你要激活的网页地址。
SubAddress是超链接地址指向对象的一部分,可以是Excel工作表的单元格区域。
NewWindow表明你是否需要在新窗口里面显示该文件。
缺省设置为False。
下一个参数AddHistory,现在不需要使用。
它保留给将来使用。
ExtraInfo提供额外的信息,允许跳到文件或者网页上的具体位置。
例如,这里你可以明确你需要搜索的文本。
Method指明如何附加额外信息(ExtraInfo)。
它可以是下述常数之一:
msoMethodGet或者msoMethodPost。
当你使用msoMethodGet时,ExtraInfo是附加到URL地址的字符串。
当你使用msoMethodPost时,ExtraInfo则标志为一字符串或者字节数组。
最后一个可选参数HeaderInfo是个字符串,为HTTP请求明确标题信息。
其缺省值为空字符串。
我们来看看如何在VBA过程里使用FollowHyperlink方法。
本过程的目的是使用AltaVisa搜索引擎查找任何输入到工作表单元格里面的文本。
1.在VB编辑器窗口,激活工程浏览器窗口,并且双击Excel对象文件夹里的对象Sheet2(Sheet2)
2.在代码窗口里,输入Worksheet_BeforeDoubleClick过程,如下所示(回顾第十四章里关于在Excel里创建和使用事件过程的内容)
PrivateSubWorksheet_BeforeDoubleClick(ByValTargetAsRange,_
CancelAsBoolean)
DimstrSearchAsString
strSearch=Sheets
(2).Range("C3").Formula‘译者:
如有问题,本句改为…Sheets(“Sheet2”)…
IfTarget=Range("C3")Then
Cancel=True
ActiveWorkbook.FollowHyperlink_
Address:
="_
ExtraInfo:
="q="&strSearch,_
Method:
=msoMethodGet
EndIf
EndSub
3.现在切换到Excel应用软件窗口,并且在Sheet2的单元格C3里面输入任何你想要查找信息的词语或术语(参见图16-4)
4.确保你链接到因特网了
5.双击单元格C3。
该操作导致单元格C3里输入的文本会被发送到AltaVisa搜索引擎,屏幕上应该会显示查找特定条件主题的索引(图16-5)
图16-4Excel工作表可以用来将搜索参数发送到任何因特网搜索引擎(参见第二步的Worksheet_BeforeDoubleClick过程)
图16-5从Excel工作表打开的网页,列出了基于工作表单元格数据为标准找到的主题
使用VBA创建和发布HTML文件
和以前的版本一样,Excel2002也允许你将文件保存为HTML格式(HypertextMarkupLanguage超文本链接标示语言)。
该格式就像它的标准.xls格式一样,也能够为Excel所识别。
当你保存Excel文件为HTML格式时,你可以使用因特网浏览器例如IE或者NetscapeNavigator来查看电子表格了。
当你保存工作簿或其部分为HTML时,Excel保存原始工作簿的选项。
正由于此,用户既可以使用浏览器,也可以在Excel应用软件窗口内部来查看该文件。
Excel2002能够保存数据和图表为交互式网页。
在保存整个工作簿或者其一部分为HTML格式时,你可以选择创建一个静态的HTML文件,或者一个交互式的HTML文件并明确该文件的保存地址。
你可以直接将文件保存到网页,网络服务器或者当地电脑。
不需要使用任何东西,只要使用用户界面(文件|保存为网页),你就可以将整个工作簿或者工作簿的一部分保存到网页,以至于用户可以交互式地使用该信息,或者仅查看该数据。
如何将整个工作簿或者工作表(或者它们的成员之一——例如图表,数据透视表)放置到网页上的详细指导,可以在Excel在线帮助里找到。
因为本书是关于编程的,我们仅侧重于通过VBA代码执行这些任务的方法。
Excel2002的VBA对象库提供了发布工作表到网页的对象。
要使用编程的方法创建和发布Excel文件为HTML格式的话,你应该熟悉PublishObject对象和PublishObjects集合。
图16-6从文件菜单上点击“另存为网页”后就会弹出另存为对话框,允许保存工作簿为网页
图16-7点击另存为对话框(参见图16-6)上的“发布”按钮后,“发布为网页对话框就会弹出来
PublishObject代表保存为网页的工作表成员,而PublishObjects是某个特定工作簿中所有PublishObject对象的集合。
可以使用Add方法添加工作表成员到PublishObjects集合里。
该方法将会创建一个对象,代表保存为网页的特定工作表成员。
Add方法的格式如下:
expression.Add(SourceType,Filename,[Sheet],[Source],[HtmlType],_[DivID],[Title])
方括号里的参数是可选的。
Expression返回一对象,属于PublishObjects集合。
SourceType明确对象源使用下述常数之一:
常数
描述
xlSourceAutoFilter
自动筛选单元格区域
xlSourceChart
图表
xlSourcePivotTable
数据透视报告
xlSourcePrintArea
打印区域
xlSourceQuery
查询表(外部数据区域)
xlSourceRange
单元格区域
xlSourceSheet
整个工作表
Filename是明确对象源(SourceType)保存地址的字符串,它可以是URL(UnifiedResourceLocator统一的信息指定方法)或者当地或网络文件的路径。
Sheet是保存为网页的工作表名称。
Source是确定对象源的唯一名称。
该参数取决于SourceType参数。
当SourceType是xlSourceRange常数时,Source是单元格区域,或者单元格区域定义的名称。
如果参数SourceType是常数,例如xlSourceChart,xlSourcePivotTable或者xlSourceQuery,那么Source则指定图表名称,数据透视报告名称或者查询表名称。
HTMLType指明是否被选的工作表成员保存为交互式Office网页组成成员,或者静态的文本和图像。
它可以是下述常数之一:
常数
描述
xlHTMLCalc
使用电子表格成员。
该成员使得在因特网浏览器上直接查看,分析和计算电子表格数据成为可能。
该成员也允许你更改文字,单元格,行和列的格式
xlHTMLChart
使用图表成员。
该成员允许你在浏览器里创建交互式图表
xlHTMLList
使用数据透视表成员。
该成员允许你在网页浏览器上重新安排,筛选和汇总信息。
该成员也能够从电子表格或者数据库(例如Assess,SQL服务器或者OLAP服务器)里显示数据。
xlHTMLStatic(缺省值)
使用静态(非交互式)HTML,仅可查看。
发布在HTML文档上的数据不可更改。
注意:
OfficeWebComponents允许你在因特网浏览器上使用Excel分析功能。
DivID是HTMLDIV上唯一的识别符,用来识别网页上的项目。
Title是网页标题。
我们在学习如何从VBA过程里使用Add方法之前,需要学习如何使用PublishObject的Publish方法。
该方法将允许你发布某个特定文档的成员或者成员集合到网页上。
该方法比较简单,看上去如下所示:
expression.Publish([Create])
Expression是个表达式,返回PublishObject对象或者PublishObjects集合。
该可选的参数Create,仅和PublishObject一起使用。
如果HTML文件已经存在,那么,设置该参数为True以覆盖该文件;设置该参数为False以插入新项目到该文件结尾。
如果该文件并不存在的话,那么不管参数Create的值是什么,都会创建一个新HTML文件。
既然已经给你介绍了将Excel工作簿发布为HTML格式要用的VBA对象和方法,那么你可以开始编程了。
在接下来的练习里,你将创建两个VBA过程,第一个将创建和发布一个带有一个内嵌图表的Excel工作表为静态HTML;第二个过程将示范该相同的工作表如何能制作为一个交互式的网页。
1.创建一工作表和图表,如图16-8所示
2.保存该工作簿为PublishExample.xls
3.激活VB编辑器窗口,并且插入一个新模块到当前VBA工程
4.在代码窗口,输入如下所示的两个过程。
第一个过程PublishOnWeb将内嵌图表的工作表发布到网页上,作为一个静态的HTML。
第二个过程CreateHTMLFile则调用PublishOnWeb过程并且提供两个必要的参数:
你要发布的工作簿名称和数据将保存的位置HTML文件的名称。
'Theprocedurebelowwillpublishaworksheet
'withanembeddedchartasstaticHTML
SubPublishOnWeb(strSheetNameAsString,strFileNameAsString)
DimobjPubAsExcel.PublishObject
SetobjPub=ThisWorkbook.PublishObjects.Add(_
SourceType:
=xlSourceSheet,_
Filename:
=strFileName,Sheet:
=strSheetName,_
HtmlType:
=xlHtmlStatic,Title:
="CallsAnalysis")
objPub.PublishTrue
EndSub
SubCreateHTMLFile()
CallPublishOnWeb("HelpDesk","C:
\WorksheetWithChart.htm")
EndSub
5.输入完两个过程后,运行名为CreateHTMLFile的过程。
当该过程完成时,你将看到一个名为C:
\WorksheetWithChart.htm的新文件。
同时,也有一个名为WorksheetWithChart_files的文件夹,存储一些补偿文件。
6.在Windows文件浏览器里,双击第五步创建的文件C:
\WorksheetWithChart.htm。
该动作将导致发布的工作表出现在因特网浏览器里(参见图16-9)。
图16-8使用文件菜单里的另存为网页命令,或者从VBA过程,可以将类似这个带内嵌图表的工作表放置到网页上
图16-9发布为静态(非交互式)网页的Excel工作表
要交互式地发布如图16-8所示的内嵌图表的工作表的话,那么请按下面操作:
1.在VB编辑器窗口,插入新模块到当前VBA工程
2.在代码窗口,输入InterHTML过程,如下所示:
SubInterHTML()
DimstrSheetNameAsString
strSheetName="HelpDesk"
'ensurethatthechartisnotselected
Range("A1").Select
ActiveWorkbook.PublishObjects_
.Add(xlSourceChart,"C:
\Inter_WorksheetWithChart.htm",_
strSheetName,"Chart1",xlHtmlChart).Publish(True)
EndSub
3.运行你刚创建的过程
4.在Windows文件浏览器里,双击C:
\Inter_WorksheetWithChart.htm,这将导致因特网浏览器被激活并显示交互式图表(参见图16-10)。
该网页也包含一交互式工作表,为图表提供数据。
5.更改B列里的任何数值,并查看图表上的变化。
注意:
想要交互式地使用数据的话,你就必须在你的电脑上安装MicrosoftIE4.01或者更高版本,以及MicrosoftOfficeWebComponents。
图16-10交互式发布的Excel工作表可以在浏览器上直接修改
网络服务器——储存和打开工作簿
要使用VBA语句在一个网络服务器上保存和打开工作簿是可能的,然而,在你能够储存你的工作簿到网络服务器之前,必须在该服务器上运行FrontPageServerExtensions。
下述语句将工作簿保存到一网站:
ActiveWorkbook.SaveAs“http:
//www.yourWebS
下述语句打开储存在一网站上的工作簿:
Workbooks.Open(“http:
//www.yourWebS
网络查询
如果你计划从某个网页获取数据,并且在Excel里使用和分析它们,那么你可以打开Excel的数据菜单,并选择“导入外部数据”|“新建Web查询”。
网络查询允许你直接从网络获取数据到Excel里面。
将数据放置到工作表后,你可以使用Excel工具来进行数据分析。
使用网络查询,你可以导入工作表一个表格,一系列表格或者某具体网站包含的所有文本。
技巧16-1网络查询准备就绪
Excel2002带来了好些个内置网络查询。
它们安装在C:
\ProgramFiles\MicrosoftOffice\Office10\Queries文件夹里面,并且可以文件菜单上的打开命令来装载它们。
这些查询的名称为:
MSNMoneyCentralInvestorCurrencyRates.iqy
MSNMoneyCentralInvestorMajorIndicies.iqy
MSNMoneyCentralInvestorStockQuotes.iqy
如果你的C:
\ProgramFiles\MicrosoftOffice\Office10\Queries文件夹是空的,那么你需要更新当前的Excel安装(使用控制面板上的添加/删除程序对话框)并且注明你需要安装这些功能。
Figure16-11:
TDKcorporationstockquoteswereretrievedfromthewebusingthebuilt-inwebqueryMSNMoneyCentralInvestorStockQuotes.iqyaftertypingTDKinadialogboxdisplayeduponactivatingthisquery.
想要运行网络查询的话,你就必须链接到因特网上。
网络查询可以是静态的也可以是动态的。
静态查询总是返回相同的数据,而动态查询则允许用户明确不同的参数来缩小网页返回的数据。
网络查询存储在后缀名为.iqy的文本文件里。
.iqy文件的内容可以使用任何文本编辑器(例如Windows记事本)打开后查看。
Figure16-12:
Thisdialogboxintheuserinterfaceallowsyoutocreateawebquerywithoutknowinganythingaboutprogramming.
Figure16-13:
Thewebqueryfilereferencesthepagefromwhichyouwanttoretrievedataandspecifiesparameterstodefinehowdatashouldbeimportedandanyspecialinstructionsforthewebserver.
.iqy文件包含下述部分:
片断名称
描述/示例
查询类型
(可选片断)
当你使用版本片断时,设置为WEB:
WEB
查询版本
(可选片断)
允许你设置网络查询的版本号码,例如:
1
URL
(必须)
你将获取数据的网页的URL,例如:
http:
//www.um.lublin.pl/oswiata/sz_pdst.htm
POST参数
(可选片断)
你可以使用POST或者GET方法向网络服务器发送参数。
本片断用来通过POST方法发送参数。
这些参数需要分别输入在不同行上,如下例所示:
From=USD&Amount=1&To=CAD
From,Amount和To是参数名称,而等号后面的值是参数设置。
参数用&符合分隔开来。
注意,当使用GET方法发送参数时,用使用问号将参数附加在URL地址之后,如下所示:
Symbols是个参数名称,而met则是一个股票符号(参数数值),你想要从指定URL地址获取的数据。
通过VBA创建和运行网络查询
在前面的章节里,你学习了网络查询可以通过使用菜单选项或者在一个文本编辑器例如记事本里敲入特殊的指令来成就。
创建网络查询的第三种方法是通过VBA语句。
想要编程创建一个网络查询的话,那么请使用QueryTables集合的Add方法。
该集合属于工作表对象并包含具体工作表中的所有QueryTable对象。
Add方法返回一个QueryTable对象,代表一个新查询。
该方法的格式如下:
expression.Add(Connection,Destination,[Sql])
Expression是一个返回QueryTable对象的表达式,Connection指定该查询表的数据源,数据源可以使下面之一:
n带有格式为“URL;
"URL;http:
//www.nycenet.edu/distschweb/searchresult.asp"
n表明指向现有网络查询文件(.iqy)路径的字符串,使用“FINDER;
例如:
"FINDER;C:
\ProgramFiles\MicrosoftOffice\Office\Queries\_
MSN
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- ExcelVBACh16