|
开发集成的Office解决方案,第2部分David Shank2000年6月1日发布 在上个月的专栏中,我简单介绍了使用 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 查询。 单击数据菜单中的获得外部数据,然后单击新 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 页面 (英文)。 在单击查看表按钮后,我可以浏览页面上的每个表,直到找到我需要的表为止。例如,在下面的屏幕快照显示的对话框中,我已经标出了要导入 Excel 中的表。 从对话框底部的导航控制标签可以看到,原始页面上有 15 个表,基金表的编号为 13。找到了要使用的表后,单击获得表按钮,将其导入 Excel 中: 获得表按钮创建了 Web 查询,用该表作为它的数据源。 创建自定义对话框 自定义“Web 查询”对话框是通过下面的步骤创建的:
添加使之完全工作的 VBA 代码 我不打算提供该自定义对话框后面的所有代码,但是我将显示足够的内容来帮助您理解如何使它工作。在您理解了“概貌”之后,您能够填写细节,不会有很多麻烦了。 自定义对话框后面的代码完成下列的过程:
这就是全部要做的!在创建了 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 Talk 专栏
|
开发集成的Office解决方案
最新推荐文章于 2013-09-29 10:19:30 发布
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>
<script language=javascript>var msviFooter2;if (document.getElementById){msviFooter2 = document.getElementById("msviFooter2");msviFooter2.style.filter = "";}</script>
<script language=javascript>footerjs(document);</script>