Excel2002VBA_Ch16
学习微软 Excel 2002 VBA 编程和XML，ASP技术 - CH16
(2010-11-28 17:28:38)
转载▼
分类：Excel
第十六章Excel和因特网
作者：Julitta Korol 翻译：Tiger Chen Dec 12‘ 2005
我们这几年已经见证了因特网的飞速发展，这让我们获取散布在世界各地的无数的知识成为可能。我们要感谢因特网，我们有了触手可及的数据库，涵盖各行各业和各个知识领域，词典和百科全书，股票查询，地图，天气预报和大量其它类型的储存在无数网页服务器上的信息。很多情况下，从网页上获取的信息成为电脑程序进一步分析的主题。由于其结构（行和列），Excel 2002是使用这些网页表数据的极佳工具。
本章示范Excel 2002里可用的内置工具如何从网页获取数据，或者发布Excel电子表格到网页。在这里，你将发现许多VB语句，让你使用自己编写的VB过程来获取或者发布数据。为了能够尽可能从本章受益，你应该链接到因特网（猫，电话线或网线，因特网服务器账号，以及网页浏览器，例如Internet Explorer 5.0或更高版本）。
使用VBA创建超链接
和其它Office里的应用软件一样，Excel 2002也可以让你在电子表格里创建超链接。点击一个含有超链接的单元格之后，你可以打开一个网络服务器，局域网或者因特网上的文件。你既可以直接在用户界面使用插入|超链接（图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. 激活第三步创建的过程（运行）
Sub FollowMe()
Dim myRange As Range
Set myRange = Sheets(1).Range("A1")
myRange.Hyperlinks.Add _
Anchor:=myRange, _
Address:="https://www.wendangku.net/doc/ec17152249.html,/bin/search", _
ScreenTip:="Search Yahoo", _
TextToDisplay:="Click here"
End Sub
当你运行FollowMe过程后，第一个工作表的单元格A1将会包含一个超链接―Click here‖，屏幕提示为―Search Yahoo‖（参见图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里创建和使用事件过程的内容）
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim strSearch As String
strSearch = Sheets(2).Range("C3").Formula ?译者：如有问题，本句改为…Sheets(―Sheet2‖)…If Target = Range("C3") Then
Cancel = True
ActiveWorkbook.FollowHyperlink _
Address:="https://www.wendangku.net/doc/ec17152249.html,/cgi-bin/query", _
ExtraInfo:="q=" & strSearch, _
Method:=msoMethodGet
End If
End Sub
3. 现在切换到Excel应用软件窗口，并且在Sheet2的单元格C3里面输入任何你想要查找信息的词语或术语（参见图16-4）
4. 确保你链接到因特网了
5. 双击单元格C3。该操作导致单元格C3里输入的文本会被发送到AltaVisa搜索引擎，屏幕上应该会显示查找特定条件主题的索引（图16-5）
图16-4 Excel工作表可以用来将搜索参数发送到任何因特网搜索引擎（参见第二步的Worksheet_ BeforeDoubleClick过程）
图16-5 从Excel工作表打开的网页，列出了基于工作表单元格数据为标准找到的主题
使用VBA创建和发布HTML文件
和以前的版本一样，Excel 2002也允许你将文件保存为HTML格式（Hypertext Markup Language 超文本链接标示语言）。该格式就像它的标准.xls格式一样，也能够为Excel所识别。当你保存Excel文件为HTML格式时，你可以使用因特网浏览器例如IE或者Netscape Navigator来查看电子表格了。当你保存工作簿或其部分为HTML时，Excel保存原始工作簿的选项。正由于此，用户既可以使用浏览器，也可以在Excel应用软件窗口内部来查看该文件。
Excel 2002能够保存数据和图表为交互式网页。在保存整个工作簿或者其一部分为HTML 格式时，你可以选择创建一个静态的HTML文件，或者一个交互式的HTML文件并明确该文件的保存地址。你可以直接将文件保存到网页，网络服务器或者当地电脑。
不需要使用任何东西，只要使用用户界面（文件|保存为网页），你就可以将整个工作簿或者工作簿的一部分保存到网页，以至于用户可以交互式地使用该信息，或者仅查看该数据。如何将整个工作簿或者工作表（或者它们的成员之一——例如图表，数据透视表）放置到网页上的详细指导，可以在Excel在线帮助里找到。因为本书是关于编程的，我们仅侧重于通过VBA代码执行这些任务的方法。Excel 2002 的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
Filename是明确对象源（SourceType）保存地址的字符串，它可以是URL（Unified Resource Locator统一的信息指定方法）或者当地或网络文件的路径。Sheet是保存为网页的工作表名称。Source是确定对象源的唯一名称。该参数取决于SourceType参数。当SourceType是xlSourceRange常数时，Source是单元格区域，或者单元格区域定义的名称。如果参数SourceType是常数，例如xlSourceChart，xlSourcePivotTable或者xlSourceQuery，那么Source 则指定图表名称，数据透视报告名称或者查询表名称。HTMLType指明是否被选的工作表成
注意：Office Web Components允许你在因特网浏览器上使用Excel分析功能。
DivID是HTML DIV上唯一的识别符，用来识别网页上的项目。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文件的名称。
' The procedure below will publish a worksheet
' with an embedded chart as static HTML
Sub PublishOnWeb(strSheetName As String, strFileName As String)
Dim objPub As Excel.PublishObject
Set objPub = ThisWorkbook.PublishObjects.Add( _
SourceType:=xlSourceSheet, _
Filename:=strFileName, Sheet:=strSheetName, _
HtmlType:=xlHtmlStatic, Title:="Calls Analysis")
objPub.Publish True
End Sub
Sub CreateHTMLFile()
Call PublishOnWeb("Help Desk", "C:\WorksheetWithChart.htm")
End Sub
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过程，如下所示：
Sub InterHTML()
Dim strSheetName As String
strSheetName = "Help Desk"
' ensure that the chart is not selected
Range("A1").Select
ActiveWorkbook.PublishObjects _
.Add(xlSourceChart, "C:\Inter_WorksheetWithChart.htm", _
strSheetName, "Chart 1", xlHtmlChart).Publish (True)
End Sub
3. 运行你刚创建的过程
4. 在Windows文件浏览器里，双击C:\Inter_WorksheetWithChart.htm，这将导致因特网浏览器被激活并显示交互式图表（参见图16-10）。该网页也包含一交互式工作表，为图表提供数据。
5. 更改B列里的任何数值，并查看图表上的变化。
注意：想要交互式地使用数据的话，你就必须在你的电脑上安装Microsoft IE 4.01或者更高版本，以及Microsoft Office Web Components。
图16-10 交互式发布的Excel工作表可以在浏览器上直接修改
网络服务器——储存和打开工作簿
要使用VBA语句在一个网络服务器上保存和打开工作簿是可能的，然而，在你能够储存你的工作簿到网络服务器之前，必须在该服务器上运行FrontPage Server Extensions。下述语句将工作簿保存到一网站：
ActiveWorkbook.SaveAs ―https://www.wendangku.net/doc/ec17152249.html,/TestWkb.xls‖
下述语句打开储存在一网站上的工作簿：
Workbooks.Open(―https://www.wendangku.net/doc/ec17152249.html,/TestWkb.xls‖)
网络查询
如果你计划从某个网页获取数据，并且在Excel里使用和分析它们，那么你可以打开Excel 的数据菜单，并选择―导入外部数据‖|―新建Web查询‖。网络查询允许你直接从网络获取数据到Excel里面。将数据放置到工作表后，你可以使用Excel工具来进行数据分析。使用网络查询，你可以导入工作表一个表格，一系列表格或者某具体网站包含的所有文本。
技巧16-1 网络查询准备就绪
Excel 2002带来了好些个内置网络查询。它们安装在C:\Program Files\Microsoft Office\Office10\ Queries文件夹里面，并且可以文件菜单上的打开命令来装载它们。这些查询的名称为：
MSN MoneyCentral Investor Currency Rates.iqy
MSN MoneyCentral Investor Major Indicies.iqy
MSN MoneyCentral Investor Stock Quotes.iqy
如果你的C:\Program Files\Microsoft Office\Office10\ Queries文件夹是空的，那么你需要更新当前的Excel安装（使用控制面板上的添加/删除程序对话框）并且注明你需要安装这些功能。
Figure 16-11: TDK corporation stock quotes were retrieved from the web using the built-in web query MSN MoneyCentral Investor Stock Quotes.iqy after typing TDK in a dialog box displayed upon activating this query.
想要运行网络查询的话，你就必须链接到因特网上。
网络查询可以是静态的也可以是动态的。静态查询总是返回相同的数据，而动态查询则允许用户明确不同的参数来缩小网页返回的数据。网络查询存储在后缀名为.iqy的文本文件里。.iqy文件的内容可以使用任何文本编辑器（例如Windows记事本）打开后查看。
Figure 16-12: This dialog box in the user interface allows you to create a web query without knowing anything about programming.
Figure 16-13: The web query file references the page from which you want to retrieve data and specifies parameters to define how data should be imported and any special instructions for the web server.
通过VBA创建和运行网络查询
在前面的章节里，你学习了网络查询可以通过使用菜单选项或者在一个文本编辑器例如记事本里敲入特殊的指令来成就。创建网络查询的第三种方法是通过VBA语句。
想要编程创建一个网络查询的话，那么请使用QueryTables集合的Add方法。该集合属于工作表对象并包含具体工作表中的所有QueryTable对象。Add方法返回一个QueryTable对象，代表一个新查询。该方法的格式如下：
expression.Add(Connection, Destination, [Sql])
Expression是一个返回QueryTable对象的表达式，Connection指定该查询表的数据源，数据源可以使下面之一：
n 带有格式为―URL;
"URL;https://www.wendangku.net/doc/ec17152249.html,/distschweb/searchresult.asp"
n 表明指向现有网络查询文件（.iqy）路径的字符串，使用― FINDER;.‖格式。例如：
"FINDER;C:\Program Files\Microsoft Office\Office\Queries\ _
MSN MoneyCentral Investor Currency Rates.iqy"
n 包含OLE DB或者ODBC链接的字符串。ODBC链接字符串形式为―ODBC;
"ODBC;DSN=MyNorthwind;UID=NorthUser;PWD=UserPass;Database=Northwind"
n ADO或者DAO记录集对象。Excel将保留该记录集，直到查询表被删除或者该链接被更改。查询表的结果不可编辑
n 文本文件路径字符串，格式为―TEXT;
―TEXT;C:\myTextFile.txt‖
destination是查询表左上角的目标单元格（查询表结果放置的地方）。该单元格必须在包含expression里使用的QueryTable对象的工作表中。当使用QueryTable对象作为数据源时，可选参数Sql不需要。
下面显示的过程在当前活动工作簿里创建了一个新的网络查询，从网页获取的数据将放置在一工作表里，作为静态文本。
1. 打开一个新工作簿，并保存为MyWebQueries.xls
2. 切换到VB编辑器窗口，并且插入一个新模块到当前VBA工程
3. 在代码窗口，输入Manhattan_Schools过程，它从网页上获取曼哈顿的NYC学校清单。Sub Manhattan_Schools ()
' create a Web query in the current worksheet, connect to the Web,
' retrieve data and paste it in the worksheet as static text
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;https://www.wendangku.net/doc/ec17152249.html,/dist_sch/sch/" & _
"searchresult.asp?boro=Manhattan&flag=schoolInfo2", _
Destination:=Range("a1"))
?译者：Excel2003上述网址已经不好使，可以换一个例如
"URL;https://www.wendangku.net/doc/ec17152249.html,/focus/utop.html"查询中国大学排名
.BackgroundQuery = True
.WebSelectionType = xlSpecifiedTables
.WebTables = "Table3" ‘ 那么这里也要相应改为‖18‖
.WebFormatting = xlWebFormattingNone
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub
4. 运行Manhattan_Schools过程。在程序的执行中，会发生下述任务：a 建立对指定网页的链接，b 获取网页数据，以及c 数据放置到工作表。
当你激活上述过程时，当前工作表将显示曼哈顿学校的名称和地址（见图16-14）。注意，该工作表不含有任何超链接，因为我们在过程代码里，将QueryTable的WebFormatting属性设置为xlWebFormattingNone了。该属性决定当你导入页到查询表时，从网页应用多少格式。你可以使用下述常量之一：xlWebFormattingAll，xlWebFormattingNone（缺省设置），或者xlWebFormattingRTF。QueryTable对象的BackgroundQuery设置为True时，允许你在从网页获取数据时可以执行其它操作。WebSelectionType属性决定是否导入整个网页，网页上所有表格，还是网页上的某个特定表格到查询表中。WebSelectionType属性可以是下述常量之一：xlAllTables，xlEntirePage或者xlSpecifiedTables。
当你将网页导入查询表时，WebTables属性明确一个逗号分隔的表格名称列表，或者表格索引号。
从网页获取数据后，为了在工作表里显示这些数据，你必须使用QueryTable对象的Refresh 方法。如果你在过程代码里忽略了该方法，从网页获取的数据就不会可见。
通过设置SaveData属性为True，从网页获取的表格就会和工作簿一起保存。
Figure 16-14: This data was retrieved from a web page using the web query in a VBA procedure.
5. 在由网络查询放置在工作表里的数据区域中的任意地方单击右键，然后从快捷菜单上选择编辑查询。你将看到编辑网络查询对话框。点击该对话框工具栏上的选项按钮可以访问查询选项对话框，如图16-15所示。注意，在格式区域，选项按钮―无‖被选上了。该选项按钮代表过程代码中的WebFormatting属性的xlWebFormattingNone的设置。
Figure 16-15: The Web Query Options dialog box
网络查询参数
为了从网页获取数据，你经常需要明确参数。要在你的网络查询里，向网络服务器发送参数的话，那么你需要在核实某个具体网络服务器使用哪种方法后，使用POST或者GET方法。
你可以使用下述方法找到该信息：
1. 激活你的浏览器并且输入你想要获取数据的网页地址。例如，输入：
https://www.wendangku.net/doc/ec17152249.html,/ucc/
Figure 16-16: This web page allows you to convert one type of currency into another type.
2. 从浏览器的查看菜单里选择源文件。该网页背后的代码就会出现在记事本里面（参见如16-17）
3. 在记事本里，选择编辑菜单的查找并且输入POST作为要查找的文本。如果给网络服务器发送参数使用的是POST方法，那么文本POST就应该出现，如下所示。POST方法后，有提供数据的网络服务器URL地址。现在，你知道了使用的是哪种发送参数的方法，你还需要知道如何调用这些参数。
Figure 16-17: You can view the source of the underlying data for a particular web page by se lecting View | Source from your browser‘s menu. The underlying code will appear in Notepad. 4. 在记事本，选择编辑菜单上的查找并且输入Name作为查找字符串。在单词name之后，你应该可以看到括号里有一些文本，这些文本就是第一个参数的名称。在单词―value=‖后面，你应该可以看到参数的当前值。例如：
在上面的HTML语句中，单词―Amount‖是参数名称，―1‖是该参数的当前值。参数值也可以是HTML
…
…
在上面的HTML代码片断里，单词―From‖是参数名称，该参数可以是下述值之一：USD，CAD，GBP，DEM。
如果网络服务器使用GET方法接收参数的话，那你可以在浏览器地址栏里看到该参数名称和置：
https://www.wendangku.net/doc/ec17152249.html,/dist_sch/sch/searchresult/asp?boro=Manhattan _
&flag= schoolInfo2
注意，第一个参数前面带有一个问号，参数之间使用&符号分割开来。
静态和动态参数
网络查询参数可以是静态的或者动态的。当你使用静态参数时，你在运行网络查询时不需要输入任何值（参见下面的过程Portfolio）。静态网络查询总是返回相同的数据。
如果你使用动态参数的话，那么你在执行网络查询时，可以给你的数据收集明确一个或多个值作为标准（参见下一章节的过程Portfolio2）。动态网络查询基于提供的参数返回数据。在接下来的示例中，你将看到如果编程创建一个静态网络查询。该网络查询将使用GET方法给网络服务器发送参数。这些参数是静态的，因为你当网络查询运行时，你不会被提示以提供它们的值。服务器将要接收到的参数值编写在VBA过程里。
1. 在VB编辑器窗口，插入一个新模块到当前VBA工程– VBAProject(MyWebQueries.xls)
2. 在代码窗口，输入Portfolio过程代码：
Sub Portfolio()
Dim sht As Worksheet
Dim qryTbl As QueryTable
' insert a new worksheet in the current workbook
Set sht = ThisWorkbook.Worksheets.Add
' create a new Web query in a worksheet
Set qryTbl = sht.QueryTables.Add(Connection:= _
"URL;https://www.wendangku.net/doc/ec17152249.html,/quote?symbols=met&symbols=aol", _
Destination:=sht.Range("a1"))
?Excel2003上述查询出错，可以将网址改为"URL;https://www.wendangku.net/doc/ec17152249.html,/q?d=t&p=&q=q&s=MET+AOL&m=L"
' retrieve data from Web page and specify formatting
' paste data in a worksheet
With qryTbl
.BackgroundQuery = True
.WebSelectionType = xlSpecifiedTables
.WebTables = "17" ?这里相应改为23
.WebFormatting = xlWebFormattingAll
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub
3. 运行Portfolio过程，该过程的结果显示在图16-18上。