我正在使用 Excel VBA 的 WebQuery，使用类似于此示例的内容：
（此示例实际上是从此处复制粘贴的：http://support.microsoft.com/kb/213730< /a> 并且我使用具有不同 POST 参数的不同 URL）
Sub URL_Post_Query()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://webservices.pcquote.com/cgi-bin/excel.exe", _
Destination:=Range("a1"))
.PostText = _
"QUOTE0=[""QUOTE0"",""Enter up to 20 symbols separated " & _
"by spaces.""]"
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub
我需要能够进行特别大的查询（例如，假设我正在获取 20,000 种证券的股票价格信息），并且当我这样做时因此，尽管查询成功完成，但 Excel 会“锁定”并且在工作时不会显示任何进度。
使用这种语法，有什么方法可以访问传入的数据流吗？这将通过两种方式提供帮助：我可以在收到数据时以较小的块处理数据，并且我可以创建一个进度表来向用户显示。
谢谢。
I am using Excel VBA's WebQuery, using something similar to this example:
(This example is actually copy-pasted from here: http://support.microsoft.com/kb/213730 and I am using a different URL with different POST arguments)
Sub URL_Post_Query()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://webservices.pcquote.com/cgi-bin/excel.exe", _
Destination:=Range("a1"))
.PostText = _
"QUOTE0=[""QUOTE0"",""Enter up to 20 symbols separated " & _
"by spaces.""]"
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub
I need to be able to make particularly large queries (for example, let's say I'm getting stock price information for 20,000 securities), and when I do so, Excel "locks up" and doesn't display any progress while it is working, although the query does successfully complete.
Using this syntax, is there any way to get access to the stream of data, as it is coming in? This would help in 2 ways: I could process data in smaller chunks as it is received, and I could create a progress meter to display to the user.
Thanks.
发布评论
评论（3）
如果您想要更多控制，您可以从 Web 查询切换到使用 xmlhttp 实例之类的方式：这可以异步使用（不是超级简单，但相当可行）：
http://www.dailydoseofexcel.com/archives/2006/10/09/async-xmlhttp-calls/
请注意，不能保证当您发送大量符号时 Web 服务会返回单独对每个符号的响应：服务器通常会缓冲内容直到整个响应完成。
您最好将报价分成多个请求，每个请求包含较少数量的符号。
If you want more control, you could switch from the web query to something like using an instance of xmlhttp: this can be used asynchronously (not super-simple, but quite do-able):
http://www.dailydoseofexcel.com/archives/2006/10/09/async-xmlhttp-calls/
Note there's no guarantee that when you send a large block of symbols the webservice returns the response for each symbol individually: it's common for servers to buffer content until the entire response is complete.
You might be better off chunking your quotes into multiple requests, each with a smaller number of symbols.
如果您想要完全访问 Web 调用的结果，您可以将 URL 作为另一个工作簿打开，而不是使用 Web 查询。
请参阅我对此问题的回答：当查询完成时，如何对来自 Excel Web 查询的数据进行后处理？
这种方法的权衡是您必须管理自己处理返回的数据 - Excel 不会将其放入给定的目的地。但听起来你觉得这样可以吗？
这是一个同步调用，但是你确实得到了一个进度条，所以用户体验更好。
在我们尝试了与您似乎一直在做的事情非常相似的事情之后，我们最终走上了这条路线。
If you want full access to the results of the web call, you could open the URL as another workbook instead of using a Web Query.
See my answer on this question: How can I post-process the data from an Excel web query when the query is complete?
The tradeoff of that approach is you have to manage processing the data you get back yourself - Excel won't put it in a given destination for you. But it sounds like that's maybe ok with you?
It's a synchronous call, but you do get a progress bar, so the user experience is better.
We ended up going this route after we tried something pretty similar to what you seem to have been doing.
如果您正在处理大型 XML 文档，那么您可能会发现通过 SAX 而不是 DOM 处理结果会使事情不太容易冻结。您可能会找到这个答案< /a> 到一个有用的相关问题
If you are working with a large XML document then you might find that processing the results via SAX rather than DOM makes things less prone to freezing up. You might find this answer to a related question to be of use