VBA webservcie call takes time and excel goes to Not responding

Question

I have this web-service call in excel through VBA.

Req.Open "POST", MyURL, False
Req.setRequestHeader "Content-Type", "text/xml"

Req.send (sReq)

In this Req.send (sReq) takes time to respond when sReq length is too much. During this time excel goes not responding and then comes back to live and proceed to next line in the code.

Can you suggest how can I stop my excel going to Not responding on this step ?

Answer 1

Unfortunately this is not possible with the out of the box Excel/VBA.

To keep excel responsive during the web request you would need it to execute asynchronously (on a separate thread) but since VBA runs on a single thread this is not possible.

You could however write a VSTO (Visual Studio Tools for Office) add in for excel using VB.NET or C#.NET, this could add custom ribbon tab where you can put a button to initiate your web request asynchronously.