Glasgow Taxonomic Name Server

Developed by Rod Page

Home

Queries

Find a name
Find from id
List synonyms
Show classification
Check names

Source

Related projects

Calling Web services from Microsoft Excel

Microsoft Excel can call external data using web services. Under Windows you need the Microsoft's SOAP Toolkit (see below), which enables Visual Basic macros to consume web services. Microsoft don't supply the SOAP Toolkit for Macintosh, but we can combine Visual Basic and Applescript to achieve the same result.

Microsoft Windows

This tutorial assumes you are using Microsoft Windows 98, ME, NT, 2000, or XP. I have tested this on Windows 2000 with SOAP Toolkit 3.0 and Excel 2000. Much of the what follows is based on the tutorial available at Web Continuum.

Step 1: Install SOAP Toolkit

Download Microsoft's SOAP Toolkit 3.0

Step 2: Create a Visual Basic module using the Visual basic Editor

Launch Excel and open the Visual Basic Editor by going to the Tools menu.

To make sure the VB Editor knows that you have the SOAP Toolkit installed, select Tools->References. Scroll down the list of libraries and pick "Microsoft Soap Type Library" (V3 if you have it).

Add a code module by choosing Insert->Module. Copy this code into your Excel VBA module

Option Explicit
   ' Excel VBA Function Wrapper to call TaxonNameService
   Public Function TaxonID(taxon As String) As Single
   Dim objSClient As MSSOAPLib30.SoapClient30 ' Remember to change this in project    references if using a different SOAP library
   Dim fResult As Single
   Set objSClient = New SoapClient30
   Call objSClient.mssoapinit(par_WSDLFile:="http://darwin.zoology.gla.ac.uk/~rpage/MyToL/www/soap/TaxonNameService.wsdl")
   fResult = objSClient.getTaxonID(taxon)
   Set objSClient = Nothing
   TaxonID = fResult
   End Function

Your screen should look something like this:

Now close the VB Editor.

Step 3: Using the function in Excel

You can now use the function TaxonID has you would any built-in Excel function. Select the cell you want the value displayed in, then go to the Insert menu and choose Function. In the Paste Function dialog box, scroll down to User Defined functions and you should see TaxonID listed:

Here is a screen shot of the result:

Mac Os X

I've tried this using Mac OS 10.2.8 ("Jaguar") and Excel X for Mac Service Release 1. Jesse Shank's article on searching Google from Microsoft Word showed me how to combine Visual basic and Applescript.

Step 1: Create a Visual Basic module using the Visual Basic Editor

Launch Excel and open the Visual Basic Editor by going to the Tools menu.

Add a code module by choosing Insert->Module. Copy this code into your Excel VBA module

Option Explicit
   ' Excel VBA Function Wrapper to call TaxonNameService
    Public Function TaxidFromAccession(accession As String) As Single
    Dim wURL, wBinding, Method
    wURL = "http://darwin.zoology.gla.ac.uk/~rpage/MyToL/www/soap/TaxonNameService.php"
    wBinding = "http://darwin.zoology.gla.ac.uk/~rpage/MyToL/www/soap/TaxonNameService/binding"
    Method = "getTaxidFromAccession"
    TaxidFromAccession = MacScript("set acc to " & Chr(34) & accession & Chr(34) & Chr(13) & "tell application " & Chr(34) & wURL & Chr(34) & Chr(13) & "set mn to " & Chr(34) & Method & Chr(34) & Chr(13) & "set sa to " & Chr(34) & Method & Chr(34) & Chr(13) & "set mns to " & Chr(34) & wBinding & Chr(34) & Chr(13) & "set params to {} " & Chr(13) & "set params to params & {accession:acc}" & Chr(13) & "set this_result to call soap {method name:mn, parameters:params, SOAPAction:sa, method namespace uri:mns}" & Chr(13) & "end tell" & Chr(13) & "set taxid to |Result| of this_result" & Chr(13) & "return taxid")
    End Function

This code uses the MacScript function to call Applescript, which performs the SOAP query. Note the Chr(34), which is a quote character, and Chr(13), which is a return. It is also important that there are no line breaks in the string passed parameter for MacScript.

The originl Applescript code is:

set acc to accession 
tell application wURL 
   set mn to Method 
   set sa to Method 
   set mns to wBinding 
   set params to {}
   set params to params & {accession:acc} 
   set this_result to call soap {method name:mn, parameters:params, SOAPAction:sa, method namespace uri:mns} 
   end tell 
set taxid to |Result| of this_result 
return taxid

Now close the VB Editor

Step 2: Using the function in Excel

You can now use the function TaxidFromAccession has you would any built-in Excel function (see the instructions for the Windows version of Excel).