如何VBA发送异步XMLHTTP请求？ [英] How to VBA sends an Async XMLHTTP request?

问题描述

要在Excel web服务中获取数据，我创建一个Excel插件提供的功能得到它;用户只需要在类型细胞：
= sendRequest将（http://webservice.com）

To get data from a webservice in Excel, I have created an excel addin provide function to get it; user just need type in cell: =sendRequest("http://webservice.com")

我有2个Excel文件，以向世人证明两种方法发送请求：1的同步和2的异步

I have 2 excel files to demostrate the sending request in two methods: 1.synchronous and 2.asynchronous

在同步方法，就可以发送请求和normaly获取数据。但是，如果我们的有100个，200个细胞调用它，它会采取Excel中大量的时间等待;这也使得Exel的是不会respoding 。

In sync method, the function can send request and get data normaly. But, if we have 100, 200 cells that call it, it will take Excel a huge amount of time waiting; which also makes Exel to be not respoding.

我给这家目前的解决方案是使用异步方法的低于code

My current solution for this is to use async method as below code

Public Function sendAsyncRequest(URL)
    'other statement

    ' Get some stuff asynchronously.
    xmlHttpRequest.Open "GET", URL, True
    xmlHttpRequest.send
    sendAsyncRequest = xmlHttpRequest.responseText
End Function

但是，单元格的值常是是repsone文字为零0代替。

But the cell's value is alway is zero 0 instead of repsone text.

我必须用我的处理程序类与的onreadystatechange xmlHtt prequest对象来设置响应绑定文本单元格。不过，它也清除单元格公式。

I must use my handler class to bind it with OnReadyStateChange of xmlHttpRequest object to set response text into cell. But then, it also clears the cells' formulas.

所以我的问题是如何更改单元格的显示文本，而不改变其配方？

我也欢迎其他解决方案，以发送请求并获得返回值在异步方法。

I also welcome another solution to send request and get return value under async method.

推荐答案

作为解决方案的缺点说这里，在propper方式来获得异步返回值的功能是1）的缓存您请求的URL =＆GT;返回的值来收集/ dicitonary，然后2）的刷新您的公式

As the downside of the solution stated here, the propper way to get the async return value for the function is to 1) cache your request's url => returned value to a collection/dicitonary, and then 2) refresh your formula

这篇关于如何VBA发送异步XMLHTTP请求？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！