This site is hosted and sponsored by hyve.com specialists in Cloud Hosting UK and VMware Hosting. If you are interested in our services please call us for chat on 0800 612 2524

This code allows the user control of a spreadsheet whilst the sheet is calling a webservice 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 webpage 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> counterParty = (List<CounterParty>)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 webpage.

Back to VBA