[Back]

Calling the WoRMS webservice from Microsoft Excel XP/2003

This tutorial assumes you are using any Microsoft Windows Operating System with Office XP or 2003 installed

Step 1: Install the Office Web Services Toolkit for your Office version

• Microsoft Office XP Web Services Toolkit 2.0
• Microsoft Office 2003 Web Services Toolkit 2.01

Step 2: Lauch the Web Service References Tool

Launch Excel and open the Visual Basic Editor by going to the Tools->Macro-> Visual Basic Editor.

In the Visual Basic Editor, go to Tools > Web Service References. Selecting this brings up this dialog:

Step 3: Generate the VBA code

Select the checkbox Web Service URL and enter this value: https://www.marinespecies.org/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 4: 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.

Credits for this tutorial go to Simon St.Laurent (Oreilly).