查询完成后，如何对来自 Excel Web 查询的数据进行后处理? [英] How can I post-process the data from an Excel web query when the query is complete?

问题描述

作为电子表格开发人员，我试图将两组行拼接在一起:一组来自网络查询到我拥有的网络服务，另一组是由电子表格用户(不是我)添加的一组手动行.

As a spreadsheet developer, I am trying to stitch together two sets of rows: one from a web query to a web service I own, and the other a set of manual rows added by the spreadsheet's user (not me).

Excel 内置的 Web 查询/连接对象仅提供两种模式:我可以打开启用后台刷新"使 Web 查询异步，或者取消选中它.

Excel's built in Web Query / Connections object only provides two modes: I can turn on "Enable background refresh" which makes the web query asynchronous, or uncheck it.

如果取消选中它，Excel 在查询执行时会冻结，这是不可取的.检查后，似乎没有任何类型的回调或事件挂钩可供通知，以便我可以对刷新的网络数据进行操作.

With it unchecked, Excel freezes up while the query executes, which is undesireable. With it checked, there doesn't seem to be any kind of callback or event hook available to be notified, so that I can operate against the refreshed web data.

还有其他方法可以做到这一点吗?

Is there another way to do this?

推荐答案

Excel Web 查询使用称为 QueryTable 的对象来执行检索和显示数据的业务.

An Excel web query utilizes an object called a QueryTable to carry out the business of retrieving and displaying the data.

一个 QueryTable 可以被 VBA 访问.

A QueryTable can be accessed by VBA.

就像图表对象一样，可查询对象具有只能通过使用 类模块中的 WithEvents 关键字来响应的事件，如下所示:

And just like the chart object a querytable object has events that can only be responded to by using the WithEvents keyword from a class module, like so:

Private WithEvents MyQueryTable As QueryTable

Private Sub MyQueryTable_AfterRefresh(ByVal Success As Boolean)
    'Do your post processing here...
End Sub

这篇关于查询完成后，如何对来自 Excel Web 查询的数据进行后处理?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！