Web Services: Via VBA or equivalent

Accessing the Nematrian website's web services in Microsoft Excel (and other Microsoft Office components) using VBA

[this page | pdf | references | back links]

Using a spreadsheet which already has in-built access to these web services

Calling Nematrian web functions from within Microsoft Excel or equivalents from other providers is very easy to do if you already have access to a spreadsheet that has been set up to give you this access. The spreadsheet library include the facility to download spreadsheets that illustrate how to access many of the functions in the Nematrian function library as do the pages describing individual web functions. These downloadable spreadsheets generally have embedded within them the entire Nematrian function library.

Building a spreadsheet from scratch: Part I

It is also not difficult to build such a spreadsheet yourself. If you can get the example VBA code set out below to work then you should be able to use any Nematrian web service without too much difficulty.

Public Sub TestResultsFromNematrian()
Dim sEnv As String
Dim xmlhtp As New XMLHTTP60
Dim xmlDoc As New DOMDocument60
Dim Node As IXMLDOMNode
Dim NodeList As IXMLDOMNodeList
Dim Ans1 As String
Dim Ans2 As String
sEnv = "<?xml version=""1.0"" encoding=""utf-8""?>" _
& "<soap:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance""" _
& " xmlns:xsd=""http://www.w3.org/2001/XMLSchema""" _
& " xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">" _
& "<soap:Body><ComplexSum xmlns=""http://www.nematrian.org/"">" _
& "<InputDouble1>1</InputDouble1><inputDouble2>1</inputDouble2>" _
& "<InputString1>a</InputString1><inputString2>b</inputString2>" _
& "</ComplexSum></soap:Body></soap:Envelope>"
With xmlhtp
.Open "post", "http://www.nematrian.com/WebServices.asmx?op=ComplexSum", False
.setRequestHeader "Host", "www.nematrian.com"
.setRequestHeader "Content-Type", "text/xml; charset=utf-8"
.setRequestHeader "SoapAction", "http://www.nematrian.org/ComplexSum"
.setRequestHeader "Accept-encoding", "zip"
.send (sEnv)
xmlDoc.LoadXML .responseText
End With
Set NodeList = xmlDoc.ChildNodes(1).ChildNodes(0).ChildNodes(0).ChildNodes(0).ChildNodes
Ans1 = NodeList(0).nodeTypedValue
Ans2 = NodeList(1).nodeTypedValue
MsgBox "1 + 1 = " & Ans1 & " and 'a' & 'b' = '" & Ans2 & "'"
End Sub

If you are comfortable that you understand this code and that making use of it will not compromise you or your computer then you could try to insert the above code into a blank VBA module in a blank Excel spreadsheet.

To do this, you would first open Excel and create a new (blank) spreadsheet. Then you would open VBA within Excel by, for example, pressing the ALT and the F11 keys down simultaneously. A VBA Project referring to the worksheet should appear somewhere in the list of VBA Projects on the left hand side, but it will probably not have any VBA modules within it. So, you would first insert a blank module into the Project that seems to be linked to your blank worksheet (using Insert - Module not Insert – Class Module) and then copy and paste this code into this module.

You also need to include a reference to a Microsoft XML library. You can do this by using the Tools - References facility within VBA. This facility typically opens up a long list of possible references that you could include, and checkboxes alongside ones you might include. You would need to tick one called something like Microsoft XML, v6.0, although earlier (and possibly later) versions also seem to work fine (although you may then have to replace the “60” with some other number at the end of the lines involving Dim xmlhtp As New XMLHTTP60 and Dim xmlDoc As New DOMDocument60.

Next try running the TestWebService macro shown above. Several possible things might happen, e.g.:

(a) Excel/VBA may refuse to allow macros to run. This means that the macro functionality within Excel is disabled. It will need to be enabled before you can make use of the web service. See the Excel Help facility to work out how to do this.

(b) If macros are enabled then Excel/VBA may show a user-defined type not defined compile error and simultaneously highlight the text xmlhtp As New XMLHTTP60 and/or xmlDoc As New DOMDocument60. This indicates that you haven’t included a reference as above to one of the Microsoft XML libraries.

(c) If you have enabled macros and added a reference to one of the Microsoft XML libraries then the macro should run. Assuming the website is working properly, you should get a message box popping up showing 1+1 = 2 and ‘a’ & ‘b’ = ‘ab’. Well done, you have called the Nematrian website using a ‘complex’ web service from within Microsoft Excel! All other Nematrian web services can be accessed in a similar type of manner, except that they generally also require you to provide a valid SessionId.

Building a spreadsheet from scratch: Part II

Using more complicated Nematrian web services from within Microsoft Excel is plain sailing once you have got to the end of Part I, as long as you use the Nematrian Web Service Example Code Generator. This creates all the VBA code you would need to access any available Nematrian web function. Even easier, of course, is to download one of the example spreadsheets that already has embedded within it the relevant elements to allow it to access the Nematrian online web function library.

To use most Nematrian web functions you will need to obtain a Session Id. You can either get one by logging in to the website or you can obtain a temporary SessionId from the menu at the top of the website.

More advanced programmers wanting to understand the structure of what is returned from the Nematrian website when you call a Nematrian web function may find this link helpful.

Using other types of spreadsheet

It is not difficult to create equivalent spreadsheets in, say, OpenOffice as long as you alter the macros that they call so that they send http requests to the Nematrian website and receive them back in a manner understood by OpenOffice. See individual web function description pages for more details.

Warning

Users should not insert software generated by the website into their own programs or spreadsheets unless they understand what it does and are sure that it will not damage or corrupt any of their existing work.

The Nematrian website takes no responsibility for losses arising from use of code sourced from this page or the from the web service example code generator facility.

Please bear in mind that it is possible that hackers may tamper with the Nematrian website and introduce malicious code in the hope that you will run this code assuming that it will work as you expect. If you have any doubts about whether the code you see on this page may be malicious then please refer to an expert.

Use of the Nematrian website, including its web services, is subject to the terms of the Nematrian Web Services Software License Agreement. By making use of the site, including its web services functionality, you agree to be bound by this Agreement.

NAVIGATION LINKS
Contents | Prev | Next

Desktop view | Switch to Mobile