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