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