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

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

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


  • 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

Add new attachment

Only authorized users are allowed to upload new attachments.
« This page (revision-1) was last changed on 24-May-2017 15:30 by UnknownAuthor
G’day (anonymous guest) My Prefs
  • View Page Source
  • This clear IPSec security association,
    clear ipsec sa peer X.X.X.X

All Pages

Page views: 2656

Private Tomcat




SQL Server




Web Mail

Windows Plesk

Linux Plesk




Persits ASPUpload

Wiki Help

Referring Pages:

JSPWiki v2.8.1