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

问题描述

为了从Excel中的webservice获取数据，我创建了一个excel addin提供函数来获取它;用户只需要输入单元格：
= sendRequest（http://webservice.com）

我有两个excel文件以两种方式演示发送请求：1. 同步和2. 异步

在 sync 方法中，该函数可以发送请求获取数据正常。但是，如果我们有100,200个单元格，那么它会使Excel花费大量的时间等待;这也使得Exel不被重新定位。

我目前的解决方案是将 async 方法用作以下代码

 公共函数sendAsyncRequest（URL）
'其他语句
 
'异步获取一些东西。 
 xmlHttpRequest.OpenGET，URL，True 
 xmlHttpRequest.send 
 sendAsyncRequest = xmlHttpRequest.responseText 
结束函数
  

但是，单元格的值是0而不是repsone文本。

我必须使用我的处理程序类将 OnReadyStateChange 绑定到 xmlHttpRequest对象将响应文本设置为单元格。但是，它也清除细胞的公式。

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

$ b $我也欢迎另一个解决方案来发送请求，并在 async 方法下获取返回值。

解决方案

由于解决方案的缺点在于此处，获取异步返回值的Propper方式功能是1）缓存您的请求的url =>返回值到一个集合/ dicitonary，然后2）刷新您的公式

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")

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

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.

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

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

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.

So my question is How to change the display text of cell without changing its formula?

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

解决方案

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屋！