查询完成后，如何从Excel网页查询中处理数据？ [英] How can I post-process the data from an Excel web query when the query is complete?

问题描述

作为一个电子表格开发人员，我试图将两组行拼接在一起：一个是网页查询，一个是我自己的Web服务，另一个是由电子表格的用户（而不是我）添加的一组手册。 / p>

Excel内置的Web查询/连接对象只提供两种模式：我可以打开启用后台刷新，使Web查询异步，或取消选中。 >

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

是有另一种方式来做到这一点吗？

解决方案

Excel Web查询利用一个名为QueryTable的对象来执行检索和显示数据的业务。 >

VBA可以访问QueryTable。

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

  Private WithEvents MyQueryTable As QueryTable 
 
 Private Sub MyQueryTable_AfterRefresh（ByVal Success As Boolean）
'你的后期处理在这里... 
 End Sub 
  

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'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.

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?

解决方案

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

A QueryTable can be accessed by VBA.

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网页查询中处理数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！