Using Excel 2003 to Manage Project Sites with Windows SharePoint Services 2003

Article
05/23/2014

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Anup Kafle, 3Sharp

Published: July 2005

Updated: January 2006

Applies to: Microsoft Visual Studio 2005 Tools for the Microsoft Office System, Microsoft Windows SharePoint Services 2003, Microsoft Office Excel 2003

Summary: Learn how to use Excel 2003 and Visual Studio 2005 Tools for Office to create and manage Windows SharePoint Services Web sites. The sample code is available in Visual Basic and Visual C#. (26 printed pages)

Download OfficeVSTOManageProjectSiteswithExcelandSharePoint.msi.

Contents

Overview

This document is designed to help solution developers and architects understand how Microsoft Office Excel 2003 can be integrated with back-end systems (in this case Microsoft Windows SharePoint Services) to create powerful client-side applications, through which back-end functionality is exposed. In particular, this solution is used by project managers to launch a new Windows SharePoint Services team site and to collect data used to manage a project. It shows how various Microsoft technologies can work together to improve and simplify business processes, with an emphasis on Microsoft Visual Studio 2005 Tools for the Microsoft Office System (Visual Studio 2005 Tools for Office).

Project Site Management Solution Concepts and Architecture

The following topics explain in more detail the concepts and architecture of the Project Site Management solution:

Solution Benefits

A quick introduction to the solution
Solution Technologies

A list of Microsoft products and technologies used to implement the solution
Solution Scenario

A discussion of how the solution functions, who uses it, and the components involved
Solution Components

A detailed discussion of the components that make up the solution

Solution Benefits

With this solution, project managers gain benefits that are not available using only Windows SharePoint Services:

The ability to create SharePoint sites from outside of the Windows SharePoint Services user interface
A rich Excel user interface for editing and managing project tasks
The ability to manage multiple projects from one location
The ability to generate project documents, such as project charter and project risks, on the fly using form data

Solution Technologies

The target customer for this solution is a small or medium-sized corporation that uses the Microsoft Windows Server 2003 operating system for its corporate network, that already has an investment in collaboration software, such as Microsoft SharePoint Products and Technologies, or that is planning on making such an investment.

The following technologies are used in this solution:

Operating system: Microsoft Windows Server 2003
Spreadsheet program: Microsoft Office Excel 2003
Team collaboration tool: Microsoft Windows SharePoint Services 2003
Development tool: Microsoft Visual Studio 2005 Tools for the Microsoft Office System

Solution Scenario

The project manager launches the Excel workbook application to create a new project site and fills in all project-related information in one single worksheet. With one click of a button, the application creates a project Web site on the designated server that is running Windows SharePoint Services, with all relevant data and document attachments.

Using another sheet in the same workbook, the project manager reviews and modifies tasks from existing project sites, then detaches the application from the Windows SharePoint Services environment by taking the entire document offline — allowing more flexibility in working with project data.

Table 1. Solution workflow for the project manager

User action	Implementation
Opens the ProjectManagerApplication.xls workbook.	Excel displays the Getting Started worksheet with the Document Actions task pane.
Fills in all project-related information and executes site creation command.	Excel launches the site creation routine.
Selects a project site from a list of existing sites.
Adds a task to the Task list for a particular project.
Edits a task item.
Deletes a task that is no longer needed.
Opens SharePoint site, verifies changes made from Excel.
Changes a task in the SharePoint site.
Goes back to the Excel application and verifies changes made from Windows SharePoint Services.
Takes the document offline with cached SharePoint site data.	The Go Offline button toggles between online and offline status.
Modifies Excel data offline.
Synchronizes offline changes by bringing document back online.

Solution Components

The main components of the Project Site Management solution are:

Excel workbook with Visual Studio 2005 Tools for Office application
Custom assemblies
Built-in Windows SharePoint Services Web services
Windows SharePoint Services template

The following diagram illustrates the solution architecture.

Figure 1. Project Site Management solution architecture

Getting Started Worksheet

The Getting Started worksheet appears when the workbook opens. The Document Actions task pane loads on the right side of the screen or where the user docked it last. The user has two choices: Start New Project Site or View Existing Project Sites.

Create New Project Worksheet

When the user clicks Start New Project Site, the workbook view switches to the Create New Project worksheet, and then adds a new set of custom controls to the Document Actions task pane. All of the lists in the Excel worksheet are Visual Studio 2005 Tools for Office ListObject host controls, wrapping the native Excel XML list object.

Figure 2: Create New Project worksheet

Using this worksheet, the user can add a new project site to a specified server that is running Windows SharePoint Services. The server is designated under Server URL in the Document Actions task pane. Figure 2 displays the worksheet with sample data. At this point, the user clicks Create Site in the Document Actions task pane to initiate the site-creation process.

Manage Tasks Worksheet

The user can also use the workbook to manage project tasks. When the user switches to the Manage Tasks worksheet, the task pane automatically loads a new set of custom controls. These controls enable the user to load and manipulate existing tasks.

The user starts by specifying where the project sites are located, by typing in the project site collection URL and clicking Go in the Document Actions task pane.

Windows SharePoint Services returns a list of all subsites under the specified site collection URL. These subsites are listed as list box items under Available Projects. The user can pick one project from this list to view their tasks.

At this point, the solution calls Windows SharePoint Services for each project to pull down the list of tasks and users in the selected project. Instead of putting them in the worksheet, however, the solution puts them in a dataset which is bound to the list object on the worksheet.

Figure 3: Manage Tasks worksheet

Project Site Management Solution Walkthrough

The following section shows you the steps that a project manager would typically go through while using this application for managing SharePoint project sites. These steps assume that a server running Windows SharePoint Services exists, with a predefined location for project sites, such as http://server03/sites/projectsites.

To start the application

Install the application using the MSI provided.
Navigate to the program installation folder, and then open the VB (or CS) folder.

The path to the default installation folder is My Documents\Visual Studio 2005\Projects\Project Site Management Sample.
Double-click ProjectManagerSolution.sln (ProjectManagerSolutionCS.sln for Visual C#) to open the solution in Visual Studio.
In Solution Explorer, right-click the ExcelApplication project, and then choose Set as Startup Project.
Press F5 to build and run the solution.

The Getting Started worksheet opens with the Document Actions task pane loaded.

To create a new project site

Click Create New Project Site on the Document Actions task pane.

The workbook switches to the Create New Project worksheet.
In the Document Actions task pane of this worksheet, in the Server URL box, type the URL of the SharePoint site, for example, http://server03/sites/projectsites.

This is the URL of the parent project site that will have other project sites below it.
In the Project Title box, type a title for the project.

The Project Site URL box automatically populates according to the Project Title box.
In the Authority box, type the name of the person who is authorizing the project.
In the Agent box, type the name of the project manager.
In the Mission Statement box, type the project goals and missions.
In the Resources section of the worksheet, click Import Users.

This opens a file browser that enables the import of an XML document to populate the Resources section. For the solution sample, you fill the names out manually. Cancel the file browser and proceed.
In the Name column, type the name of a resource.
In the Domain User Id column, type a valid server name and the alias of the resource, for example, server03\neils.
In the Email Address column, type the e-mail address of the resource.
In the Project Role column, select the project role for the resource.
Type comments into the Comments box (optional).

Note

Repeat Steps 8 through 12 to add additional resources.
In the Tasks section, in the Task Title column, type all of the tasks that are required of the project.

Add at least three tasks for a complete demonstration scenario.
For each task in the previous step, add start and due dates.
In the Assign To column, use the list box to assign each task to a resource.
In the Description box, type a brief description of each task. (optional)
In the Milestones (Events) section, in the Milestone Name list, type names for milestones (major accomplishment points) for the project.
Give each milestone a date, location text (if applicable), and description.
In the Project Risks section, type descriptions of all project risks, including % Likelihood of occurrence and Severity of the risk.
In the Constraints section, type descriptions of project constraints.

At this point, the spreadsheet should look similar to Figure 2.
In the Document Actions task pane of the Create New Project worksheet, click Create Site.

A message box appears, confirming the result of the site creation process.
Open a Web browser window, and then verify that a Web site exists at the Project Site URL created in Step 3 and that it contains all the data you entered.

To create additional sites

To quickly add additional SharePoint project sites, change the name of the project in the Document Actions task pane, and leave the rest of the worksheet intact.

Follow Steps 21–22 above. Using this method, you can re-use the data that is already on the worksheet to create a new site.

To manage project tasks

Select the tab for the Manage Tasks worksheet.

A new control appears in the Document Actions task pane.
In the Project Site Collection URL box, type the project site URL of the project you want to manage, for example, http://server03/sites/projectsites.
Click Go (the green arrow to the right of the text box).

The Listbox control in the task pane populates with project site names.
Select one site name, and then click OK.
Verify that the tasks from the selected site populate the tasks list object in the worksheet.
Modify the title of one of the tasks in the tasks list object.
Click Update Tasks, to apply changes.

A message box appears, confirming the task successfully updated.
Add a new task to the list by following the template from the other tasks in the list.
Click Update Tasks to save the new task.
Start your browser, and then browse to your SharePoint site to verify that the task added successfully.
Switch to Excel.
Right-click on a task row item, point to Delete, and then click Row.
Click Update Tasks to apply this change.

Note

You do not have to click Update Tasks after each change to the list. You can make multiple changes, and then click Update Tasks to apply all of them.
Restart your browser, and then browse to your SharePoint site to verify that the task was deleted from the corresponding SharePoint project site.

To manage projects in offline mode

If there are no project tasks listed in the worksheet, follow the steps above to populate the list object with tasks.
In the Document Actions task pane, click Go Offline.

Other controls in the task pane are now unavailable.
Save the Excel workbook with a different name within the same folder as the original workbook.
Quit Excel.
Start Excel and open the workbook.

The workbook automatically opens the Manage Tasks worksheet. The workbook is now in offline mode.
Delete a task from the list.
Add a new task to the list.
Save the workbook, and then quit Excel.
Restart Excel and the workbook.

The workbook again opens displaying the Manage Tasks worksheet.
In the Document Actions task pane, click Go Online.

This sends all changes made to the list object since you took the workbook offline to Windows SharePoint Services.
Start your browser and browse to your SharePoint site to verify that the changes were applied to the site.

Code Review

This section looks at the custom code and components within the Project Site Management solution. Most of the code walkthroughs in this article cover the Visual Studio Tools for Office application for Excel. Integration and custom work with SharePoint Products and Technologies is also discussed.

These code walkthroughs do not offer an exhaustive, start-to-finish explanation of every piece of the solution. Rather, they show those elements that are most interesting and valuable, and that provide insights into concepts that you must know to modify the solutions for your requirements.

Topics in this section include:

Project Site Management Application

Describes the custom code, components, and special techniques used in the Project Site Management Visual Studio 2005 Tools for Office application, the core of the solution.
Datasets

Gives insight on how dataset objects are used to communicate data between the Project Site Management application and the server that is running Windows SharePoint Services.
Integration with Windows SharePoint Services

Discusses the various built-in Windows SharePoint Services Web services that are used in this solution.

Project Site Management Application

This part of the solution uses Visual Studio 2005 Tools for Office. While the solution code is available in both Microsoft Visual Basic .NET and Visual C#, only Visual Basic code samples are shown in these examples.

Custom User Controls

Visual Studio 2005 Tools for Office supports Microsoft Windows Forms controls and host controls. Host controls extend Word 2003 and Excel 2003 objects, such as the Excel NamedRange object or the Word Bookmark object. Windows Forms controls are objects that users can interact with to enter or manipulate data, and they can be added to Word documents, to Excel worksheets, and to the Document Actions task pane.

User controls provide a way for you to create and reuse custom graphical interfaces. A user control is essentially a component with a visual representation. As such, it might consist of one or more Windows Forms controls, Excel or Word controls, components, or blocks of code that contain functionality. User controls can be placed in Windows Forms and in the Document Actions task pane in the same manner as other controls.

The Project Site Management solution uses these controls at design time to enhance the user experience and ease workflow. The DataEntryControl in the task pane is an example of a typical custom control that uses multiple Windows Forms controls.

DataEntryControl User Control

As described in the Solution Components section earlier, this control appears on the Document Actions task pane, and is used to enter information about a new project site. The control was created at design time in Visual Studio 2005. Open the control in designer view to see how the various components are laid out.

Once the custom control is created and coded, it must be added to the Document Actions task pane for it to be visible during run time. If you examine the code under the Startup event of the ThisWorkbook class, you see the following line of code that is responsible for this operation:

Me.ActionsPane.Controls.Add(New GettingStartedControl)

In this case, Me refers to the Globals.ThisWorkbook object.

After you add the control to the Document Actions task pane, it can be accessed in the code using its object name. For example, the following code sample hides the DataEntryControl object from view in the task pane:

Me.ActionsPane.Controls("DataAccessControl").Visible = False

There are times when you may want to access these custom controls programmatically from a context other than the task pane. For example, you may want ThisWorkbook (a global object) to communicate with the DataAccessControl object to retrieve a value stored in the DataAccessControl object so that this value can be stored as a document property that is saved with the workbook. An application of this example is illustrated later in this article.

Host Controls

As stated earlier, this solution makes extensive use of list objects, which are a type of host control. These controls are added to the workbook at design time and can be accessed programmatically by their object names. For example, there are five list objects in the Create New Project worksheet, where the user fills in project-related data for a new project site to be created.

The tasksList object is one such control. It contains five data columns, in which the user types project task information. The Assign To column has a data-validation rule applied to it at design time, so that only the users listed in the Name column of the usersList object are allowed. You can open the workbook in design view and see how the data validation rule applies to the list column.

To see how data validation applies to the tasksList control

In the tasksList object, select the data body range of the Assign To column.
On the Visual Studio Data menu, point to Microsoft Office Excel Data and click Validation.
In the Data Validation Settings window, set the following values:
- Allow: List
- Source: Point to the data body range of the Name column of usersList
Click OK.

Project Site Creation Steps

After all list objects in the worksheet are filled in, and the controls in the DataEntryControl form are populated, you are ready to create the site with a click of the Create Site button. While this is a simple step, the application does a lot of work in the background:

The Excel application checks to see if all required data is filled in.
The application gathers data from each list object host control and creates an XML document object.
It also gathers data from the DataEntryControl custom control.
It passes all the accumulated data to the external assembly, ProjectSharePointCreation, which applies business rules to the data.
ProjectSharePointCreation passes the validated data to another assembly, SharePointClientLibrary, which transforms the data into a structure that the Windows SharePoint Services can understand.
SharePointClientLibrary calls the Document Workspace Web service (dws.asmx) — a built-in Web service that is responsible for creating document workspace sites — and passes all the project site information.
The Web service creates the site using the Document Workspace site template, which is a standard site template included with Windows SharePoint Services.
The Web service returns a confirmation message that is passed back to the Excel application, and then converted to a message box for the user to see.

Data Entry List Objects

List objects in Excel make it easy for you to fill in or retrieve structured data. Before a project site is created, you must fill in all project-related information in Excel. Much of this data entry is done in list objects, because this data can be easily converted into XML nodes or XML document programmatically.

The following function illustrates how user data is collected programmatically and converted to an XML node object:

Function CollectUsersData() As XmlNode
    Dim xmlDoc As XmlDocument
    Dim usersListObject As Excel.ListObject
    Dim userNode As XmlNode
    Dim r As Integer = 0

    'Define the users list data source.
    usersListObject = Globals.Sheet2.usersList.InnerObject

    'Prepare the users data node object.
    xmlDoc = New XmlDocument
    xmlDoc.AppendChild(xmlDoc.CreateElement("Users"))

    For r = 1 To usersListObject.DataBodyRange.Rows.Count
        'Ensure each row has a user name.
        'If blank, stop the function and notify user.
        If usersListObject.DataBodyRange(r, 1).Value= String.Empty Then
           MessageBox.Show( _
           Globals.ThisWorkbook.strings.GetString("userNameRequired"))
           Return Nothing
        End If

        'Ensure each user has a login name.
        'If blank, stop the function and notify user.
        If usersListObject.DataBodyRange(r, 2).Value= String.Empty Then
           MessageBox.Show( _
           Globals.ThisWorkbook.strings.GetString("userLoginRequired"))
           Return Nothing
        End If

        'Ensure each user has an email address.
        'If blank, stop the function and notify user.
        If usersListObject.DataBodyRange(r, 3).Value= String.Empty Then
           MessageBox.Show( _
           Globals.ThisWorkbook.strings.GetString("userEmailRequired"))
           Return Nothing
        End If

        'Ensure each user has a role assigned.
        'If blank, stop the function and notify user.
        If usersListObject.DataBodyRange(r, 4).Value= String.Empty Then
           MessageBox.Show( _
           Globals.ThisWorkbook.strings.GetString("userRoleRequired"))
           Return Nothing
        End If

        'For each row item, create a User node object.
        userNode = xmlDoc.CreateNode(XmlNodeType.Element, "User", "")

        'Add child elements (data fields) to this node.
        userNode.InnerXml = _
        "<UserName>" + usersListObject.DataBodyRange(r, 1).Value.ToString() + "</UserName>" + _
        "<Login>" + usersListObject.DataBodyRange(r, 2).Value.ToString() + "</Login>" + _
        "<Email>" + usersListObject.DataBodyRange(r, 3).Value.ToString() + "</Email>" + _
        "<Role>" + usersListObject.DataBodyRange(r, 4).Value.ToString() + "</Role>" + _
        "<Notes>" + usersListObject.DataBodyRange(r, 5).Value.ToString() + "</Notes>"

        'Add these data fields to the root element.
        xmlDoc.SelectSingleNode("/Users").AppendChild(userNode)
    Next

    'Return the xml document object to calling procedure.
    usersNode = xmlDoc.DocumentElement
    Return usersNode

End Function

Within the DataEntryControl class, usersNode is a global variable. The global variables tasksNode, milestonesNode, risksDoc, and charterDoc are populated with list data using similar functions.

Datasets

Visual Studio 2005 Tools for Office provides a rich interface for using datasets. In this solution, a majority of the data transfer between the Excel application and Windows SharePoint Services is accomplished using datasets. The structure of the datasets used in this solution is pre-defined at design time by XML schemas. The ProjectSharePointCreation class contains the main dataset schemas used in this solution, including ProjectInfo and ProjectList. After the XML schema is created, the MSDataSetGenerator custom tool runs against it. The tool auto-generates code so that the schema is available for use as a strongly typed dataset from the code. For example, the ProjectList class is a member of the ProjectSharePointCreation namespace. An instance of this class is used as a global variable, under ThisWorkbook, as shown in the following code:

Imports ProjectSharePointCreation
Public Class ThisWorkbook
    Friend projectsList As ProjectList
    ...
    ...

The ProjectList schema is designed to store the list of SharePoint project sites that are retrieved after a Web service call. After this list is retrieved, and the projectsList dataset object is populated, the data is ready for transfer to a user interface control. In this solution, this is the Available Projects check-box control. The following code binds the control to the dataset:

'Initialize the dataset object.
Globals.ThisWorkbook.projectsList = _
    Globals.ThisWorkbook.SPProjectManagement.ProjectSitesDS
'Data bind and populate the check-box.
With Me.projectsCheckList
    .DataSource = Globals.ThisWorkbook.projectsList.Project
    .DisplayMember = "Title"
    .ValueMember = "Url"
End With

The ProjectInfo schema and its associated dataset object is a little more complicated. This schema retrieves all the task information about the projects selected by the user. As with the ProjectList schema, ProjectInfo is made available in the solution by declaring it as a global variable:

Friend projectInfoDataSet As ProjectInfo

To populate the projectInfoDataSet object with actual data, the solution iterates through each site the user has selected in the Available Projects control. It passes the project site URL string value of each site into the AddProjectInfo function. On each subsequent call of this function, task data from the site is appended to the projectInfoDataSet object using the .Merge method, as shown in the following code:

Private Shared Sub AddProjectInfo(ByVal projectSiteUrl As String)
    Dim localProjectInfo As ProjectInfo

    'Get the info (all Tasks and Users) about the current project site.
    localProjectInfo = _
        Globals.ThisWorkbook.SPProjectManagement.GetProjectInfo( _
        projectSiteUrl)

    'Add the current project info to the global ProjectInfo dataset.
    Globals.ThisWorkbook.projectInfoDataSet.Merge(localProjectInfo)
End Sub

After the dataset object is filled with data, the dataset is bound to the user interface object. This time, the object is the list object host control, tasksListobject, which is part of the Sheet3 class (that is, the Manage Tasks worksheet).

First, the BindingSource object is declared as a global variable in the class:

Public Class Sheet3
    'Define the data binding source for the Tasks list object.
    Friend tasksBindingSource As System.Windows.Forms.BindingSource
    ...
    ...

The binding process is similar to the binding of the check-box list control discussed earlier:

'Bind the task list object to data source if not done already.
If IsNothing(tasksBindingSource) = True Then
    tasksBindingSource = New System.Windows.Forms.BindingSource
    tasksBindingSource.DataMember = "Task"
    tasksBindingSource.DataSource = _
        Globals.ThisWorkbook.projectInfoDataSet
    'Apply the data binding.
    Globals.Sheet3.tasksListObject.SetDataBinding(tasksBindingSource)
End If
'Refresh list data.
Globals.Sheet3.tasksListObject.Refresh()

After the list object is filled with data, its rows can be added, modified, or deleted, and the changes can be propagated to Windows SharePoint Services through the dataset.

Managing Task Data Modifications

For rows that are inserted, updated, or deleted in the list object, the changes are propagated to the corresponding dataset. The function UpdateTasks passes these changes to Windows SharePoint Services. The function returns an integer value indicating the number of successfully modified rows:

'Get local reference to the global dataset.
Dim projectInfo As ProjectInfo
projectInfo = Globals.ThisWorkbook.ProjectInfoDataSet

Dim serverUrl As String = Globals.ThisWorkbook.serverUrlProperty.Value.ToString
Dim counter As Integer = 0

If serverUrl.Length > 0 Then
    'Set up the project management.
    Dim projectManager As New ProjectManagement(CStr(Globals.ThisWorkbook.serverUrlProperty.Value))

    'Walk the projects looking for updates.
    Dim changedTaskTable As ProjectInfo.TaskDataTable
    Dim updateAction As TaskAction
    Dim taskId As Integer
    Dim results As ProjectResults
    Dim completeData As Boolean
    Dim errorMessage As StringBuilder = New StringBuilder

    Try
        'Get the tasks row to set the project url and to
        'get the changed tasks.
        changedTaskTable = CType(projectInfo.Task.GetChanges, _
            ProjectSharepointCreation.ProjectInfo.TaskDataTable)

        If IsNothing(changedTaskTable) = False Then
            Dim changedTaskRow As ProjectInfo.TaskRow
            Dim i As Integer

The code created a data table called changedTaskTable, which is populated with inserted, updated, or deleted rows from the dataset. The next task is to walk the list object and match the data table row with the corresponding list object row. The function starts from the bottom of the list object and moves upward:

For i = projectInfo.Task.Rows.Count - 1 To 0 Step -1
    completeData = True
    changedTaskRow = projectInfo.Task(i)

    'Handle only the rows that have been added, modified, or deleted.
    'First, handle the deleted tasks.
    If changedTaskRow.RowState = DataRowState.Deleted Then
        projectManager.WebsiteUrl = CStr(changedTaskRow _
            ("ProjectSiteUrl", DataRowVersion.Original))
        updateAction = TaskAction.Delete

        Dim row As DataRow = changedTaskRow

        taskId = CInt(row("TaskId", DataRowVersion.Original).ToString)
        results = projectManager.UpdateTask(updateAction, _
            taskId, "Nothing", "Nothing", TaskStatus.None, _
            TaskPriority.None, DateTime.Now, _
            DateTime.Now, 1, CDec(0.0))

        'Mark row as unchanged.
        changedTaskRow.AcceptChanges()

        counter = counter + 1

    ElseIf changedTaskRow.RowState = DataRowState.Added Or _
    changedTaskRow.RowState = DataRowState.Modified Then
        projectManager.WebsiteUrl = changedTaskRow.ProjectSiteUrl
        taskId = -1

        If changedTaskRow.RowState = DataRowState.Modified Then
            updateAction = TaskAction.Update
            completeData = False

            'See if task Id is valid.
            Try
                taskId = CInt(changedTaskRow.TaskId)
                completeData = CheckCompleteData(changedTaskRow)

            Catch ex As System.FormatException

            Catch ex As System.Data.StrongTypingException

            End Try

        Else
            'Assume an addition.
            updateAction = TaskAction.[New]
            completeData = CheckCompleteData(changedTaskRow)
        End If

        If completeData = True Then
            If changedTaskRow.RowState <> DataRowState.Deleted Then
                'Get the userId of the AssignedTo person
                'and reassign the value from UserName to UserId.
                changedTaskRow.AssignedTo = GetUserIdFromName _
                    (changedTaskRow.AssignedTo)

                'Get the description of the changed task.
                Dim description As String = String.Empty
                If changedTaskRow.IsDescriptionNull = False Then
                    description = changedTaskRow.Description
                End If

                'Gather all supplied vales and make the call.
                With changedTaskRow
                    results = projectManager.UpdateTask _
                        (updateAction, taskId, .Title, description, _
                        CType([Enum].Parse(GetType(TaskStatus), _
                        .Status), TaskStatus), _
                        CType([Enum].Parse(GetType(TaskPriority), _
                        .Priority), TaskPriority), .StartDate, _
                        .DueDate, CInt(.AssignedTo), .PercentComplete)
                End With

                'Mark the row as unchanged.
                changedTaskRow.AcceptChanges()

                counter = counter + 1
            End If
        Else
            errorMessage.Append(vbCrLf + taskId.ToString)
        End If
    End If
Next

By this point, all modified rows have been identified and relayed to Windows SharePoint Services using the projectManager.UpdateTask method call. The rest of the UpdateTask function is responsible for refreshing and reformatting the worksheet data, and returning the count of modified rows to the calling procedure.

            SetProjectTaskList()
            SetUpAssignedToColumn()
        End If

    Catch ex As Exception
        MessageBox.Show(ex.Message)

    Finally
        If errorMessage.Length > 0 Then
            MessageBox.Show(Globals.ThisWorkbook.strings. _
            GetString("taskUpdateIncomplete") + errorMessage.ToString)
        End If

    End Try
End If

Return counter

Offline Functionality

Visual Studio 2005 Tools for Office provides uses data islands to store external data with Office documents so that the data can be used even when the document is offline.

Dataset Caching

In this application, the dataset object projectInfoDataSet is an example of a cached object that is saved with the document. To enable data caching, apply the <Cached> attribute to the variable declaration. For example, projectInfoDataSet is declared as a global variable in the ThisWorkbook class, as shown in the following code:

<Cached()> Friend WithEvents projectInfoDataSet As ProjectInfo

Document Properties

When you take the workbook offline, all information on the worksheet and any cached dataset is saved with it. However, the task pane data is lost. One way that you can preserve this information is by saving the desired values as document properties. For example, in this solution, the Server URL text that the user types in the task pane is saved as a document variable. The following code illustrates this process.

First, global variables are declared in the ThisWorkbook class:

Friend serverUrlProperty As Microsoft.Office.Core.DocumentProperty
Friend offlineModeProperty As Microsoft.Office.Code.DocumentProperty

The document properties are set before the workbook is saved. This corresponds to the ThisWorkbook_BeforeSave event:

Private Sub ThisWorkbook_BeforeSave(ByVal SaveAsUI As Boolean, ByRef Cancel As Boolean) Handles Me.BeforeSave
    'Set the offline mode document property to "Yes".
    offlineModeProperty.Value = True

    'Save the URL address of the server in the actions pane.
    Dim customControlObject As DataAccessControl
    customControlObject = _
        CType(Me.ActionsPane.Controls("DataAccessControl"), _
        DataAccessControl)

    'Copy the text value to the document property.
    serverUrlProperty.Value = _
        customControlObject.serverAddressText.Text
End Sub

Here, the custom control is converted explicitly using the CType function.

The previous code sets document variables so that project information is kept intact with the document even when offline. When the user wishes to go back online, upon opening the workbook, the application checks the offlineModeProperty document property value to determine if it is offline. If the value is True, the application takes all the necessary actions to make the workbook ready for use. The following code sample of the ThisWorkbook_Startup event illustrates this. Note that the variable properties is a global variable of type Microsoft.Office.Core.DocumentProperties:

'Get the custom document properties.
properties = CType( _
    Globals.ThisWorkbook.CustomDocumentProperties, _
    Microsoft.Office.Core.DocumentProperties)

offlineModeProperty = properties.Item("Offline Mode")
serverUrlProperty = properties.Item("Server Url")

'If the Offline Mode is True, then prepare the document
'and the actions pane for offline use.
If offlineModeProperty.Value = True Then
    'Open the workbook to Sheet3.
    Globals.Sheet3.Activate()

    'Prepare the actions pane custom control.
    DataAccessControlOffline(serverUrlProperty.Value)
End If

The external method DataAccessControlOffline makes necessary changes to the custom control so that it is usable in offline mode:

Private Sub DataAccessControlOffline(ByVal serverUrl As String)
    Dim customPaneControl As DataAccessControl

    'Show Sheet3 by default.
    Globals.Sheet3.Activate()
    'Append the server URL text to the action pane.
    customPaneControl = _
        CType(Me.ActionsPane.Controls("DataAccessControl"), _
        DataAccessControl)
    customPaneControl.serverAddressText.Text = serverUrl

    'Disable action pane buttons.
    With customPaneControl
        .goPictureButton.Enabled = False
        .projectsCheckList.Enabled = False
        .updateTasksButton.Enabled = False
        .offlineButton.Text = Me.strings.GetString("goOnlineText")
    End With
End Sub

Integrating with Windows SharePoint Services

All communication between Windows SharePoint Services and the Project Site Management application happens through Web services. These are standard Web services that are included with Windows SharePoint Services; no custom Web services are used. The following table summarizes all Web services used in this application.

Table 2. Web services used in this solution

Web service name	Description	Path
Document Workspace	Creates a SharePoint site using the document workspace template.	../_vti_bin/dws.asmx
Lists	Manipulates (create, modify, delete) SharePoint list data for a given site.	../_vti_bin/lists.asmx
Sites	Retrieves information about a collection of site templates in a given SharePoint site.	../_vti_bin/sites.asmx
Users and Groups	Provides methods for working with project users and user roles for a given site.	../_vti_bin/usergroup.asmx
Webs	Retrieves information about the collection of subsites within a given SharePoint site.	../_vti_bin/webs.asmx

The following section highlights an example of how one Web service is used in the solution.

Retrieving Web Sites Collection

As stated in the Solution Components section earlier, one of the steps of using the Manage Tasks worksheet is retrieving the list of all SharePoint sites that are subsites under the Web site provided by the user. The Webs (Webs.asmx) Web service uses the site URL provided by the user in the task pane as an input parameter. The result of the Web service call is an XML document containing a list of subsites.

In the following code sample, SharePointClientLibrary is the custom class that contains Web references to the server that is running Windows SharePoint Services. Focus on the Web reference named WebsWS. Although the URL of this reference was defined at design time, it also must be defined using code at run time to run the Web service call against the particular Web site the user specified in the task pane. This value is contained within the url string variable:

Dim websService as new SharePointClientLibrary.WebsWS.Webs
Dim results as XmlNode
websService.Credentials = System.Net.CredentialCache.DefaultCredentials
websService.Url = url + "/vti_bin/webs.asmx"

results = websService.GetWebsCollection()
return results

Uploading to a SharePoint Library

This solution also features automatic creation of project charter documents and project risk documents with contents based on data filled in by the user at run time. It collects discrete pieces of information from Excel list objects and the Document Actions task pane as XML document fragments. For more information, see Create New Project Worksheet. It then sends these document fragments to the ProjectCreation class of the external assembly, ProjectSharePointCreation.dll.

The ProjectCreation class contains procedures for transforming the XML document fragments into WordML fragments. The following code shows this. This function returns an XML document for a given document node and XSLT resource name:

Private Shared Function GetTransformedDocument(ByVal documentNode As 
XmlNode, ByVal xsltResourceName As String) As XmlDocument
    'Creates an XmlDocumemt based on a document node
    'and the name of the transform stored in resources.
    Dim retValue As XmlDocument = Nothing
    Try
        'We need to grab the request into the right format for the 
        'web service.
        Dim transform As XslCompiledTransform = _
            GetResourceTransform(xsltResourceName)

        If Not (transform Is Nothing) Then
            Dim documentDoc As New XmlDocument()
            documentDoc.LoadXml(documentNode.OuterXml)

            Dim sb As New StringBuilder()
            Dim sw As New StringWriter(sb, _
                System.Globalization.CultureInfo.CurrentCulture)
            transform.Transform(documentDoc.CreateNavigator(), _
                Nothing, sw)
            sw.Close()

            retValue = New XmlDocument()
            retValue.LoadXml(sb.ToString())
        End If
    Catch
        retValue = Nothing
    End Try

    Return retValue
End Function

The function GetResourceTransform is defined as follows:

Private Shared Function GetResourceTransform(ByVal resourceTransformName As String) As XslCompiledTransform
    Dim retValue As XslCompiledTransform = Nothing
    Try
        retValue = New XslCompiledTransform()
        Dim s As Stream = [Assembly].GetExecutingAssembly(). _
            GetManifestResourceStream(resourceTransformName)
        Dim sr As New StreamReader(s)

        Dim doc As New XmlDocument()
        doc.LoadXml(sr.ReadToEnd())

        retValue.Load(doc.CreateNavigator(), Nothing, Nothing)
    Catch
        retValue = Nothing
    End Try
    Return retValue
End Function

The result of the previous transform is converted into bytes and passed to the AddDocument procedure of the DocumentLibrary custom class.

Note

Because the AddDocument procedure uses the Web DAV client, the WebDAV Web service extension must be enabled on the server that is running Windows SharePoint Services.

Public Sub AddDocument(ByVal documentToAdd As DocumentActionList, ByVal documentContents() As Byte)
    Dim size As Integer
    Dim objStream As Stream
    Dim succeeded As Boolean = True
    Dim hwrHttpWebRequest As HttpWebRequest
    Dim failureReason As String = ""

    If documentToAdd Is Nothing Then
        Throw New ArgumentNullException("documentToAdd")
    End If

    If documentContents Is Nothing Then
        Throw New ArgumentNullException("documentContents")
    End If

    Try
        'Get the document _url into an html-encoded string.
        Dim documentUrl As String = System.Web.HttpUtility.HtmlEncode _
            ((_url + "/" + Me._name + "/" + _
            documentToAdd.DocumentActionRequest(0).BaseName))
        Dim uri = New Uri(documentUrl)
        hwrHttpWebRequest = _
            CType(HttpWebRequest.Create(uri), HttpWebRequest)

        'Get the size of the document to be written.
        size = documentContents.Length

        'Set up the request.
        hwrHttpWebRequest.Accept = "*/*"
        hwrHttpWebRequest.ContentLength = size
        hwrHttpWebRequest.ContentType = "application/octet-stream"
        hwrHttpWebRequest.Credentials = _
            System.Net.CredentialCache.DefaultCredentials
        hwrHttpWebRequest.KeepAlive = True
        hwrHttpWebRequest.PreAuthenticate = True
        hwrHttpWebRequest.Method = "PUT"
        hwrHttpWebRequest.UserAgent = "DAV Client"
        hwrHttpWebRequest.SendChunked = False
        hwrHttpWebRequest.AllowWriteStreamBuffering = True

        'Obtain the request stream and write the file information.
        objStream = hwrHttpWebRequest.GetRequestStream()
        objStream.Write(documentContents, 0, size)
        objStream.Flush()
        objStream.Close()

        'Get the results of the write.
        hwrHttpWebRequest.GetResponse()
    Catch ex As System.Web.Services.Protocols.SoapException
        failureReason = ex.Message
        succeeded = False
    Catch wex As System.Net.WebException
        failureReason = wex.Message
        succeeded = False
    End Try
    SetDocumentUpdateResults(succeeded, failureReason, documentToAdd)
End Sub

Code Security

The sample code referred to in this article is intended for instructional purposes. You should not use it in deployed solutions without modifications. In particular, you must consider code security.

To illustrate the simplicity of this sample solution, a list of potential threats has been identified using the threat modeling process and tools described in the Threat Modeling section of the Microsoft Security Developer Center.

The following are examples of identified threats or vulnerabilities that you should consider before expanding or deploying this solution.

Visual Studio Tools for Office assemblies are replaced

If the Visual Studio Tools for Office assemblies are replaced with other assemblies, this could result in the application behavior being altered to compromise the system. You can mitigate this threat by signing the code, using strong name conventions, or providing hash evidence. This sample does not implement these precautions, but they are strongly recommended before you deploy an application to a production environment.
Windows SharePoint Services Web Service request is intercepted and altered

You can mitigate this threat by implementing a secure network protocol.
Users inadvertently overwrite data when synchronizing after offline use

Consider this scenario: User A modifies data offline while user B changes the online version of the document. If user A goes online at a later point and synchronizes, user B's changes are overwritten.

For more information about code security, visit the Microsoft Security Developer Center.

Conclusion

This article shows how various aspects of Visual Studio 2005 Tools for Office, Excel, and Windows SharePoint Services work harmoniously in a hypothetical business scenario. The Project Site Management solution showcased in this article is a functional tool, but it lacks some elements of a real-world application, such as a foolproof user interface and robust error handling. It is meant to provide guidance for Office solution developers for integrating an Excel Visual Studio 2005 Tools for Office solution with a Windows SharePoint Services team site.

About the Author

Anup Kafle is a developer for 3Sharp LLC, a solutions provider focused on helping customers understand, design, develop, deploy, and secure solutions with Windows, Exchange and Microsoft Office. 3Sharp works closely with Microsoft, creating many high-profile training, design, and deployment resources, such as the recently-released FabriKam 3.1 Solutions Learning Platform, which teaches developers how to create end-to-end smart client solutions using Microsoft Office.

Additional Resources

Visual Studio 2005 Tools for Office

Office Developer Center

Microsoft Office Developer Center

Code Security