Asynchronous Web Service Call From VBA

Category: ASP

This code allows the user control of a spreadsheet whilst the sheet is calling a web service which takes a long time to return data.

• Create a Module in Excel or VB
• Add a reference in Excel to Microsoft XML Dom

Private d As Class1

Sub doit()

    Set d = New Class1
    d.Execute
  
End Sub

• Create a Class called Class1 in Excel or VB

Private WithEvents objXML As DOMDocument

Public count As Long
Public done As Boolean

Public Sub Execute()
Set objXML = New DOMDocument
objXML.async = True
Dim loaded As Boolean

    
    loaded = objXML.Load("http://WEBSERVER/tradeblotter/getcounterparties.aspx")
    MsgBox "Getting Counterparties"
    count = 1
    done = False
    
    If objXML.parsed Then
        Call objXML_ondataavailable
    End If
End Sub

Private Sub objXML_ondataavailable()

If Not done Then
    MsgBox "Got Counterparties"
    done = True
    DoEvents
End If

    Dim retNode As IXMLDOMNode
    For intRow = count To objXML.FirstChild.childNodes.Length
       Set retNode = objXML.FirstChild.childNodes(intRow - 1)
       Sheet2.Cells(intRow, 1).Value = retNode.Attributes.getNamedItem("name").Text
       Sheet2.Cells(intRow, 2).Value = retNode.Attributes.getNamedItem("label").Text
    Next


 count = objXML.FirstChild.childNodes.Length - 1
End Sub

• Create a web page that returns XML and deploy to a web server

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Threading;
using Calyon.Sky.Commando;
using System.Web.UI.MobileControls;
using System.Xml;
using System.Collections.Generic;

public partial class getcounterparties : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        BlotterData blotterData = new BlotterData("");

        List counterParty = (List)blotterData.GetAllCounterPartiesWithoutRatings();

        XmlDocument xmlDoc = new XmlDocument();
        XmlNode xmlCPS = xmlDoc.AppendChild(xmlDoc.CreateElement("CPS"));
        foreach (CounterParty cp in counterParty) {
            XmlNode xmlCp = xmlCPS.AppendChild(xmlDoc.CreateElement("CP"));
            xmlCp.Attributes.Append(xmlDoc.CreateAttribute("name")).Value = cp.m_Name;
            xmlCp.Attributes.Append(xmlDoc.CreateAttribute("label")).Value = cp.m_Label;
        }
        
        Response.ContentType = "text/xml";
        Response.Write(xmlDoc.InnerXml.ToString());

    }

}

• Call the Function in the Module and wait for the data to be returned. The async code only works if there is a delay in the response from the web page.

VBA