我可以从Excel VBA Web查询获取流？ [英] Can I get the stream from an Excel VBA Web Query?

问题描述

我正在使用Excel VBA的WebQuery，使用类似于这个例子的东西：
（这个例子实际上是从这里复制粘贴： http://support.microsoft.com/kb/213730 ，我使用不同的POST参数不同的URL）

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

我需要能够做出特别的大量查询（例如，假设我正在获得20,000证券的股票价格信息），而当我这样做时，Excel锁定，并且在运行时不显示任何进度，尽管查询成功完成。

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.

谢谢。 / p>

Thanks.

推荐答案

如果你想要更多的控制，你可以从web查询切换到使用xmlhttp的一个实例：这可以使用异步（不是超简单，但相当可行）：

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/

请注意，当您发送大块符号时，Web服务将单独返回每个符号的响应：服务器通常会缓冲内容，直到整个响应完成。

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.

这篇关于我可以从Excel VBA Web查询获取流？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！