Using Microsoft Windows SharePoint Services with the Microsoft Office System

 

Mike Gunderloy
Lark Group, Inc.

December 2003

Applies to:
    Microsoft® Windows® SharePoint™ Services
    Microsoft Office SharePoint Portal Server 2003
    Microsoft Office Access 2003
    Microsoft Office Excel 2003
    Microsoft Office System

Summary: Learn how to programmatically interact with Microsoft SharePoint Products and Technologies from applications in the Microsoft Office System including how to use the SharePoint list feature, Web service, and document libraries programmatically from Access and Excel. (16 printed pages)

Contents

Introduction
Introduction to Windows SharePoint Services
Using SharePoint Lists
Using the Web Service Interface of Windows SharePoint Services
Documents and Windows SharePoint Services
Conclusion

Introduction

One of the major themes for the Microsoft® Office System is increased support for collaboration from within applications in the Microsoft Office System. Such features as Document Workspace sites and Meeting Workspace sites enable users to share data seamlessly, cooperate on tasks, and exchange e-mail and instant messages without leaving their familiar applications in the Microsoft Office System. Microsoft Windows® SharePoint™ Services enables this collaboration. Microsoft Office SharePoint Portal Server 2003 builds on this collaboration to offer an even richer set of features. In addition to deep user interface integration, the Microsoft Office System integrates with Microsoft SharePoint Products and Technologies through a variety of APIs.

Note   The information in this article also applies to Microsoft Office SharePoint Portal Server 2003, which is built on the Windows SharePoint Services platform.

Introduction to Windows SharePoint Services

Windows SharePoint Services allows you to communicate, share documents, and work together on projects using nothing more than a Web browser.

Installing Windows SharePoint Services

To make use of Document Workspace sites in the Microsoft Office System, you must install Microsoft Windows SharePoint Services or Microsoft SharePoint Portal Server 2003. To install Windows SharePoint Services, you must have a server meeting the minimum prerequisites:

  • Windows Server 2003 operating system
  • NTFS file system
  • Microsoft Internet Information Services (IIS) 6.0 installed in its default Worker Process Isolation Mode
  • ASP.NET installed

For a complete list of pre-requisites, see the Microsoft Windows SharePoint Services Administrators Guide.

Microsoft Windows SharePoint Services is available for download from the Microsoft Download Center.

Windows SharePoint Services Architecture

Windows SharePoint Services is built on the twin foundations of Microsoft SQL Server (or MSDE), and ASP.NET. Windows SharePoint Services includes a set of Web Parts, which are ASP.NET server controls. Web Parts can display lists or images, retrieve stock quotes or weather forecasts, and perform a host of other tasks. You add Web Parts to Web Part zones on Web Part Pages. You can build and modify Web Part Pages in an HTML editor such as Microsoft Office FrontPage 2003 or directly in the browser through the Windows SharePoint Services user interface (UI).

Using SharePoint Lists

Using the Microsoft Office System you can interact with Windows SharePoint Services data programmatically. A good starting point is with support in Microsoft Office Access 2003 of SharePoint lists. From within Access, you can treat a SharePoint list just like any other data source. You can import data from Windows SharePoint Services, export Access data to a SharePoint site, or link Windows SharePoint Services data to an Access database.

As with other data sources, Access uses the DoCmd.TransferDatabase method to interact with SharePoint lists. For example, to export a table to a Windows SharePoint Services database, you can use this syntax:

DoCmd.TransferDatabase acExport, "Windows SharePoint Services", _
 "http://server_name/sites/site_name", _
 acTable, "Customers", "Customers"

The previous example exports the Customers table from the current database to a SharePoint list, also named Customers, on the specified SharePoint site.

To import a SharePoint list to Access, or to add a link to a SharePoint list from an Access database, you can also use the TransferDatabase method but the syntax is somewhat more complex. Here's a Microsoft Visual Basic for Applications (VBA) command to add a link to a SharePoint list named Parts:

DoCmd.TransferDatabase acLink, "Windows SharePoint Services", _
 "WSS;HDR=NO;IMEX=2;" & _
 "DATABASE=http://server_name/sites/site_nam;" & _
 "LIST={800BE2B7-FA3C-4CFC-BBB3-8500C4EDCF22};" & _
 "VIEW=;RetrieveIds=Yes;TABLE=Parts", acTable, , _
 "Parts"

The difficulty in constructing this command is that Windows SharePoint Services uses GUIDs to identify lists. For example, that's how it stores ten different Contact lists in the same MSDE database. When you use the Access UI to import or add a link data in Windows SharePoint Services, it matches the names to the GUIDs. When you interact with the Jet engine directly through VBA, you would need to match them manually. The long string in the example that starts with "Windows SharePoint Services" and ends with "TABLE=Parts" identifies a particular SharePoint list.

To programmatically import or add links to data in Windows SharePoint Services, there are two ways to identify the correct GUID. The easy way is to add a link manually between the desired SharePoint list and the Access database, and then open the linked table in design view; the Description property of the linked table contains the necessary linking string. Obviously, this is less than useful when linking to arbitrary lists. You can also use the Windows SharePoint Services Web service interface to Windows SharePoint Services, which exposes a Lists.GetList method. This method gets the name of a list on a specified SharePoint site and returns detailed information, including the GUID that identifies the list. The Windows SharePoint Services Web service interface is discussed in more detail later in this document.

If you created a link between a SharePoint list and an Access database, you can work with it just like any other table. You can execute SQL statements to retrieve or change data, or open a Recordset and manipulate the list programmatically. Access treats the SharePoint list similar to another table and all of the table-oriented code you already know works. An optimistic locking model coordinates concurrency issues such as when two users work with the same data at the same time between Windows SharePoint Services and Access.

Microsoft Office Excel 2003 also integrates with SharePoint lists by implementing an extension to the Excel 2003 object model to make working with lists distinct from other activities. The Worksheet object in Excel is extended with a ListObjects collection that contains individual ListObject objects. The ListObject contains collections of ListColumns and ListRows and the individual ListColumn and ListRow objects have properties that tie them to Excel Range objects.

By default, the ListObjects collection contains native Excel 2003 lists. But you can hook one of these lists up to a SharePoint site by calling the Publish method of the corresponding ListObject object.

For example, consider the worksheet shown in Figure 1. To create a list from the rectangular area extending from A1 to C8, you could run the following VBA code:

Public Sub PublishList()
    ' Get the collection of lists for the active sheet
    Dim L As ListObjects
    Set L = ActiveSheet.ListObjects
    ' Add a new list
    Dim NewList As ListObject
    Set NewList = L.Add(xlSrcRange, Range("A1:C8"), , True)
    NewList.Name = "PartsList"
    ' Publish it to a SharePoint site
    NewList.Publish Array("http://server_name/sites/site_name", _
     "NewParts"), True
End Sub

Figure 1. A worksheet range to be published as a list (click picture to see larger image)

When adding the list, the third parameter indicates that this particular list has headers. When the list is published, Windows SharePoint Services uses these headers as column names for the list, as shown in Figure 2.

Figure 2. The range exported as a SharePoint list (click picture to see larger image)

The second argument to the Publish method of the ListObject object indicates that this list is linked to the SharePoint list. This enables the use of the Refresh and UpdateChanges methods to keep the two versions of the list synchronized.

The Refresh method copies the current schema and data from the SharePoint list to the worksheet. For example:

Public Sub RefreshList()
    ActiveSheet.ListObjects("PartsList").Refresh
End Sub

Any unsaved changes you make to the data in the worksheet is overwritten when you call the Refresh method.

The UpdateChanges method is more complex. If a value in the worksheet changes, UpdateChanges copies it to the SharePoint list. If a value in the SharePoint list changes, the method copies the change to the worksheet. But what happens if both values change? The method detects the conflict, and acts on it according to a parameter that you supply. For example, you can leave the decision to the user by calling the UpdateChanges method this way:

Public Sub UpdateList()
    ActiveSheet.ListObjects("PartsList").UpdateChanges (xlListConflictDialog)
End Sub

With the xlListConflictDialog constant supplied, Excel displays the dialog box shown in Figure 3 if it detects any conflicts between the local version of the data and the version on the SharePoint list. The user decides how to resolve these conflicts.

Figure 3. Resolving an update conflict between Excel and a SharePoint list (click picture to see larger image)

You can control the behavior of the UpdateChanges method by specifying different constants:

  • xlListConflictDiscardAllConflicts indicates to accept the version of the data stored on the SharePoint list
  • xlListConflictError indicates to raise an error if a conflict occurs
  • xlListConflictRetryAllConflicts indicates to overwrite the version of the data stored on the SharePoint list

Using the Web Service Interface of Windows SharePoint Services

The Web Service interface of Windows SharePoint Services provides added functionality and options for integration. Table 1 provides an overview of the various Web Services that are available.

Table 1. Web services available in Windows SharePoint Services

Service Web Reference URL Description
Administration http://server_name:5966/_vti_adm/Admin.asmx Administrative methods for managing a deployment of Microsoft Windows SharePoint Services, such as for creating or deleting site collections.
Alerts http://server_name/_vti_bin/Alerts.asmx Methods for working with alerts for list items in a SharePoint site.
Data Retrieval Service http://server_name/_vti_bin/DspSts.asmx Methods for retrieving schemas and data
Document Workspace http://server_name/_vti_bin/DWS.asmx Methods for managing Document Workspace sites and the data they contain
Forms http://server_name/_vti_bin/Forms.asmx Methods for returning forms used in the user interface when working with the contents of a list
Imaging http://server_name/_vti_bin/Imaging.asmx Methods that enable you to create and manage picture libraries
Lists http://server_name/_vti_bin/Lists.asmx Methods for working with lists and list data
Meetings http://server_name/_vti_bin/Meetings.asmx Methods that enable you to create and manage Meeting Workspace sites
Permissions http://server_name/_vti_bin/Permissions.asmx Methods for working with Windows SharePoint Services security
Site Data http://server_name/_vti_bin/SiteData.asmx Methods used by search services to extract and crawl data from SharePoint sites.
Sites http://server_name/_vti_bin/Sites.asmx Method for returning information about the collection of site templates on the virtual server.
Users and Groups http://server_name/_vti_bin/UserGroup.asmx Methods for working with users, site groups, and cross-site groups
Versions http://server_name/_vti_bin/versions.asmx Methods for working with file versions
Views http://server_name/_vti_bin/Views.asmx Methods for working with views of lists
Web Part Pages http://server_name/_vti_bin/WebPartPages.asmx Methods to send information to and retrieve information from XML Web services.
Webs http://server_name/_vti_bin/Webs.asmx Methods for working with sites and subsites

Note   SharePoint Portal Server provides several additional Web services. For more information, see the Microsoft SharePoint Products and Technologies Software Development Kit (SDK).

Each of these Web services exposes several methods that you can use from client applications. By browsing to the Web Reference URL using Internet Explorer, you can see the details of these methods. For example, Figure 4 shows the methods in the Lists Web service. If you browse on the computer where Windows SharePoint Services is installed, you can also interactively test the methods from the browser.

Figure 4. Exploring the Lists Web service

As an example of what you can do with the Web services included with Windows SharePoint Services, create a macro in Microsoft Office Word 2003 that retrieves all of the lists from a particular SharePoint site, together with the GUIDs that identify those lists. Although the macro converts this information to a table in Word, you could also use it in Access to determine the necessary information to link or import a SharePoint list.

To make building the macro easy, see the Microsoft Office 2003 Web Services Toolkit. This toolkit gives you an easy way to set up the necessary code to invoke a Web service from within VBA code. You can also download the Office XP version of the toolkit. The changes between the two versions of the toolkit are negligible, but you can see all of the details at What's New in the Microsoft Office 2003 Web Services Toolkit.

After installing the toolkit, you can select Tools and then Web Service References to open the Web Services Toolkit dialog box, as shown in Figure 5. Type the URL to one of the Web services and click Search to show the methods of that Web service in the Search Results tree view. Check the box next to the Web service name and click Add. The toolkit builds all of the necessary VBA code to use the Web service in your project.

Figure 5. Using the Web Services Toolkit to add a Web Reference (click picture to see larger image)

After the Web Reference is created, the following code invokes one of its methods and formats the results for display:

Sub GetListCollection()
    ' Class created by toolkit to connect to the Web service
    Dim ws As New clsws_Lists
    ' The collection is returned as an XML node list
    Dim lc As MSXML2.IXMLDOMNodeList
    'Root node of the returned list
    Dim nod As MSXML2.IXMLDOMNode
    ' Document to hold an XSLT transform
    Dim docXSLT As New MSXML2.DOMDocument50
    ' Output string for the XML transformation
    dim strOutput As String
    
    ' Retrieve the collection of lists
    Set lc = ws.wsm_GetListCollection

    ' Create the XSLT to select the desired information
    docXSLT.LoadXml ("<xsl:stylesheet" & _ 
     "xmlns:xsl=""http://www.w3.org/1999/XSL/Transform""" & _
     " xmlns:wss=""http://schemas.microsoft.com/sharepoint/soap/""" & _
     " version=""1.0""><xsl:output method=""text"" />" & _
     "<xsl:template match=""wss:List"">," & _
     "<xsl:value-of select=""@Title""/>," & _
     "<xsl:value-of select=""@ID""/></xsl:template></xsl:stylesheet>")

    ' Get the root node from the list
    Set nod = lc.Item(0)
    ' Apply the XSLT transform
    strOutput = (nod.transformNode(docXSLT))

    ' Use the Word 2003 object model to turn the results into a table
    With Selection
        .TypeText ("List Name, List ID" & strOutput)
        .HomeKey wdStory, wdExtend
        .ConvertToTable Separator:=wdSeparateByCommas, NumColumns:=2
        .HomeKey wdStory
    End With
End Sub

Executing this code inside a new Word document produces the table shown in Figure 6.

Figure 6. Data retrieved by using the Lists Web service (click picture to see larger image)

There are several other ways to use Web services from the Microsoft Office System applications, including the INCLUDETEXT field code in Word and Excel Web Queries. For more information, see Working with Documents and Web services in the Microsoft Office System

Documents and Windows SharePoint Services

You can also store documents in Windows SharePoint Services as well. Every SharePoint site includes a document library, called Shared Documents that serves as a repository for documents.

Programmatically Saving to a SharePoint Site

To save a document to a SharePoint site, you can use the Save method from the object model of the application, together with a properly constructed URL. For example, to add the first open Excel workbook to a SharePoint site on a server execute this line of code:

Application.Workbooks(1).SaveAs _
 "http://server_name/sites/site_name/Shared%20Documents/file_name.xls"

With the workbook saved to a document library, you can continue to work with it normally within Excel. In addition, the document is available on the SharePoint site, as shown in Figure 7.

Figure 7. Documents in a document library (click picture to see larger image)

You can perform a number of operations on the document directly from the document library. In particular, you can use version control (check-in and check-out), or build a Document Workspace site based on the document.

Using Check Out and Check In

From the browser interface, you can check out a document by selecting Check Out from the drop-down menu associated with the document. You can then edit the document in its host application. When you save the document, Windows SharePoint Services prompts you as to whether to keep the document checked out, check it in, or discard your changes. Windows SharePoint Services uses a simple exclusive model to handle check-outs: only one user can have a specified document checked out at any given time.

Each time you check a document in, Windows SharePoint Services stores a new version of the document. You can purge old versions directly from the browser interface if you so choose.

You can also check documents in and out from VBA code inside of a document provided that the document was opened from a document library. Because of the exclusive check-out model, you should always check to make sure that it is safe to perform these operations before actually performing them. To check out an Excel workbook, use this code example:

If Workbooks.CanCheckOut("PartSales.xls") = True Then
    Workbooks.CheckOut docCheckOut
Else
    MsgBox "This document cannot be checked out."
End If

Conversely, you could check in the same document with this code example:

If Workbooks("PartSales").CanCheckIn = True Then
    Workbooks("PartSales").CheckIn SaveChanges:=True, _
     Comments:="New version"
Else
    MsgBox "This document cannot be checked in."
End If

Note that the check-out functionality is located on the Workbooks collection, and the check-in functionality belongs to the individual Workbook object.

Shared Workspaces

Finally, you can create a Document Workspace site around a document in Word, Excel, or PowerPoint. A Document Workspace site is a SharePoint site designed to help a group of users collaborate on one or more documents. Document Workspace sites are managed through the Shared Workspace task pane inside of your document. Figure 8 shows the Shared Workspace task pane in action.

Figure 8. The Shared Workspace task pane

The Shared Workspace task pane includes a link to the Shared Workspace and six tabs (see Figure 8). You can use the link to open the Shared Workspace in your browser. From left to right, the tabs have these functions:

  • Status. Displays status messages
  • Members. Displays the members of the Shared Workspace. Each of these members has an associated Name Control that lets you send e-mail or instant messages, schedule a meeting, add the member to your Outlook contacts, and perform other useful tasks.
  • Tasks. Displays the tasks in the Shared Workspace, together with their status and to whom they are assigned
  • Documents. Displays the documents in the document library that contains the current document
  • Links. Displays the hyperlinks in the Shared Workspace
  • Document Information. Allows you to see who last modified the document and its version history, as well as any custom properties that are assigned to the document

You can create a Shared Workspace directly from the task pane in the UI. Alternatively, you can use the SharedWorkspace object to do so, like this:

ActiveDocument.SharedWorkspace.CreateNew _
 URL:="http://server_name/sites/site_name", _
 Name:="Workspace_name"

The SharedWorkspace object provides programmatic access to all of the functions of the Shared Workspace pane.

Conclusion

Windows SharePoint Services provides the collaborative backbone for the applications in the Microsoft Office System. You can use SharePoint lists from applications such as Access and Excel as a place to store data to share both with users of the Microsoft Office System and others. For deeper integration, you can use Web services included as part of Windows SharePoint Services to interact directly with Windows SharePoint Services, or use the Shared Workspace object model to build document-centric collaborative solutions. As with the Microsoft Office System, Windows SharePoint Services delivers a solid core of functionality that you can extend with your own code.

© Microsoft Corporation. All rights reserved.