Section 10.3. Using Web Services

10.3. Using Web Services

SharePoint web services allow remote applications to get and modify SharePoint sites and content. These services are more complete than the VBA object model and can be used from any web-service aware programming language such as Visual Basic .NET, C#, C++, VBA, Java, and so on.

Table 10-5 lists the web services SharePoint provides. All services except Admin.asmx are installed in the _vti_bin virtual folder, which maps to C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\60\ISAPI under the default SharePoint installation.

Table 10-5. SharePoint web services

Service	Use to
Administration ( Admin.asmx )	Manage SharePoint sites (for example, create or delete sites). This service is only installed for the SharePoint Central Administration site in the _vti_adm folder.
Alerts ( Alerts.asmx )	Get or delete alerts on a site.
Document Workspace ( Dws.asmx )	Manage document workspace sites and the data they contain.
Forms ( Forms.asmx )	Get forms used in the user interface when working with the contents of a list.
Imaging ( Imaging.asmx )	Create and manage picture libraries.
List Data Retrieval ( DspSts.asmx )	Perform queries against lists.
Lists ( Lists.asmx )	Work with lists and list data.
Meetings ( Meetings.asmx )	Create and manage meeting workspace sites.
Permissions ( Permissions.asmx )	Work with the permissions for a site or list.
Site Data ( SiteData.asmx )	Get metadata or list data from sites or lists.
Sites ( Sites.asmx )	Get information about the site templates for a site collection.
Users and Groups ( UserGroup.asmx )	Work with users, site groups, and cross-site groups.
Versions ( Versions.asmx )	Work with versions within a document library.
Views ( Views.asmx )	Work with views of lists.
Web Part Pages ( WebPartPages.asmx )	Get web part pages; get, add, delete, or change web parts .
Webs ( Webs.asmx )	Work with sites and subsites.

Detailed reference information about these web services and their methods is available in the SharePoint SDK.

10.3.1. From VBA

The Office 2003 object library provides objects for working with SharePoint document workspaces and document libraries. In general, you'll use those objects when programming SharePoint from VBA. However, there are some cases where you may need to use the SharePoint web services to access lower-level tasks not available through the object library.

To use a web service from VBA:

Install the Web Services Toolkit from Microsoft at www.microsoft.com/downloads.
Close and restart any running Office applications
Open the Visual Basic Editor and select Web References from the Tools menu. Visual Basic displays the Microsoft Office 2003 Web Services Toolkit references dialog (Figure 10-2)
Select the Web Service URL option button and type the address of the web service using this form: http:// sharepointURL /_vti_bin/ service .asmx (for example, http://wombat1/xlSPDemo/_vti_bin/Lists.asmx) .
Click Search. The Toolkit should find the web service and display it in the Search Results list .
Select the checkbox beside the service in Search Results and click Add .
The Toolkit generates a proxy class named clsws_ Service and adds it to the current project .

animal 10-2. Adding a web reference in VBA

The SharePoint server must authenticate the user before you can call any of the web service methods. If the user has not been authenticated, a "Maximum retry on connection exceeded" error occurs. In Visual Basic .NET or C# .NET, you authenticate the user from code by creating a Credentials object for the user. For example, the following .NET code passes the user's default credentials to a web service:

 wsAdapter.Credentials = System.Net.CredentialCache.DefaultCredentials

Unfortunately, you can't do that directly in VBA. Instead, you must use one of the following techniques to connect to the SharePoint server through the Office application:

Update or refresh a document that is shared on the server.
Insert an existing SharePoint list on an Excel worksheet. This can even be a dummy list placed on the server solely for the purpose of establishing connections.
Navigate to the SharePoint site in code.

Any of these techniques displays SharePoint's authentication dialog box and establishes a user session for the Office application. Afterward, you can call web service methods, and they will be authorized using the current session.

10.3.2. VBA Programming Tips

The generated proxy classes hard-code the site address as a constant in the generated proxy class modules:

 Private Const c_WSDL_URL As String = _       "http://wombat1/xlSPDemo/_vti_bin/lists.asmx?wsdl"

You can change the c_WSDL_URL constant to target other sites or subsites. SharePoint creates a _vti_bin virtual folder for any new web site. That folder mirrors _vti_bin at the top-level site.

One thing you will notice quickly when using the generated proxy classes is that the error reporting is minimal. When a method fails on the server side, you receive only a general error. To receive more detail, change the proxy class's error handler. The following code shows how to add details to the Lists web service error handler:

 Private Sub ListsErrorHandler(str_Function As String)         If sc_Lists.FaultCode <> "" Then             Err.Raise vbObjectError, str_Function, sc_Lists.FaultString & _  vbCrLf & sc_Lists.Detail ' Add detail  'Non SOAP Error         Else              Err.Raise Err.Number, str_Function, Err.Description         End If      End Sub

10.3.3. Working with Lists

One of the most useful scenarios for using web services in VBA is calling the Lists web service from Excel. The Lists web service lets you perform tasks on the server that you cannot otherwise perform through Excel objects. Specifically, you can use the List web service 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.
Perform queries.

The following sections demonstrate how to perform those tasks in VBA using the Lists web service.

10.3.3.1 Adding attachments

Use the Lists web service AddAttachment method to add a file attachment to a row in a list; then use GetAttachmentCollection to retrieve attachments from within Excel. For example, the following code attaches the image file joey.jpg to the second row of a shared list:

 ' Requires Web reference to SharePoint Lists.asmx     Dim lws As New clsws_Lists, src As String     src = ThisWorkbook.Path & "\joey.jpg"     dest = lws.wsm_AddAttachment("Excel Objects", "2", "joey.jpg", FileToByte(src))

The AddAttachment method's last argument is an array of bytes containing the data to attach. To convert the image file to an array of bytes, the preceding code uses the following helper function:

 Function FileToByte(fname As String) As Byte(  )         Dim fnum As Integer         fnum = FreeFile         On Error GoTo FileErr         Open fname For Binary Access Read As fnum         On Error GoTo 0         Dim byt(  ) As Byte         ReDim byt(LOF(fnum) - 1)         byt = InputB(LOF(fnum), 1)         Close fnum         FileToByte = byt         Exit Function     FileErr:         MsgBox "File error: " & Err.Description     End Function

10.3.3.2 Retrieving attachments

Use the Lists web service GetAttachmentCollection method to retrieve an attachment from a list. The GetAttachmentCollection method returns an XML node list that contains information about each attachment for the row. The following code retrieves the location of the file attached in the previous section:

 Dim lws As New clsws_Lists ' Requires Web reference to SharePoint Lists.asmx     Dim xn As IXMLDOMNodeList  ' Requires reference to Microsoft XML     Set xn = lws.wsm_GetAttachmentCollection("Excel Objects", "2")     ThisWorkbook.FollowHyperlink (xn.Item(0).Text)

Notice that the returned XML node list is a collection since rows can have multiple attachments. Since the preceding example only attached one file, this sample simply retrieves the first item from the node list. The Text property of this item is the address of the attachment on the SharePoint server.

10.3.3.3 Deleting attachments

Finally, it is very simple to delete an attachment using the DeleteAttachment method:

 Dim lws As New clsws_Lists  ' Requires Web reference to SharePoint Lists.asmx     lws.wsm_DeleteAttachment "Excel Objects", "2", _     SPSITE & "/Lists/Excel Objects/Attachments/2/joey.jpg"

Since DeleteAttachment requires the fully qualified address of the attachment, it is useful to save the address of each attachment somewhere on the worksheet or to create a helper function to retrieve the address from the SharePoint server, as shown here:

 Function GetAttachment(ListName As String, ID As String) As String         Dim lws As New clsws_Lists ' Requires Web reference to SharePoint Lists.asmx         Dim xn As IXMLDOMNodeList  ' Requires reference to Microsoft XML         Set xn = lws.wsm_GetAttachmentCollection(ListName, ID)         GetAttachment = xn.Item(0).Text     End Function

10.3.3.4 Performing queries

You don't commonly need to perform queries through the Lists web service. Most of the operations you want to perform on the list data are handled through the Excel interface or through the Excel list objects.

However, advanced applicationsor especially ambitious programmersmay use the Lists web service to exchange XML data directly with SharePoint. For instance, you may want to retrieve a limited number of rows from a very large shared list. In this case, you can perform a query directly on the SharePoint list using the GetListItems method. For example, the following code gets the first 100 rows from a shared list:

 Dim lws As New clsws_Lists ' Requires Web reference to SharePoint Lists.asmx     Dim xn As IXMLDOMNodeList  ' Requires reference to Microsoft XML     Dim query As IXMLDOMNodeList     Dim viewFields As IXMLDOMNodeList     Dim rowLimit As String     Dim queryOptions As IXMLDOMNodeList     rowLimit = "100"     Dim xdoc As New DOMDocument     xdoc.LoadXml ("<Document><Query /><ViewFields />" & _       "<QueryOptions /></Document>")     Set query = xdoc.getElementsByTagName("Query")     Set viewFields = xdoc.getElementsByTagName("Fields")     Set queryOptions = xdoc.getElementsByTagName("QueryOptions")     Set xn = lws.wsm_GetListItems("Shared Documents", "", query, _       viewFields, rowLimit, queryOptions)

The results are returned as XML. To see them, you can simply display the root node of the returned object as shown here:

 Debug.Print xn.Item(0).xml

The key to the preceding query is XML supplied the LoadXml method. You create conditional queries using the Query element and determine the columns included in the results using the ViewFields element. Perhaps the simplest way to create these queries is to write them as a text file in an XML editor (or Notepad), then load them from that file using the Load method shown here:

 xdoc.Load ("query.xml")

The query file takes this form:

 <Document>     <Query>        <OrderBy>            <FieldRef Name="Column 1" Asending="FALSE"/>         </OrderBy>        <Where>           <Gt>              <FieldRef Name="_x0031_" />              <Value Type="Value">6</Value>           </Gt>        </Where>     </Query>     <ViewFields>            <FieldRef Name="ID" />            <FieldRef Name="_x0031_" />            <FieldRef Name="_x0032_" />            <FieldRef Name="_x0033_" />     </ViewFields>     <QueryOptions>        <DateInUtc>FALSE</DateInUtc>        <Folder />        <Paging />        <IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns>        <MeetingInstanceId />        <ViewAttributes Scope="Recursive" />        <RecurrenceOrderBy />        <RowLimit />        <ViewAttributes />        <ViewXml />     </QueryOptions>     </Document>

Notice that the FieldRef elements sometimes use the internal SharePoint names to identify columnslists don't always use the titles displayed in the columns as column names. You can get the internal column names by examining the list's XML. To see the list's XML, use the GetList method, as shown here:

 Dim lws As New clsws_Lists         Dim xn As IXMLDOMNodeList ' Requires reference to Microsoft XML         Set xn = lws.wsm_GetList ("Shared Documents")         Debug.Print xn(0).xml

10.3.4. From .NET

Working with SharePoint web services from Visual Studio .NET is much the same as working from VBA, except you are using a somewhat different language (VB.NET or C#) and you use the .NET Framework objects rather than the Microsoft Soap and XML type libraries.

To use a web service from Visual Studio .NET:

Choose Project Add web reference, enter the URL of the web service, and choose Go. Visual Studio displays Figure 10-3 .
Create a new object from the class .
Set the object's Credentials property to the user's default credentials .
Call the web service methods from the object .

animal 10-3. Adding a web reference in .NET

For example, here is a very simple console application that gets all of the web sites on the SharePoint server and displays their descriptions:

 Module Module1         Sub Main(  )             ' Create a new object from the web service.             Dim webs As New wombat1.Webs             ' Pass the service the default credentials.             webs.Credentials = System.Net.CredentialCache.DefaultCredentials             Dim xdoc As New Xml.XmlDocument             ' Load the results in an XML document.             xdoc.LoadXml(webs.GetWebCollection.OuterXml)             ' Save the results             xdoc.Save("..\temp.xml")             ' Display results in the default XML editor.             Process.Start("..\temp.xml")         End Sub     End Module

The last line displays the resulting file, using whatever editor is registered on your system for displaying XML. On my system, that is Internet Explorer, which displays the result as shown in Figure 10-4.

animal 10-4. Displaying the result of the web service

Process.Start is the .NET equivalent of Shell ; using it to open documents in their default editors is a handy trick to know.

10.3.5. .NET Programming Tips

You can change the site that the web service acts on by changing the Web Reference URL property for the web service. You can also rename the namespace so it is more descriptive.

To set the web reference properties, select the item under Web References in the Solution Explorer. Then set the properties in the properties window as shown in Figure 10-5.

animal 10-5. Changing web reference properties

The .NET Framework contains many useful classes for working with XML and integrates its database support with XML. For instance, you can load XML directly into a DataSet and then use that object to easily iterate over elements or load the items into a data grid. The following code shows how to display the results from a web service in a data grid on a Windows form:

 Private Sub Form1_Load(ByVal sender As System.Object, _       ByVal e As System.EventArgs) Handles MyBase.Load         ' Create a new object from the web service.         Dim webs As New wombat1.Webs         Dim ds As New DataSet, str As String         ' Pass the service the default credentials.         webs.Credentials = System.Net.CredentialCache.DefaultCredentials         ' Get the list of sites.         str = webs.GetWebCollection.OuterXml( )         ' Read the string into a stream         Dim sr As New IO.StringReader(str)         ds.ReadXml(sr)         ' Display results.         dGrid.DataSource = ds.Tables(0)         sr.Close( )     End Sub     Private Sub Form1_Resize(ByVal sender As Object, _       ByVal e As System.EventArgs) Handles MyBase.Resize         dGrid.PreferredColumnWidth = Me.Width / 2         dGrid.Width = Me.Width         dGrid.Height = Me.Height     End Sub

Figure 10-6 illustrates the results from this very simple program.

animal 10-6. Quick results from using data sets with XML

Often in .NET you can assemble a few very powerful classes to create quick results with a few lines of code. In fact, Form1_Load is deliberately verbose in order to separate the steps. Here's how the code would more typically be written:

 Private Sub Form1_Load(ByVal sender As System.Object, _       ByVal e As System.EventArgs) Handles MyBase.Load         ' Create a new object from the web service.         Dim webs As New wombat1.Webs, ds As New DataSet         ' Pass the service the default credentials.         webs.Credentials = System.Net.CredentialCache.DefaultCredentials         ' Shortened form of load/read.         ds.ReadXml(New IO.StringReader(webs.GetWebCollection.OuterXml(  )))         ' Display results.         dGrid.DataSource = ds.Tables(0)     End Sub

10.3.6. From ASP.NET

When using SharePoint web services from ASP.NET applications on other servers, you must still provide valid credentials of authorized users. In most cases, you'll want to do that by impersonating the current user and passing his credentials to the service. To impersonate the current user in an ASP.NET web application, change the Web.config of the application as shown here:

 <authentication mode="Windows" />     <authorization>         <deny users="?" /> <!-- Deny unauthenticated users -->     </authorization>     <identity impersonate="true" />

These settings enforce Windows authentication and then run the ASP.NET code under the current user's identity. You can use other authentication and authorization techniques, but the concept is the samethe code invoking the web service must impersonate a user authorized to make the request on the SharePoint server.

Once the code is impersonating an authorized user, invoking the web service is basically the same as shown previously with the addition that the DataGrid web control requires a DataBind method to load data:

 Dim ds as DataSet     Private Sub Page_Load(ByVal sender As System.Object, _       ByVal e As System.EventArgs) Handles MyBase.Load         ' Create a new object from the web service.         Dim webs As New wombat1.Webs         ' Pass the service the default credentials.         webs.Credentials = System.Net.CredentialCache.DefaultCredentials         ' Shortened form of load/read.         ds.ReadXml(New IO.StringReader(webs.GetWebCollection.OuterXml(  )))         ' Display results.         dGrid.DataSource = ds.Tables(0)         ' Bind data (required for DataGrid web control).         dGrid.DataBind(  )     End Sub

10.3.7. Using the Admin Service

The Admin.asmx web service is not installed on the root SharePoint site, but rather is provided as part of the Central Administration site which SharePoint installs on a separate port number. To create a web reference for this service, find the port number for the Central Administration site. To do that, open the site in the browser and record the port number used in the Address bar. Then specify the port number and the _vti_adm folder in the URL of the web reference. For example:

 http://wombat1:2933/_vti_adm/Admin.asmx

The Admin service provides two main methods: CreateSite and DeleteSite . These methods require administrative privileges to use, but are otherwise straightforward. For example, the following code adds a Delete site feature to the previous ASP.NET data grid example:

 Private Sub dGrid_DeleteCommand(ByVal source As Object, _       ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) _       Handles dGrid.DeleteCommand         ' Display the delete panel.         pnl.Visible = True         ' Show the site to delete.         lbl.Text = ds.Tables(0).Rows(e.Item.ItemIndex).Item(1)     End Sub     Private Sub cmdDelete_Click(ByVal sender As System.Object, _       ByVal e As System.EventArgs)         ' Create admin object.         Dim adm As New wombat1Adm.Admin         ' If an item is selected         If lbl.Text <> "" Then             Try                 ' Delete the site.                 adm.DeleteSite(lbl.Text)                 ' Display success.                 status.text = lbl.Text & " deleted."             Catch ex As Exception                 ' Otherwise, note the error.                 status.text = "Error: " & ex.Message             End Try         End If         ' Hide panel.         pnl.Visible = False     End Sub     Private Sub cmdNo_Click(ByVal sender As Object, _       ByVal e As System.EventArgs) Handles cmdNo.Click         ' Clear the site and hide the panel.         lbl.Text = ""         pnl.Visible = False     End Sub

In addition to the data grid, the preceding code uses some controls defined in the following ASP.NET web form:

 <form id="Form1" method="post" runat="server">         <asp:Panel id="pnl" runat="server" Visible="False">           Are you sure you want to delete         <asp:Label id="lbl" runat="server"></asp:Label>?         <asp:Button id="cmdYes" runat="server" Text="Yes"></asp:Button>         <asp:Button id="cmdNo" runat="server" Text="No"></asp:Button></asp:Panel>         <asp:Label id="status" runat="server"></asp:Label><br>         <asp:DataGrid id="dGrid" runat="server" Width="432px" AllowSorting="True">             <Columns>                 <asp:ButtonColumn Text="Delete"                   CommandName="Delete"></asp:ButtonColumn>             </Columns>         </asp:DataGrid>     </form>

At runtime, the user can select a site from the grid and delete it as shown in Figure 10-7.