Programming Excel with VBA and .NET

Use the Lists Web Service

SharePoint Services includes the Lists Web Service for getting at shared lists and their data directly. The Lists Web Service lets you perform tasks on the server that you cannot otherwise perform through Excel objects; you can use it to:

• Add an attachment to a row in a list

• Retrieve an attachment from a row in a list

• Delete an attachment

• Delete a list from a SharePoint server

• Look up a list GUID

• Perform queries directly on the shared list

To use a web service from Visual Basic:

1. Install the web Services Toolkit from Microsoft at www.microsoft.com/downloads.

2. Close and restart Excel.

3. Open the Visual Basic Editor and select Web References from the Tools menu. Visual Basic displays the Microsoft Office 2003 Web Services Toolkit dialog (Figure 14-8).

Figure 14-8. The Web Services Toolkit in action

The SharePoint Web Services reside in the _vti_bin folder of the SharePoint site. To use the Lists Web Service:

1. Display the Microsoft Office 2003 Web Services Toolkit as described earlier.

2. Select the Web Service URL option button and type the address of the address using this form: http://sharepointURL/_vti_bin/lists.asmx.

3. Click Search. The toolkit should find the Lists Web Service and display it in the Search Results list.

4. Select the checkbox beside Lists in Search Results and click Add.

5. The toolkit generates a proxy class named clsws_Lists and adds it to the current project. ...