Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
2.7k views
in Technique[技术] by (71.8m points)

excel - How to VBA sends an Async XMLHTTP request?

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

This is my solution file for your question; indeed it is updated from your async test file.

Based on this discusion, you can change the display text of cell without changing its formula by using Range(yourCellAddress).NumberFormat = "0;0;0;""Value to display"""

So to your question, the solution is

  1. In your sendAsyncRequest function replace the return line as

    sendAsyncRequest = "anything other than numbers"

  2. In your ReadyStateChangeHandler sub, replace

    Application.Range(cellAddress).Value = XMLHttpReq.responseText 'return responseText to cell

by

cellDisplayText = XMLHttpReq.responseText
Range(cellAddress).NumberFormat = "0;0;0;""" & cellDisplayText & """"

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...