开发集成的Office解决方案

最新推荐文章于 2013-09-29 10:19:30 发布

xuandhu

最新推荐文章于 2013-09-29 10:19:30 发布

阅读量1.9k

点赞数

分类专栏： ASP.NET组件文章标签： office webbrowser microsoft excel web vba

本文链接：https://blog.csdn.net/xuandhu/article/details/221730

版权

ASP.NET组件专栏收录该内容

10 篇文章 0 订阅

订阅专栏

http://www.microsoft.com/china/msdn/archives/technic/voices/office06012000.asp

<script language=javascript src="/library/toolbar/cn/global.js"></script> <script language=JavaScript> </script> <script language=javascript> </script> <script> var sContentID = ""; </script> <script language=JavaScript src="/library/include/ctredir.js" type=text/javascript></script> <script language=javascript> var doImage=doImage;var TType=TType; function mhHover(tbl,idx,cls){var t,d;if(document.getElementById)t=document.getElementById(tbl);else t=document.all(tbl);if(t==null)return;if(t.getElementsByTagName)d=t.getElementsByTagName("TD");else d=t.all.tags("TD");if(d==null)return;if(d.length<=idx)return;d[idx].className=cls;} function footerjs(doc){if(doImage==null){var tt=TType==null?"PV":TType;doc.write(' ');}} </script>

在 Microsoft.com 中搜索：

开发集成的Office解决方案，第2部分

David Shank
2000年6月1日发布

下载本文的源代码 (66.4K)

在上个月的专栏中，我简单介绍了使用 Visual Basic® for Applications (VBA) 和 Automation，集成两个或多个 Office 应用程序中的功能。这些简单的技术，使得我们能够在还不是顶呱呱的 office 应用程序中添加功能。在本期专栏中，我准备继续讨论更高级例子，它们扩展了 Microsoft Excel 的，利用 Web 查询从 Web 页中获取数据的能力。

“我的自定义 Web 查询”对话框标识了 Web 页面中包含我所需要的数据的表，并且允许我将该表导入 Excel 中。

理解 Web 查询

Web 查询允许使用 Microsoft Excel 分析来自 Web 页面的数据。通常，Web 页面中包含着您想进一步分析的制表数据，分析手段为 Excel 的强大数据分析功能。您可以将数据从 Web 页面复制并粘贴到 Excel 中，但是随后，每当需要更新数据时必须重复该过程。用 Web 查询，可让 Excel 将数据导入并保存到工作表中；然后，每当您打开工作表时，数据会自动刷新。例如，这可以用在 Excel 自定义抵押贷款计算器中，它使用来自 Web 页面的当前抵押贷款利率作为其数据来源。

可用“新建 Web 查询”对话框创建 Web 查询。

查看图 1.“Web 查询”对话框

单击数据菜单中的获得外部数据，然后单击新 Web 查询可以打开该对话框。然后可以输入需要使用的 Web 页面地址，或者单击浏览 Web 命令按钮导航到 Web 页面。在选择了页面后，需要指定是否希望获得整个页面、页面上的所有表或者页面上的特定的表。在单击了确定按钮后，您指定的信息便从 Web 页面取出，并放入活动的工作表中。在检索了数据后，可以用 Microsoft Excel 中的工具和功能来分析它。

在大多数情况下，您只想要 Web 页面中的一个数据表 — 而不是整个页面或页面上的所有表。您可以用表号或者表名，在页面上指定单个表。但是，实际上页面中的表很少会有名称，因此在大多数情况下需要知道表号。表号指定了表在页面的所有表中的位置。HTML 格式的表是由 <TABLE></TABLE> 标记对指定的。为了确定页面中的表号，必须查看页面的源 HTML 并且计算 <TABLE></TABLE> 标记对，直到找到需要的表为止。大多数 Web 页面都广泛使用表来格式化组织和显示数据的方式。这意味着在任何给定的 Web 页面上都可以有几十个表。此外，表还常常嵌套在其他表中，使得确定表的实际位置很困难。实际上，在 HTML 中寻找这样的页面是很不方便的 — 而且需要了解如何阅读和解释页面后面的原始 HTML。

想象一下，如果只要指出您所需要的表，然后让 Excel 找到表号，这会多么简单。我把这个功能想象得越好，我越感觉到在 Excel 中增加这个功能实际上会非常容易。它将意味着创建自定义 Web 查询对话框，在框中显示 Web 页面，并允许我选择指定的表。

创建您自己的 Web 查询对话框

我的自定义 Web 查询对话框，允许我导航到包含希望在 Excel 中使用的数据的 Web 页面。我的自定义对话框显示在下面的屏幕快照中，在此我已经导航到 MoneyCentral Top Funds 页面（英文）。

查看图 2. Web 查询表查找程序

在单击查看表按钮后，我可以浏览页面上的每个表，直到找到我需要的表为止。例如，在下面的屏幕快照显示的对话框中，我已经标出了要导入 Excel 中的表。

查看图 3. Web 查询表查找程序中的表

从对话框底部的导航控制标签可以看到，原始页面上有 15 个表，基金表的编号为 13。找到了要使用的表后，单击获得表按钮，将其导入 Excel 中：

查看图 4. 将表导入 Excel 中

获得表按钮创建了 Web 查询，用该表作为它的数据源。

创建自定义对话框

自定义“Web 查询”对话框是通过下面的步骤创建的：

打开 Excel 的 Visual Basic Editor (VBE)，并插入新的 UserForm。
添加“文本框”和“标签”控件，在窗体顶部创建“地址”框。
添加显示 Web 页面和表的 WebBrowser 控件。要将 WebBrowser 控件放入工具箱中，请单击 VBE 工具菜单，选择其他控件命令，再选择“Microsoft Web 浏览器”控件。（如果控件不出现，则必须设置对它的引用，使它出现在列表中。要设置引用，请单击 VBE 工具菜单上的引用命令，然后单击浏览按钮并导航到 Windows System 目录中的文件 shdocvw.dll。现在 WebBrowser 控件将出现在“其他控件”列表中，然后就可以将它添加到工具箱了。）
添加查看表、重载页面、获得表和关闭按钮的“命令按钮”控件。
为了在窗体的底部显示“Table #x of y”信息，用两个命令按钮和一个标签添加用于表导航的控件。

添加使之完全工作的 VBA 代码

我不打算提供该自定义对话框后面的所有代码，但是我将显示足够的内容来帮助您理解如何使它工作。在您理解了“概貌”之后，您能够填写细节，不会有很多麻烦了。

自定义对话框后面的代码完成下列的过程：

它根据“地址”文本框中用户提供的地址导航到 Web 页面。
当用户单击“查看表”命令时，代码将把每个表中的 HTML 放入 VBA 集合对象的页面中。然后遍历集合 — 并且在用户的 TEMP 目录中为每个成员创建一个临时 HTML 文件，并将代表页面中表位置的临时文件名和编号保存到另一个 VBA 集合中。最后显示页面中的第一个表格，并且激活窗体底部的导航按钮。
当用户在原始 Web 页面的表中导航时，全局变量将用于跟踪当前所显示的临时 HTML 文件的地址，以及临时文件 VBA 集合中的文件索引（在下面的示例代码中，这些全局变量使用“g”前缀）。
当用户单击获得表按钮时，VBA 代码用 Web 页面的地址和表号（与前一步的 VBA 集合中所显示表的索引编号相同）在 Excel 的活动单元中创建 QueryTable 对象。
当用户关闭对话框时，所有临时 HTML 文件都将从用户的 TEMP 目录中删除。

这就是全部要做的！在创建了 QueryTable 对象并且提供了 Web 页面地址和表号之后，Excel 将完成其余的所有工作，实际上是将表放入工作表，然后将其正确格式化。在下面的小节中将介绍一些执行这些不同步骤的代码。

导航到 Web 页面

在此代码中 WebBrowser1 是 WebBrowser 控件的名称，txtAddress 是 Address 文本框控件的名称。用户提供的地址将传送给 WebBrowser1 的 Navigate2 方法以便显示指定的页面。

Private Sub txtAddress_AfterUpdate()
	' 如果输入新的 URL，则试图导航至
	' 新的 Web 页地址。
	WebBrowser1.Navigate2 Me.txtAddress.Text
End Sub

关于使用 Microsoft WebBrowser 控件的详细信息，请参阅 MSDN Online Web Workshop（英文）。

从 Web 页面获得表

实际上，从 Web 页面中提取代表每个表的 HTML，并将它保存为 VBA 集合中的项目，是非常容易的：

Set colTables = WebBrowser1.Document.all.tags("TABLE")

Microsoft WebBrowser 控件的文档属性将返回当前显示页面的“文档对象模型 (DOM)”。DOM 将 Web 页面中的每个元素都显示为可以用 VBA 代码处理的对象。DOM 的所有属性将返回 Web 页面中所有元素的集合；标记集合返回括号内标记名称所指定的所有元素集合。请阅读 DOM（英文），可以获得更多 MSDN Online Web Workshop 中的信息。

现在我们已经有了 Web 页面中每个表的 HTML 集合，我们需要创建临时 HTML 文件以代表用户 TEMP 目录中的每个表。下面是调用 CreateTempHTML 自定义过程以便完成该任务的代码：

Dim colTables As Object
Dim colCurrentTable As Object
Dim intcntr As Integer

   With WebBrowser1
	intcntr = 0
	' 获取现有表格的集合。
	Set colTables = .Document.all.tags("TABLE")
	For Each colCurrentTable In colTables
		' 创建包含页面上每个表格的临时 HTML 页。
		Call CreateTempHTML(colCurrentTable.outerhtml, intcntr)
		intcntr = intcntr + 1
		Next colCurrentTable
   End With

请注意 CreateTempHTML 过程接受两个参数。第一个参数使用 outerhtml 属性传送代表 Web 页面中某个表的整个 HTML。intcntr 参数是 VBA 集合中每个表的索引，但是它也代表原始 Web 页面中 HTML 内每个表的位置。在创建 Excel Web 查询时我们将使用该编号来标识表。

在此没有显示 CreateTempHTML 过程，但是它可以预先假定为“<HTML><HEAD><TITLE></TITLE></HEAD><BODY>”，并且将 “</BODY></HTML>”附加到代表特定表的 HTML，以便创建完整的 Web 页面。然后它使用 VBA 打开、打印和关闭文件功能将新的 Web 页面写入用户的 TEMP 目录，并将该新 Web 页面的地址保存到另一个 VBA 集合中。当用户从原始 Web 页面浏览表时，这个新的集合将用于标识要显示的页面。

一旦为原始 Web 页面中的每个表创建了临时文件后，代表第一个表的页面将显示在对话框中，并且对话框底部的导航按钮将激活。

从 Web 页浏览表格

此时，显示来自页面的不同表就非常简单了，只要在临时文件地址集合中移动，并在对话框中为 WebBrowser 控件显示不同文件就可以了。当用户单击对话框中的“>” 按钮时将调用 ShowNextTable 过程。当用户单击对话框中的“<”按钮时将调用 ShowPreviousTable 过程。

Function ShowNextTable()
	' 从 cmdMoveNext_Click() 过程调用以
	' 显示下一个临时的 HTML 文件。
	If Me.gintCurrentRecord < gcolTablesOnPage.Count Then
		e.gintCurrentRecord = Me.gintCurrentRecord + 1
		ebBrowser1.Navigate2 gcolTablesOnPage(Me.gintCurrentRecord)
		e.cmdMovePrevious.Enabled = True
	End If
	If Me.gintCurrentRecord = gcolTablesOnPage.Count Then
		e.cmdMoveNext.Enabled = False
	End If
	lblRecords.Caption = "Table #" & Me.gintCurrentRecord & " of " & gcolTablesOnPage.Count
End Function
Function ShowPreviousTable()
	' 从 cmdMovePrevious_Click() 过程调用以
	' 显示上一个临时的 HTML 文件。
	If Me.gintCurrentRecord > 1 Then
		e.gintCurrentRecord = Me.gintCurrentRecord - 1
		ebBrowser1.Navigate2 gcolTablesOnPage(Me.gintCurrentRecord)
		e.cmdMoveNext.Enabled = True
	End If
	If Me.gintCurrentRecord = 1 Then
		e.cmdMovePrevious.Enabled = False
	End If
	lblRecords.Caption = "Table #" & Me.gintCurrentRecord & " of " & gcolTablesOnPage.Count
End Function

这些过程仅仅显示代表表的下一个或上一个 Web 页面，然后更新窗体底部的“Table #x of y”信息。

将选定的表导入到 Excel 中

当用户单击获得表命令按钮时，我们将调用 CreateWebQueryForTable 函数，以便使用用户提供的原始页面地址和代表对话框中当前所查看表的表号来创建 Excel Web 查询。

Function CreateWebQueryForTable(strURL As String, _
 					  ntTableNum As Long) As Boolean
	' 该过程用于创建 web 查询本身。
	Dim strQuery			As String
	Dim strInsertCellAddress	As String
	Dim strQuerySavedName		As String
	Dim qtblWebQuery			As QueryTable

	" 决定查询表的目的单元格。
	strInsertCellAddress = ActiveCell.Address(RowAbsolute:=False, 	columnabsolute:=False)


	' 创建 web 查询的 queryTable 对象， 
	' 指定对象属性并使用 Resfresh 
	' 方法以填充数据。
	Set qtblWebQuery = ActiveSheet.QueryTables.Add(Connection:= _
	"URL;" & strURL, Destination:=Range	(strInsertCellAddress))

	With qtblWebQuery
		.FieldNames = True
		.RowNumbers = False
		FillAdjacentFormulas = False
		PreserveFormatting = False
		.RefreshOnFileOpen = False
		.BackgroundQuery = True
		.RefreshStyle = xlInsertDeleteCells
		.SavePassword = False
		.SaveData = True
		.AdjustColumnWidth = True
		.RefreshPeriod = 0
		.WebSelectionType = xlSpecifiedTables
		.WebFormatting = xlWebFormattingAll
		WebPreFormattedTextToColumns = True
		WebConsecutiveDelimitersAsOne = True
		WebSingleBlockTextImport = False
		.WebDisableDateRecognition = False
		.WebTables = intTableNum
		.Refresh BackgroundQuery:=False
	End With
End Function

intTableNum 变量告诉 Excel 哪个表是 QueryTable 对象的来源。来自表的数据和表格式将导入 Excel 并且通过 QueryTable 对象的 Refresh 方法显示在工作表中。

有关 Excel QueryTable 对象的详细信息，可以用关键字“QueryTable”在 Excel VBA 帮助主题中搜索。

何处可获得详细信息

下面是在研究 Microsoft Access 时可以使用的一些其他资源：

有关用 Office Automation 开发解决方案的详细信息，请参见 Office Developer 站点的推荐文章列表（英文）。。
确保查看热点主题列表,它是指向各种 Office Automation 主题（英文）的功能指针。。
在包括大量大型示例代码的 Microsoft Knowledge Base（英文）中获得最新的 Office Automation。。
和往常一样，定期访问 Office Developer 站点（英文），以获取关于 Office 解决方案开发的信息和技术文章。

David Shank David Shank 是 Office 组专门从事开发者文档的程序员/作者。据说他住在 Redmond 东部的高山上，是仍生活在西北部的为数不多的土著西北人之一。

存档的 Office Talk 专栏

		2000 年
5月4日		开发集成的 Office 解决方案（英文）
4月6日		在 Microsoft Access 中设计组合框与列表框控件，第二部分（英文）
3月2日		在 Microsoft Access 中设计组合框与列表框控件（英文）
2月3日		更多有关 Office 解决方案的数据（英文）
1月13日		使用 Office 解决方案中的数据（英文）
		1999 年
12月9日		使用 Office HTML（英文）
11月4日		清除 Office Mailbag（英文）
10月7日		经验共享（英文）
9月2日		单击此处来安装（英文）
8月5日		妈妈的 Office, 过时啦（英文）
7月8日		Outlook 暑期辅导（英文）
6月10日		Office 的新功能（英文）