[Back]
Calling the Traits webservice from Microsoft Excel 2007
This tutorial assumes you are using any Microsoft Windows Operating System with Office 2007 installed
Step 1: Install the Microsoft Office XP Web Services Toolkit 2.0
Download Microsoft Office XP Web Services Toolkit 2.0
Note: although the use of the Web Services Toolkit is deprecated, it will work for Office 2007.
If you want to use the Microsoft supported method, follow this tutorial
Step 2: Enable the developer tab in Excel
Start Excel, click on the Office button, choose "Popular" and check "Show Developer tab in the Ribbon".
Step 3: Lauch the Web Service References Tool
Open Visual Basic by going to the first option on the developer tab on the Ribbon.
In Visual Basic, go to Tools > Web Service References. Selecting this brings up this dialog:
Step 4: Generate the VBA code
Select the checkbox Web Service URL and enter this value: https://marinespecies.org/traits/aphia.php?p=soap&wsdl=1
Then click 'Search', a search result will appear in the top right. Check the box to it's left.
Clicking the Add button will make Excel generate VBA code for the service.
Step 5: Create a Visual Basic module using the Visual basic Editor
Add this piece of code by choosing Insert->Module. Copy this code into your Excel VBA module
Option Explicit
'Excel VBA Function to call the AphiaNameService
Public Function getAphiaID(taxon As String) As Single
Dim aphia As New clsws_AphiaNameService
getAphiaID = aphia.wsm_getAphiaID(taxon, True)
End Function
Your screen should look something like this:
Close the VB Editor.
Step 3: Using the function in Excel
You can now use the function getAphiaID as 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 getAphiaID listed:
The result should be something like this:
Download this example.
Download elaborate example for getAphiaRecordById.
Credits for this tutorial go to Simon St.Laurent (Oreilly) and Christopher Condit (San Diego Supercomputer Center).
|