Importing Project 2007 Tasks from Excel Using a Managed Code Add-In

  • Article

Summary: Learn how to develop and deploy a managed code add-in for Microsoft Office Project 2007 that imports task data from Microsoft Office Excel, using Microsoft Visual Studio 2008 with Visual Studio Tools for Office. (36 printed pages)

Microsoft Icon Jim Corbin, Microsoft Corporation

August 2007

Applies to: Microsoft Office Project Standard 2007, Microsoft Office Project Professional 2007, Microsoft Office Excel 2007, Microsoft Office Excel 2003, Microsoft Visual Studio 2008 Professional Edition (Beta 2)

Contents

  • Overview of Importing Task Data from Excel—Why an Add-In

    • Prerequisites

    • ClickOnce Deployment

    • Differences Between Visual C# and Visual Basic for Add-in Development

    • Finding Help for the Excel and Project Object Models

  • Developing the ImportProject Add-In

    • Adding a Toolbar and Button

    • Creating the Import Dialog Box

    • Importing the Task Data from Excel

    • Developing the CreateTasks Method

    • Building and Testing the ImportProject Add-In

  • Configuring ClickOnce for Deployment

  • Next Steps

  • Conclusion

  • Additional Resources

Download: The download file pj12ImportProjectAddIn.exe includes Visual Studio projects with the complete code for a Project 2007 add-in, using both Visual C# and Visual Basic. The download file also includes the EPM_Example.xls and EPM_Example.xlsx Excel files with sample task data used in testing the add-in and illustrating this article. For the download, see Project 2007: Managed Code Add-in for Importing Tasks from Excel.

Overview of Importing Task Data from Excel—Why an Add-In

This article provides an example of a managed code add-in that uses the Microsoft Visual Studio 2008 template for Microsoft Office Project 2007. When you develop add-ins for Project 2007 and the other 2007 Microsoft Office system client applications, Microsoft Visual Studio Tools for the Microsoft Office system (3.0) is the preferred development platform. In general, when you develop Office Business Applications (OBAs) or complex Microsoft Visual Basic for Applications (VBA) macros, you should consider moving to Visual Studio Tools for Office for the advantages that the Visual Studio product offers in debugging, security, deployment, and ease of maintenance, and for all of the advantages of the Microsoft .NET Framework.

The ImportProject sample add-in shows how to use both the Project 2007 and the Microsoft Office Excel 2007 object model through the primary interop assemblies. Integrating Project and Excel is a common development task.

Many people use Excel for storing project task, resource, assignment, and cost data. Project managers who use Project Standard or Project Professional have a much better tool for project management, but might find it difficult or time-consuming to import existing project data from Excel because there is no standard format or organization of project data in Excel.

With Project 2007, you can open Excel 2003 workbooks and import worksheets if they are dedicated to task, resource, or assignment data. The Import Wizard in Project helps you map the fields from Excel to Project, and you can also save the map to use with other Excel files if they have the same kind and organization of data. Figure 1 shows that the EPM Guide Phase field in the sample Excel file is not mapped; however, you can assign it to a task custom field such as Text1.

Figure 1. Using the Import Wizard in Project

Using the Import Wizard in Project

The Import Wizard might not always perform as you expect in all cases; the imported data can require many manual changes. In those cases, particularly when there are many similar Excel files to import, a custom add-in can help solve the problem. For example, the following jobs are just a few of the things that an add-in can do when importing tasks that the Import Wizard cannot do:

  • Insert, modify, or delete specific tasks based on criteria you set.

  • Change task dates.

  • Add local custom fields based on criteria you specify.

  • Rename local custom fields.

  • Convert from local time to Coordinated Universal Time (UTC).

  • Validate task data and allow you to interactively change data that meets certain conditions.

  • Select a subset of tasks to import.

  • Separate tasks into master and subprojects.

  • Combine data from multiple worksheets. For example, add cost data from one worksheet to the tasks from another worksheet.

  • Integrate with Microsoft Office Project Server 2007 by using the Project Server Interface (PSI). For example, add enterprise custom fields to tasks.

There are similar lists of jobs that an add-in can do when importing resource and assignment data from Excel.

This article shows how to develop and deploy an add-in for Project 2007. The add-in example imports task data and does the following:

  • Adds a toolbar and button to Project that starts the Import From Excel dialog box (see Figure 2).

  • Shows the application version and the ClickOnce published version of the add-in.

  • Enables the user to select the Excel file and worksheet, specify the column letters with the types of task data, and specify the row numbers to import for header and task data. The add-in enables the user to specify a subset of tasks in the Excel worksheet, and then converts the task data so that it is consistent for importing to Project.

  • Uses a DataGridView control to show all of the Excel data to import, before the import occurs.

  • Converts dates to UTC, and shows both local dates and UTC dates before import. For more information, see Coding Best Practices Using DateTime in the .NET Framework.

  • Sets the project start date to the start date of the first task.

  • Renames the Text1 task custom field to the name of the Excel column that contains the custom field data.

  • Does not allow setting a summary task as a predecessor (that causes a COM exception in the Task.TaskDependencies.Add method). Instead, the add-in enables the user to ignore the predecessor task, constrain the task start date, or choose a non-summary task as the predecessor.

  • Handles multiple predecessor tasks.

  • Indents tasks to the correct level according to the specified work breakdown structure (WBS).

  • Helps the user add the task custom field column to the Gantt Chart view of the imported project.

Figure 2. Using the Import From Excel dialog box of the ImportProject add-in

Using the Import From Excel dialog box

Prerequisites

To use the download sample, you must have the following installed on the development computer:

  • Project Standard 2007 or Project Professional 2007

  • Excel 2007 or Excel 2003

The sample Visual Studio add-in projects use version 12.0 of the Microsoft.Office.Interop.MSProject and Microsoft.Office.Interop.Excel assemblies. You can modify the Visual Studio projects to use Project 2003 and Excel 2003 by setting references to version 11.0 of the Interop assemblies.

You can use either Visual Studio 2008 Professional Edition or Visual Studio Team System 2008; both versions include Visual Studio Tools for the Office system. If you use a version later than Visual Studio 2008 Beta 2, some of the default reference assembly names might differ. In that case, create a new Project 2007 add-in, and then copy the code files in the download into the new Visual Studio project.

Users who install the ImportProject add-in must also have the same versions of Project and Excel installed that the development computer uses.

For more information about Visual Studio 2008, see the Visual Studio Developer Center.

ClickOnce Deployment

Before Visual Studio 2008, it was difficult to deploy, manage security of, and install add-ins for Microsoft Office applications. Publishing the add-in to a network share or an Intranet Web site with ClickOnce makes it much easier to distribute the add-in, handle security, and manage prerequisites and updates.

When a user runs Setup.exe from the published site, the ClickOnce installer checks for the .NET Framework 3.5, Microsoft Visual Studio Tools for Office Runtime, and Windows Installer 3.1 prerequisites, and installs them if needed. You can set the interval at which the add-in checks the published site for updates (for example, daily or weekly), and automatically installs an update to the latest ClickOnce version.

NoteNote

Installing the .NET Framework 3.5 is time-consuming. You should notify users who do not already have it installed, and provide the Microsoft URL for downloading the .NET Framework 3.5 as an alternative. You should also notify users to uninstall any earlier version of the .NET Framework 3.5 and Visual Studio Tools for Office Runtime before they try to install the version you deploy.

ClickOnce handles the security issues easily. When you are developing an add-in, you create a test certificate to sign the ClickOnce manifest files. If the publisher cannot be verified, a user can choose whether to install the add-in (see Figure 3). If you add a signed certificate, installation does not show the dialog box for the unknown publisher.

Figure 3. ClickOnce installation with an unknown publisher

ClickOnce installation with an unknown publisher

For more information, see Configuring ClickOnce for Deployment in this article.

Differences Between Visual C# and Visual Basic for Add-in Development

Visual Studio 2008 includes templates for developing application-level add-ins for Project 2003 and Project 2007, in both Microsoft Visual C# and Microsoft Visual Basic. The choice of language primarily depends on your experience and preferences; both have advantages and disadvantages for developing add-ins. Table 1 describes some advantages and disadvantages of each language.

Table 1. Advantages and disadvantages of Visual C# and Visual Basic for add-in development

Language

Advantages

Disadvantages

Visual Basic

  • Microsoft IntelliSense shows optional parameters (see Figure 4).

  • No need to include unspecified optional parameters, for example, Application.FileNew().

  • Supports named arguments, for example, thisProject.Tasks.Add(Name:="Task Name").

  • Offers direct use of properties with parameters, such as the Worksheet.Range property: headerRange = xlWorksheet.Range(startCell, endCell)

Visual C#

  • New language features in Visual C# 2008, such as autoimplemented class properties: public string Wbs { get; set; }

  • Simple use of casts, for example: double startDate = (double)valueField.

  • Simple creation of arrays with a variable number of elements, for example:

  • Does not support optional parameters.

  • Requires System.Type.Missing for unspecified parameters, for example, Application.FileNew(missing, missing, missing, missing).

  • Does not support named arguments.

  • Cannot directly access properties with parameters, such as the Worksheet.Range property. Requires other methods, for example, headerRange = xlWorksheet.get_Range(startCell, endCell).

Whether you see the items in the Disadvantages column of Table 1 as actual disadvantages depends on your point of view. The language I primarily use is Visual C#, so I find it easiest to develop the sample add-in first in Visual C# and then port it to Visual Basic. You can organize the code in any solution in many ways, and there are probably some constructs in the ImportProject sample add-in code that could be improved.

For more information about using Visual C# and Visual Basic for add-in development, see Programming Office Applications Using Visual C#.

Finding Help for the Excel and Project Object Models

The primary interop assemblies for Project and Excel, Microsoft.Office.Interop.MSProject.dll and Microsoft.Office.Interop.Excel.dll, are the managed code interfaces to the Project and Excel client application object models. When you use an object, property, method, or event in a VBA macro, it generally has the equivalent interface in the related primary interop assembly. Visual Studio Tools for Office extends some of the native objects in Excel, such as the Range object, to provide host controls such as NamedRange for use in document-level extensions in Excel. Visual Studio Tools for Office provides an application-level add-in template only for Project, not a document-level template.

The classes and members of the Microsoft.Office.Interop.Excel namespace are documented in Visual Studio 2008. You can search for a class or member in Visual Studio Help, or select a class or member and press F1 to go to the Help page for that item. For more information about using the Excel object model in Visual Studio Tools for Office, search for "Excel Object Model Overview" on MSDN online, or see Excel Object Model Overview (the link might change in later documentation releases).

The Microsoft.Office.Interop.MSProject namespace is not documented in Visual Studio 2008. Because the MSProject primary interop assembly is the managed code interface for the Project object model, you can use VBA Help in Project to find information about the classes and members. Visual Studio IntelliSense also shows the class members and parameters. In Figure 4, Visual Basic shows that all of the parameters for the MSProject.Application.FileNew method are optional. Visual C# does not support optional parameters.

Figure 4. IntelliSense in Visual Studio for the MSProject object model

IntelliSense for the MSProject object model

To access VBA Help, open the Visual Basic Editor in Project, and then click the Help menu. VBA Help for all 2007 Office system client applications is also available in the MSDN Library. For the Project VBA reference online, see the Microsoft Office Project 2007 Developer Reference. The Project 2007 SDK also includes some VBA documentation: see VBA Object Model Changes and Tables of VBA Object Model Changes.

To access updated help topics for VBA when you are working in the Visual Basic Editor, click the Connection Status menu in the lower-right section of the Project Help window, and then click Show content from Office Online. For example, the VBA object model maps (such as the Application and Projects Object Map) are available only through online help. For descriptions of fields in Project, see the online Fields Reference.

Developing the ImportProject Add-In

The primary components of the ImportProject add-in are the ThisAddIn class, which handles all of the interaction with Project, and the ImportDialogBox form, which handles all of the interaction with Excel. The main jobs of the components are as follows:

  • ThisAddIn class. Includes the startup and shutdown event handlers for managing the add-in toolbar and the click event handler for the toolbar button.

  • ImportButtonClick event handler for the toolbar button. Instantiates the ImportDialogBox form.

  • ImportDialogBox. Opens the specified Excel file and worksheet, reads the specified header and task data, validates and adjusts the WBS and task predecessor data if necessary, creates a TaskRow object for each task to import, and then shows the adjusted data in a grid before calling the ThisAddIn.CreateTasks method to import the tasks to Project.

  • CreateTasks method. Opens a new project, sets the project start date to the start date of the first imported task, and renames the local Text1 task custom field to the Excel column header name of the custom field. The CreateTasks method then iterates through an array of the TaskRow objects, adds each task to the project, adds the custom field value, sets the task predecessors, and sets the task outline level. If a task predecessor is a summary task, CreateTasks allows the user to ignore the predecessor or to use the ChoosePredecessor dialog box to constrain the task start date or to choose a non-summary task for the predecessor. Finally, CreateTasks brings up the Column Definition dialog box in Project for adding the task custom field column to the view.

    NoteNote

    The Project object model does not include a way to programmatically set values in the Column Definition dialog box, so the add-in simply explains what the user can interactively set.

A class diagram helps to see all of the main classes and members together. The Visual C# project in the download named ImportProject includes a class diagram named ClassDiagram1. In the Visual Basic project, in Solution Explorer, right-click ImportProject_VB, click View Class Diagram to add the diagram, and then expand and arrange the classes the way you want.

The following procedures show how to develop the ImportProject add-in:

  1. Creating the ImportProject add-in

  2. Adding a Toolbar and Button

  3. Creating the Import Dialog Box

  4. Getting the Excel header data

  5. Importing the Task Data from Excel

  6. Validating and converting the task data

  7. Cleaning up and calling the ThisAddIn.CreateTasks method

  8. Developing the CreateTasks Method

  9. Adding task predecessors

  10. Adding a column in the Gantt Chart view for the task custom field

Procedure 1. To create the ImportProject add-in

  1. Start Visual Studio 2008, click New Project, and then expand the nodes in the Project types column under the language you want to use.

    NoteNote

    If you working on Windows Vista, run Visual Studio as an administrator. Right-click Microsoft Visual Studio 2008 Beta 2, and then click Run as administrator.

  2. Create a new project by using the Office 2007 Project Add-in template (see Figure 5). For example, name the project ImportProject. Figure 5 shows the name as ImportProject_VB because both the Visual C# and Visual Basic projects were created on the same development computer for this article.

    Figure 5. Creating a Visual Studio Tools for Office project in Visual Studio 2008

    Creating a VSTO project in Visual Studio 2008

    NoteNote

    Microsoft Visual Studio Tools for the Microsoft Office system (3.0) projects use the .NET Framework 3.5.

  3. If you are developing with Visual Basic, in Solution Explorer, click Show All Files, so you can see the References and My Project nodes.

  4. Add the following references:

    • Microsoft.Office.Interop.Excel, version 12.0.0.0, matches the MSProject version reference for the Project 2007 add-in.

    • System.Deployment, version 2.0.0.0, is required for showing ClickOnce properties.

    NoteNote

    Version 12 of the Excel primary interop assembly can read both Excel 2003 and Excel 2007 files (just as Excel 2007 can). If you are developing an add-in for Project 2003, use Microsoft.Office.Interop.Excel version 11.0.0.0 with Microsoft.Office.Interop.MSProject version 11.0.0.0. If the primary interop assembly reference version does not match the installed Project or Excel version, you might encounter problems in compiling and ClickOnce deployment.

  5. Add the following declarations to the ThisAddIn class (open ThisAddIn.vb or ThisAddIn.cs).

    Imports System
Imports System.Collections
Imports System.Text
Imports System.Windows.Forms
Imports MSProject = Microsoft.Office.Interop.MSProject
Imports Office = Microsoft.Office.Core

    using System;
using System.Collections;
using System.Text;
using System.Windows.Forms;
using MSProject = Microsoft.Office.Interop.MSProject;
using Office = Microsoft.Office.Core;

Adding a Toolbar and Button

The add-in checks whether a toolbar named ImportBar exists. If the toolbar does not exist, create it, and then add the button and event handler.

Procedure 2. To create the CommandBar and button objects

  1. In the ThisAddIn class, add class variables for the CommandBar and CommandBarButton objects.

  2. Create a method named AddImportToolbar, and then call the method from the ThisAddIn_Startup event handler.

    Public Class ThisAddIn
    Private commandBar As Office.CommandBar
    Private importButton As Office.CommandBarButton

    Private Sub ThisAddIn_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup
        AddImportToolbar()
    End Sub

    . . .

    Private Sub AddImportToolbar()

    End Sub

End Class

    namespace ImportProject
{
    public partial class ThisAddIn
    {
        private Office.CommandBar commandBar;
        private Office.CommandBarButton importButton;

        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
            AddImportToolbar();
        }
        . . .

        private void AddImportToolbar()
        {

        }
    }
}
    NoteNote

    The Visual Basic add-in uses the ImportProject_VB root namespace specified on the Application tab of the ImportProject_VB properties page. The Option Explicit compile option is also on by default on the Compile tab of the ImportProject_VB properties page.

  3. In the AddImportToolbar method, try reading a toolbar named ImportBar. If ImportBar does not exist, the CommandBars object throws an ArgumentException. Catch the exception and do nothing, and then create the CommandBar if necessary.

    Try
   commandBar = Application.CommandBars("ImportBar")
Catch ex As ArgumentException
   ' The toolbar named ImportBar does not exist, so create it.
End Try

If (commandBar Is Nothing) Then
   ' Check the list of command bars.
   'Dim numBars As Integer
   'Dim msg As String
   'numBars = Application.CommandBars.Count
   'msg = String.Format("There are currently {0} command bars: " + vbCrLf, numBars)

   'For i As Integer = 1 To numBars
   '    msg += String.Format("{0}: {1}" + vbCrLf, i, Application.CommandBars(i).Name)
   'Next i
   'MessageBox.Show(msg, "Command Bars in Project", _
   '                MessageBoxButtons.OK, MessageBoxIcon.Information)

   ' Add a command bar named ImportBar to the toolbar area.
   Dim barPosition As Integer = 1
   Dim isTemporary As Boolean = True
   commandBar = Application.CommandBars.Add("ImportBar", barPosition, , isTemporary)
End If

    try
{
    commandBar = Application.CommandBars["ImportBar"];
}
catch (ArgumentException e)
{
    // The toolbar named ImportBar does not exist, so create it.
}
if (commandBar == null)
{
    // Add a commandbar named ImportBar.
    /*   //Check the list of command bars.
    int numBars = Application.CommandBars.Count;
    string msg = string.Format("There are currently {0} command bars: \r\n",
        numBars);
    for (int i = 1; i <= numBars; i++)
    {
        msg += string.Format("\t{0}: {1}\r\n", i, Application.CommandBars[i].Name);
    }
    MessageBox.Show(msg, "Command Bars in Project", MessageBoxButtons.OK, 
       MessageBoxIcon.Information);
    */

    //int barPosition = (int)Office.MsoBarPosition.msoBarLeft;
    // Add the toolbar to the toolbar area.
    int barPosition = 1;
    bool isMenuBar = false;
    bool isTemporary = true;
    commandBar = Application.CommandBars.Add("ImportBar", barPosition, 
                                             isMenuBar, isTemporary);
}

  4. In the AddImportBar method, create a toolbar button named ImportProject that uses a click event handler named ImportButtonClick.

    Try
   importButton = _
       commandBar.Controls.Add(Type:=Office.MsoControlType.msoControlButton)
   importButton.Style = Office.MsoButtonStyle.msoButtonCaption
   importButton.Caption = "Import Project VB"
   importButton.Tag = "importProjectVB"
   importButton.TooltipText = "Import a project from Excel, by using the Visual Basic add-in."
   AddHandler importButton.Click, AddressOf ImportButtonClick

   commandBar.Visible = True

Catch ex As Exception
   MessageBox.Show(ex.Message, "Error adding toolbar button", _
                   MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

    try
{
    importButton = (Office.CommandBarButton)commandBar.Controls.Add(
        Office.MsoControlType.msoControlButton, missing, missing, missing, missing);
    importButton.Style = Office.MsoButtonStyle.msoButtonCaption;
    importButton.Caption = "Import Project";
    importButton.Tag = "importProject";
    importButton.TooltipText = "Import a project from Excel.";
    importButton.Click += 
        new Office._CommandBarButtonEvents_ClickEventHandler(ImportButtonClick);

    commandBar.Visible = true;
}
catch (ArgumentException e)
{
    MessageBox.Show(e.Message, "Error adding toolbar button", 
        MessageBoxButtons.OK, MessageBoxIcon.Error);
}

  5. Add the ImportButtonClick event handler that instantiates a dialog box class named ImportDialogBox (see Procedure 3 to create ImportDialogBox).

    Private Sub ImportButtonClick(ByVal ctrl As Office.CommandBarButton, _
                           ByRef cancel As Boolean)
    Dim importDialog As ImportDialogBox = New ImportDialogBox()
    importDialog.Show()
End Sub

    private void ImportButtonClick(Office.CommandBarButton ctrl, ref bool cancel)
{
   ImportDialogBox importDialog = new ImportDialogBox();
   importDialog.Show();
}

  6. To test the add-in after Step 5, comment out the two statements in the ImportButtonClick event handler, and then add a simple message box.

Creating the Import Dialog Box

For the complete code of ImportDialogBox and property settings of the controls, see the download.

Procedure 3. To create the ImportDialogBox form

  1. Add a Windows Form named ImportDialogBox, and then add the following declarations to the ImportDialogBox.vb or ImportDialogBox.cs file:

    Imports System
Imports System.Collections
Imports System.Deployment.Application     ' For ClickOnce properties.
Imports System.Text
Imports System.Windows.Forms
Imports Office = Microsoft.Office.Core
Imports Excel = Microsoft.Office.Interop.Excel

    using System;
using System.Collections;
using System.Deployment.Application;    // For ClickOnce properties.
using System.Text;
using System.Windows.Forms;
using Office = Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;

  2. On the Project menu, click ImportProject Properties, and then click the Settings tab. The default settings are matched to the test EPM_Example.xlsx file. Create the default user settings for the following properties:

    1. Column1 through Column9, type string, values A through I.

    2. ExcelDirectory, type string, value C:\.

    3. ExcelFile, type string, value [empty].

    4. RowBegin, RowEnd, and RowHeader, type int (Integer in Visual Basic), values 3, 12, and 1.

  3. Add the following class constants and variables. The Visual C# code uses the variable xx for missing parameters in calls to Project methods.

    Private Const DOT_SEPARATOR As Char = "."c
Private Const COMMA_SEPARATOR As Char = ","c

Dim xlDirectory As String
Dim xlFile As String
Dim xlApp As Excel.Application
Dim xlWorkbook As Excel.Workbook
Dim xlWorksheet As Excel.Worksheet
Dim headerRange As Excel.Range
Dim projectRange As Excel.Range

Dim taskRows As ArrayList = New ArrayList()
Dim excelColumns As ArrayList = New ArrayList()
Dim columnLetters As Hashtable = New Hashtable()

    private const char DOT_SEPARATOR = '.';
private const char COMMA_SEPARATOR = ',';

private System.Object xx = System.Type.Missing;
private string xlDirectory;
private string xlFile;

private Excel.Application xlApp;
private Excel.Workbook xlWorkbook;
private Excel.Worksheet xlWorksheet;
private Excel.Range headerRange;
private Excel.Range projectRange;

private ArrayList taskRows = new ArrayList();
private ArrayList excelColumns = new ArrayList();
private Hashtable columnLetters = new Hashtable();

  4. Create the Column enumeration for column names in Excel.

    Enum Column
   TaskId = 1
   WBS = 2
   TaskName = 3
   TaskCF = 4
   Duration = 5
   Start = 6
   Finish = 7
   Predecessors = 8
   Notes = 9
End Enum

    enum Column
{
   TaskId = 1,
   WBS = 2,
   TaskName = 3,
   TaskCF = 4,
   Duration = 5,
   Start = 6,
   Finish = 7,
   Predecessors = 8,
   Notes = 9
}

  5. Create the following controls on the ImportDialogBox form (see Figure 6):

    • BindingSource control: Set the bindingSource1.DataSource value to the array to TaskRow objects in the btnImport_Click event handler.

    • DataGridView control: The btnImport_Click event handler initializes dataGridView1 by using the BindingSource control.

    • OpenFileDialog control: The btnBrowse_Click event handler initializes the openFileDialog1 control.

    • ListBox control: Create the GetWorksheet method to start Excel and initialize listWorksheets. Call GetWorksheet from the btnBrowse_Click event handler and also from the ImportDialogBox constructor (ImportDialogBox_Load event handler in Visual Basic).

      Sub GetWorksheet(ByVal xlfilePath As String)
    xlApp = New Excel.Application()
    ' Do not interrupt with alert dialogs.
    xlApp.DisplayAlerts = False

    xlWorkbook = xlApp.Workbooks.Open(xlfilePath)
    listWorksheets.Items.Clear()

    For Each worksheet As Excel.Worksheet In xlWorkbook.Worksheets
        listWorksheets.Items.Add(worksheet.Name)
    Next
End Sub

      private void GetWorksheet(string xlFilePath)
{
   xlApp = new Excel.Application();
   // Do not interrupt with alert dialogs.
   xlApp.DisplayAlerts = false;

   xlWorkbook = xlApp.Workbooks.Open(xlFilePath,
       xx, xx, xx, xx, xx, xx, xx,
       xx, xx, xx, xx, xx, xx, xx);

   listWorksheets.Items.Clear();

   foreach (Excel.Worksheet worksheet in xlWorkbook.Worksheets)
   {
       listWorksheets.Items.Add(worksheet.Name);
   }
}

    • Button control: The btnBrowse.Text value is three dots. The btnBrowse_Click event handler uses openFileDialog1 to select and open an Excel file.

      Private Sub btnBrowse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
                            Handles btnBrowse.Click
    Dim xlsFiles As String
    xlsFiles = "Excel files (*.xls;*.xlsx)|*.xls;*.xlsx"
    Me.OpenFileDialog1.Filter = xlsFiles
    Me.OpenFileDialog1.Multiselect = False
    Me.OpenFileDialog1.Title = "Select an Excel File with Project Data"
    Me.OpenFileDialog1.InitialDirectory = xlDirectory
    Me.OpenFileDialog1.FileName = xlFile

    Dim dr As DialogResult = Me.OpenFileDialog1.ShowDialog()

    If (dr = DialogResult.OK) Then
        Try
            xlFile = Me.OpenFileDialog1.FileName
            Dim pastLastSlash As Integer = xlFile.LastIndexOf("\\") + 1
            Dim filenameLength = xlFile.Length - pastLastSlash
            xlDirectory = xlFile.Substring(0, pastLastSlash)
            xlFile = xlFile.Substring(pastLastSlash, filenameLength)

            Dim xlfilePath As String = xlDirectory + xlFile
            txtExcelFile.Text = xlfilePath
            GetWorksheet(xlfilePath)
        Catch ex As System.Security.SecurityException
            . . .
        End Try
    End If
End Sub

      private void btnBrowse_Click(object sender, EventArgs e)
{
   string xlsFiles = "Excel files (*.xls;*.xlsx)|*.xls;*.xlsx";
   this.openFileDialog1.Filter = xlsFiles;
   this.openFileDialog1.Multiselect = false;
   this.openFileDialog1.Title = "Select an Excel File with Project Data";
   this.openFileDialog1.InitialDirectory = xlDirectory;
   this.openFileDialog1.FileName = xlFile;

   DialogResult dr = this.openFileDialog1.ShowDialog();
   if (dr == DialogResult.OK)
   {
       try
       {
           xlFile = this.openFileDialog1.FileName;
           int pastLastSlash = xlFile.LastIndexOf(@"\") + 1;
           int filenameLength = xlFile.Length - pastLastSlash;
           xlDirectory = xlFile.Substring(0, pastLastSlash);
           xlFile = xlFile.Substring(pastLastSlash, filenameLength);

           string xlFilePath = xlDirectory + xlFile;
           txtExcelFile.Text = xlFilePath;
           GetWorksheet(xlFilePath);
       }
       catch (System.Security.SecurityException ex)
       {
          . . .
       }
   }
}

    • Button control: The btnImport.Text value is 3. Read Excel Data. The btnImport_Click event handler initializes the columnLetters hash table, imports the Excel column data and task rows, and shows the TaskRow objects in the DataGridView control. For the GetExcelColumnInfo and StoreColumnInfo methods, see Procedure 4. For the ImportTasksFromExcel and ConvertExcelData methods, see Procedure 5.

      Private Sub btnImport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
                          Handles btnImport.Click
   btnCancel.Enabled = True

   ' Load the columnLetters hash table.
   columnLetters.Clear()
   columnLetters.Add(DirectCast(Column.TaskId, Integer), mtxtTaskId.Text.Trim())
   . . .
   columnLetters.Add(DirectCast(Column.Notes, Integer), mtxtNotes.Text.Trim())

   Dim headerCells As Array = GetExcelColumnInfo(xlFile)
   StoreColumnInfo(headerCells)

   Dim projectCells As Array = ImportTasksFromExcel(xlFile)
   ConvertExcelData(projectCells)

   ' Show the TaskRow objects in the DataGridView.
   BindingSource1.DataSource = taskRows
   dataGridView1.DataSource = BindingSource1

   dataGridView1.Enabled = True
   btnCreateTasks.Enabled = True
End Sub

      private void btnImport_Click(object sender, EventArgs e)
{
   btnCancel.Enabled = true;

   columnLetters.Clear();
   columnLetters.Add((int)Column.TaskId, mtxtTaskId.Text.Trim());
   . . .
   columnLetters.Add((int)Column.Notes, mtxtNotes.Text.Trim());

   Array headerCells = GetExcelColumnInfo(xlFile); 
   StoreColumnInfo(headerCells);

   Array projectCells = ImportTasksFromExcel(xlFile);
   ConvertExcelData(projectCells);

   // Show the TaskRow objects in the DataGridView.
   bindingSource1.DataSource = taskRows;
   dataGridView1.DataSource = bindingSource1;

   dataGridView1.Enabled = true;
   btnCreateTasks.Enabled = true;
}

    • Label controls: The listWorksheets_SelectedIndexChanged event handler sets lblUsingWorksheet.Text to the selected worksheet. The ImportDialogBox constructor (ImportDialogBox_Load event handler in Visual Basic) sets lblAppVersion.Text to the add-in application version and sets lblPublishedVersion.Text to the ClickOnce published version. If the add-in is not installed by using ClickOnce, the ApplicationDeployment.IsNetworkDeployed value is false, and the lblPublishedVersion.Text value remains an empty string.

      lblAppVersion.Text = lblAppVersion.Text + Me.ProductVersion

If (ApplicationDeployment.IsNetworkDeployed) Then
    ' This application is installed with ClickOnce.
    Dim currentVersion As String = _
        ApplicationDeployment.CurrentDeployment.CurrentVersion.ToString()

    lblPublishedVersion.Text = lblPublishedVersion.Text + currentVersion
Else
    lblPublishedVersion.Text = String.Empty
End If

      lblAppVersion.Text = lblAppVersion.Text + this.ProductVersion;

if (ApplicationDeployment.IsNetworkDeployed)
{
    // This application is installed with ClickOnce.
    string currentVersion = 
        ApplicationDeployment.CurrentDeployment.CurrentVersion.ToString();
    lblPublishedVersion.Text = lblPublishedVersion.Text + currentVersion;
}
else
{
    lblPublishedVersion.Text = string.Empty;
}

    • TextBox control: The txtExcelFile.Text property is set by the ImportDialogBox constructor (ImportDialogBox_Load event handler in Visual Basic) and by the btnBrowse_Click event handler.

    • MaskedTextBox controls: The ImportDialogBox constructor (ImportDialogBox_Load event handler in Visual Basic) sets the Text property of each MaskedTextBox control to the value specified in the user settings, as in the following example.

      mtxtTaskId.Text = MySettings.Default.Column1
. . .
mtxtRowHeader.Text = MySettings.Default.RowHeader.ToString()

      mtxtTaskId.Text = Properties.Settings.Default.Column1;
. . .
mtxtRowHeader.Text = Properties.Settings.Default.RowHeader.ToString();

    Figure 6. Designing the import dialog box in Visual Studio

    Designing the ImportDialogBox in Visual Studio

The btnImport_Click event handler (for the button labeled 3. Read Excel Data) calls GetExcelColumnInfo, and then calls StoreColumnInfo to store the column letters and names in the excelColumns array of ColumnInfo objects.

Procedure 4. To get the Excel header data

  1. Add a class named ColumnInfo. The class constructor stores the Excel column name, letter, and number of the column in the corresponding class properties.

    Imports System

Public Class ColumnInfo

    Private _name As String
    Private _letter As String
    Private _number As Integer

    Public Sub New(ByVal colName As String, _
                   ByVal colLetter As String, _
                   ByVal nextColNumber As Integer)
        Name = colName
        Letter = colLetter
        Number = nextColNumber
    End Sub

    Public Property Name() As String
        Get
            Return _name
        End Get
        Set(ByVal value As String)
            _name = value
        End Set
    End Property

    Public Property Letter() As String
        Get
            Return _letter
        End Get
        Private Set(ByVal value As String)
            _letter = value
        End Set
    End Property

    Public Property Number() As Integer
        Get
            Return _number
        End Get
        Set(ByVal value As Integer)
            _number = value
        End Set
    End Property

End Class

    using System;

namespace ImportProject
{
    class ColumnInfo
    {
        public ColumnInfo(string colName, string colLetter, int nextColNumber)
        {
            Name = colName;
            Letter = colLetter;
            Number = nextColNumber;
        }

        // Autoimplemented properties are new in Visual C# 2008.
        // Read-only properties have private set.
        public string Name { get; set; }
        public string Letter { get; private set; }
        public int Number { get; private set; }
    }
}

  2. Add the GetExcelColumnInfo method to set the range of header cells and the range of task cells.

    Private Function GetExcelColumnInfo(ByVal file As String) As Array
    Dim startCell As String
    Dim endCell As String
    startCell = columnLetters(DirectCast(Column.TaskId, Integer)) _
                + mtxtRowHeader.Text.Trim()
    endCell = columnLetters(DirectCast(Column.Predecessors, Integer)) _
                + mtxtRowHeader.Text.Trim()
    headerRange = xlWorksheet.Range(startCell, endCell)

    Dim headerCells As Array = Nothing
    headerCells = TryCast(headerRange.Cells.Value2, Array)
    Return headerCells
End Function

    private Array GetExcelColumnInfo(string file)
{
   string startCell = columnLetters[(int)Column.TaskId] 
                      + mtxtRowHeader.Text.Trim();
   string endCell = columnLetters[(int)Column.Predecessors] 
                    + mtxtRowHeader.Text.Trim();
   headerRange = xlWorksheet.get_Range(startCell, endCell);

   Array headerCells = (Array)headerRange.Cells.Value2;
   return headerCells;
}

  3. Create the StoreColumnInfo method to save the ColumnInfo objects in the excelColumns array.

    Private Sub StoreColumnInfo(ByVal headerCells As Array)
    Dim colName As String
    Dim colLetter As String
    Dim numColumns As Integer = headerCells.GetLength(1)
    Dim i As Integer

    For i = 1 To numColumns
        colName = headerCells.GetValue(1, i).ToString()
        colLetter = columnLetters(i).ToString()
     Dim thisColumn As ColumnInfo

     thisColumn = New ColumnInfo(colName, colLetter, i)
     excelColumns.Add(thisColumn)
    Next i
End Sub

    private void StoreColumnInfo(Array headerCells)
{
   string colName, colLetter;
   int numColumns = headerCells.GetLength(1);

   for (int i = 1; i <= numColumns; i++)
   {
       colName = headerCells.GetValue(1, i).ToString();
       colLetter = columnLetters[i].ToString();

       var column = new ColumnInfo(colName, colLetter, i);
       excelColumns.Add(column);
   }
}

Importing the Task Data from Excel

After it gets and stores the Excel column information (see Procedure 4), the btnImport_Click event handler does the following:

  • Calls ImportTasksFromExcel (see Procedure 5, Step 2) to set the range of cells in the worksheet to import.

  • Calls ConvertExcelData (see Procedure 6) to validate the imported task data, make the task predecessors and outline level of each task consistent with the task IDs, create a new TaskRow object for each task, and add the object to the taskRows array.

Procedure 5. To import the task data from Excel

  1. Add a class named TaskRow. The class constructor encapsulates the converted task data such as task ID, WBS, task name, start date, duration, and predecessor IDs.

    NoteNote

    Dates in a TaskRow object are stored only in UTC. Any date can be retrieved in local time or in UTC, for example, with the StartDate or StartDateUTC property.

    The code for the TaskRow class is shown here only in Visual C#, which includes autoimplemented properties where possible. For the Visual Basic code, see the download.

    using System;

namespace ImportProject
{
    class TaskRow
    {
        // Private member variables needed only for storing start and finish dates in
        // Coordinated Universal Time (UTC).  
        private DateTime startDateUTC;
        private DateTime finishDateUTC;

        public TaskRow(double taskId,
                       string wbs,
                       int level,
                       string taskName,
                       string taskCf,
                       string duration,
                       double startDate,
                       double finishDate,
                       string predecessors,
                       string notes)
        {
            TaskId = Convert.ToInt32(taskId);
            Wbs = wbs;
            Level = level;
            TaskName = taskName;
            TaskCustomField = taskCf;
            Duration = duration;
            Predecessors = predecessors;
            Notes = notes;

            // Automation imports dates from Excel as type double. Convert to DateTime. 
            // Store both the local time and the UTC for dates.
            StartDate = DateTime.FromOADate(startDate);
            FinishDate = DateTime.FromOADate(finishDate);

            StartDateUTC = startDateUTC;
            FinishDateUTC = finishDateUTC;
        }
        // Autoimplemented properties. Read-only properties have private set.
        public int TaskId { get; private set; }
        public string Wbs { get; set; }
        public int Level { get; set; }
        public string TaskName { get; set; }
        public string TaskCustomField { get; set; }
        public string Duration { get; set; }
        public string Predecessors { get; private set; }
        public string Notes { get; set; }

        // Store all start and finish times as UTC. Add the DateTimeKind value 
        // so consumer knows whether the date is local or UTC.
        public DateTime StartDate 
        {
            get
            {
                // Convert UTC to local time for a property get.
                return DateTime.SpecifyKind(startDateUTC.ToLocalTime(), 
                    DateTimeKind.Local);
            } 
            set
            {
                startDateUTC = value.ToUniversalTime();
            }
        }

        public DateTime FinishDate 
        {
            get
            {
                // Convert UTC to local time for a property get.
                return DateTime.SpecifyKind(finishDateUTC.ToLocalTime(), 
                    DateTimeKind.Local);
            }
            set
            {
                finishDateUTC = value.ToUniversalTime();
            }
        }
        public DateTime StartDateUTC
        {
            get
            {
                // No conversion needed for a property get of UTC time.
                return DateTime.SpecifyKind(startDateUTC, DateTimeKind.Utc);
            }
            set
            {
                startDateUTC = value;
            }
        }

        public DateTime FinishDateUTC
        {
            get
            {
                // No conversion needed for a property get of UTC time.
                return DateTime.SpecifyKind(finishDateUTC, DateTimeKind.Utc);
            }
            set
            {
                finishDateUTC = value;
            }
        }
    }
}

  2. Create the ImportTasksFromExcel method.

    Private Function ImportTasksFromExcel(ByVal file As String) As Array
    Dim startCell As String
    Dim endCell As String
    startCell = columnLetters(DirectCast(Column.TaskId, Integer)) _
                + mtxtRowBegin.Text.ToString()
    endCell = columnLetters(DirectCast(Column.Notes, Integer)) _
              + mtxtRowEnd.Text.ToString()

    projectRange = xlWorksheet.Range(startCell, endCell)

    Dim projectCells As Array = Nothing
    projectCells = TryCast(projectRange.Cells.Value2, Array)
    Return projectCells
End Function

    private Array ImportTasksFromExcel(string file)
{
   string startCell = columnLetters[(int)Column.TaskId] 
       + mtxtRowBegin.Text.ToString();

   string endCell = columnLetters[(int)Column.Notes] 
       + mtxtRowEnd.Text.ToString();

   projectRange = xlWorksheet.get_Range(startCell, endCell);

   Array projectCells = (Array)projectRange.Cells.Value2;
   return projectCells;
}

Procedure 6. To validate and convert the task data

  1. Create the ConvertExcelData method. For the GetOutlineLevel, GetInitialValueAtLevel, AdjustWbs, and AdjustPredecessors methods, see the following steps.

    Private Sub ConvertExcelData(ByVal projectCells As Array)
    Dim temp As Object               ' Use temporary object to check for null values.
    ' Types must match each object type in the projectCells array.
    Dim inputTaskId As Double
    Dim outputTaskId As Double
    Dim start As Double = 0          ' Start date imported from Excel. 
    Dim finish As Double = 0         ' Finish date imported from Excel.
    Dim wbs As String
    Dim taskName As String
    Dim taskCf As String
    Dim duration As String
    Dim predecessors As String
    Dim notes As String

    Dim row As Integer               ' Rows in the projectCells array.
    Dim initialLevel As Integer = 0  ' Indent level of first task imported from Excel.
    Dim inputLevel As Integer = 0    ' Indent level of current task from Excel.
    Dim outputLevel As Integer = 0   ' Indent level of current task to create in Project.
    Dim initialValue As Integer = 0  ' Initial value of the WBS level that corresponds 
                                     ' to the inputLevel.

    Dim numRows As Integer = projectCells.GetLength(0)

    For row = 1 To numRows           ' There is no row 0 in the projectCells array.
        inputTaskId = projectCells.GetValue(row, DirectCast(Column.TaskId, Integer))
        ' The first task row is task ID 1.
        outputTaskId = Convert.ToDouble(row)
        wbs = projectCells.GetValue(row, DirectCast(Column.WBS, Integer)).ToString()
        inputLevel = GetOutlineLevel(wbs)

        If row = 1 Then
            initialLevel = inputLevel
            initialValue = GetInitialValueAtLevel(wbs, inputLevel)
        End If

        outputLevel = inputLevel - initialLevel

        If outputLevel < inputLevel Then
            wbs = AdjustWbs(wbs, inputLevel, outputLevel, initialValue)
        End If

        taskName = projectCells.GetValue(row, DirectCast(Column.TaskName, Integer)).ToString()

        temp = projectCells.GetValue(row, DirectCast(Column.TaskCF, Integer))
        ' Cannot use temp.ToString() in the IIf statement, if temp is Nothing; 
        ' must re-read projectCells.
        taskCf = IIf(temp Is Nothing, String.Empty, _
                     projectCells.GetValue(row, DirectCast(Column.TaskCF, Integer)))

        duration = projectCells.GetValue(row, DirectCast(Column.Duration, Integer)).ToString()
        ' Cannot use TryCast for value type.
        start = DirectCast(projectCells.GetValue(row, _
                           DirectCast(Column.Start, Integer)), Double)
        finish = DirectCast(projectCells.GetValue(row, _
                            DirectCast(Column.Finish, Integer)), Double)

        temp = projectCells.GetValue(row, DirectCast(Column.Predecessors, Integer))
        predecessors = IIf(temp Is Nothing, String.Empty, _
                          projectCells.GetValue(row, DirectCast(Column.Predecessors, Integer)))

        If inputTaskId <> outputTaskId Then
            predecessors = AdjustPredecessors(predecessors, Convert.ToInt32(inputTaskId), _
                                              Convert.ToInt32(outputTaskId))
        End If

        temp = projectCells.GetValue(row, DirectCast(Column.Notes, Integer))
        notes = IIf(temp Is Nothing, String.Empty, _
                 projectCells.GetValue(row, DirectCast(Column.Notes, Integer)))

        Dim thisTaskRow As TaskRow
        thisTaskRow = New TaskRow(outputTaskId, wbs, outputLevel, taskName, taskCf, _
                                  duration, start, finish, predecessors, notes)
        taskRows.Add(thisTaskRow)
    Next row
End Sub

    private void ConvertExcelData(Array projectCells)
{
   object temp;                // Use temporary object to check for null values.
   // Types must match each object type in the projectCells array.
   double inputTaskId, outputTaskId;
   double start, finish;       // Dates imported from Excel.
   string wbs, taskName, taskCf, 
       duration, predecessors, notes;
   int row;                    // Rows in the projectCells array.
   int initialLevel = 0;       // Indent level of first task imported from Excel.
   int inputLevel = 0;         // Indent level of current task from Excel.
   int outputLevel = 0;        // Indent level of current task to create in Project.
   int initialValue = 0;       // Initial value of the WBS level that corresponds 
                               // to the inputLevel.
   int numRows = projectCells.GetLength(0);

   for (row = 1; row <= numRows; row++) // There is no row 0 in the projectCells array.
   {
       inputTaskId = (double)projectCells.GetValue(row, (int)Column.TaskId);
       // The first task row is task ID 1.
       outputTaskId = Convert.ToDouble(row);
       wbs = projectCells.GetValue(row, (int)Column.WBS).ToString();
       inputLevel = GetOutlineLevel(wbs);

       if (row == 1)
       {
           initialLevel = inputLevel;
           initialValue = GetInitialValueAtLevel(wbs, inputLevel);
       }
       outputLevel = inputLevel - initialLevel;

       if (outputLevel < inputLevel)
           wbs = AdjustWbs(wbs, inputLevel, outputLevel, initialValue);

       taskName = projectCells.GetValue(row, (int)Column.TaskName).ToString();

       temp = projectCells.GetValue(row, (int)Column.TaskCF);
       taskCf = (null == temp) ? string.Empty : temp.ToString();

       duration = projectCells.GetValue(row, (int)Column.Duration).ToString();
       start = (double)projectCells.GetValue(row, (int)Column.Start);
       finish = (double)projectCells.GetValue(row, (int)Column.Finish);

       temp = projectCells.GetValue(row, (int)Column.Predecessors);
       predecessors = (null == temp) ? string.Empty : temp.ToString();

       if (inputTaskId != outputTaskId)
       {
           predecessors = AdjustPredecessors(predecessors,
               Convert.ToInt32(inputTaskId), Convert.ToInt32(outputTaskId));
       }

       temp = projectCells.GetValue(row, (int)Column.Notes);
       notes = (null == temp) ? string.Empty : temp.ToString();

       var taskRow = new TaskRow(outputTaskId, wbs, outputLevel, taskName, taskCf,
                                 duration, start, finish, predecessors, notes);
       taskRows.Add(taskRow);
    }

  2. Create the GetOutlineLevel method. The number of periods in the imported WBS string determines the outline level.

    Private Function GetOutlineLevel(ByVal wbs As String) As Integer
    Dim lvl As Integer = 0
    Dim startIndex As Integer = wbs.IndexOf(".")

    While startIndex > 0
        lvl = lvl + 1
        startIndex = wbs.IndexOf(".", startIndex + 1)
    End While

    Return lvl
End Function

    private int GetOutlineLevel(string wbs)
{
   int lvl = 0;
   int startIndex = wbs.IndexOf('.');

   while (startIndex > 0)
   {
       lvl++;
       startIndex = wbs.IndexOf(".", startIndex + 1);
   }
   return lvl;
}

  3. Create the GetInitialValueAtLevel method. Get the initial value of the WBS level that corresponds to the input level. For example, if the WBS of the first Excel task is 1.3, then the inputLevel = 1, the value of level 0 = 1, and the initial value of level 1 = 3. These values become clearer when you set a breakpoint in the method and trace through it.

    Private Function GetInitialValueAtLevel(ByVal wbsIn As String, _
                                        ByVal inputLevel As Integer) _
                                        As Integer
    Dim result As Integer = 0
    Dim wbsArray() As String
    wbsArray = wbsIn.Split(DOT_SEPARATOR)

    If wbsArray.GetLength(0) > inputLevel Then
        result = Convert.ToInt32(wbsArray(inputLevel))
    End If

    Return result
End Function

    private int GetInitialValueAtLevel(string wbsIn, int inputLevel)
{
   int result = 0;
   string[] wbsArray = wbsIn.Split(DOT_SEPARATOR);
   if (wbsArray.GetLength(0) > inputLevel)
       result = Convert.ToInt32(wbsArray[inputLevel]);

   return result;
}

  4. Create the AdjustWbs method. Change the WBS values to match the output indentation level. For example, if wbsIn = 1.3.1, the inputLevel = 2, the outputLevel = 1, and the adjustedWbs = 1.1.

    Private Function AdjustWbs(ByVal wbsIn As String, ByVal inputLevel As Integer, _
                          ByVal outputLevel As Integer, ByVal initialValue As Integer) _
                          As String
    Dim adjustedWbs As String = String.Empty
    Dim wbsArray() As String = wbsIn.Split(DOT_SEPARATOR)
    Dim numWbsElements As Integer = wbsArray.GetLength(0)
    Dim startAtElement As Integer = inputLevel - outputLevel
    Dim numAdjustedWbsElements = numWbsElements - startAtElement
    Dim i As Integer

    ' Create an array of integers for WBS values that has numWbsElements.
    Dim wbsValues() As Integer = New Integer() {}
    ReDim wbsValues(numWbsElements - 1)
    ' Create an array of integers for adjusted WBS values that has numAdjustedWbsElements.
    Dim adjustedWbsValues() As Integer = New Integer() {}
    ReDim adjustedWbsValues(numAdjustedWbsElements - 1)

    ' Get integer array of input WBS values.
    For i = 0 To numWbsElements - 1
        wbsValues(i) = Convert.ToInt32(wbsArray(i))
    Next i

    ' Load integer array of output WBS values. Adjust the value of the first element.
    For i = 0 To numAdjustedWbsElements - 1
        If i = 0 Then
            adjustedWbsValues(i) = wbsValues(startAtElement) - initialValue + 1
        Else
            adjustedWbsValues(i) = wbsValues(startAtElement + i)
        End If
    Next i

    ' Convert integer array to adjusted WBS string.
    For i = 0 To numAdjustedWbsElements - 1
        adjustedWbs += adjustedWbsValues(i).ToString()

        If i < numAdjustedWbsElements - 1 Then
            adjustedWbs += DOT_SEPARATOR.ToString()
        End If
    Next i

    Return adjustedWbs
End Function

    private static string AdjustWbs(string wbsIn, int inputLevel, 
   int outputLevel, int initialValue)
{
   string adjustedWbs = string.Empty;
   string[] wbsArray = wbsIn.Split(DOT_SEPARATOR);
   int numWbsElements = wbsArray.GetLength(0);
   int startAtElement = inputLevel - outputLevel;
   int numAdjustedWbsElements = numWbsElements - startAtElement;

   int i;

   int[] wbsValues = new int[numWbsElements];
   int[] adjustedWbsValues = new int[numWbsElements - startAtElement];

   // Get integer array of input WBS values.
   for (i = 0; i < numWbsElements; i++)
       wbsValues[i] = Convert.ToInt32(wbsArray[i]);

   // Load integer array of output WBS values. Adjust the value of the first element.
   for (i = 0; i < numAdjustedWbsElements; i++)
   {
       if (i == 0)
           adjustedWbsValues[i] = wbsValues[startAtElement] - initialValue + 1;
       else
           adjustedWbsValues[i] = wbsValues[startAtElement + i];
   }

   // Convert integer array to adjusted WBS string.
   for (i = 0; i < numAdjustedWbsElements; i++)
   {
       adjustedWbs += adjustedWbsValues[i].ToString();
       if (i < numAdjustedWbsElements - 1)
           adjustedWbs += DOT_SEPARATOR.ToString();
   }
   return adjustedWbs;
}

  5. Create the AdjustPredecessors method, which changes the predecessor task IDs to match the adjusted task IDs.

    Private Function AdjustPredecessors(ByVal predecessors As String, ByVal inTaskId As Integer, _
                                   ByVal outTaskId As Integer) As String
    Dim adjustedPreds As String = String.Empty

    If predecessors <> String.Empty Then
        Dim predsArray() As String = predecessors.Split(COMMA_SEPARATOR)
        Dim numPreds As Integer = predsArray.GetLength(0)
        Dim idAdjustment As Integer = inTaskId - outTaskId
        Dim i As Integer

        ' Create integer array of adjusted predecessor values.
        Dim adjustedPredValues() As Integer = New Integer() {}
        ReDim adjustedPredValues(numPreds - 1)

        For i = 0 To numPreds - 1
            adjustedPredValues(i) = Convert.ToInt32(predsArray(i)) - idAdjustment

            ' Do not use a predecessor value less than the first task.
            If adjustedPredValues(i) < 1 Then
                adjustedPredValues(i) = 1
            End If
        Next i

        ' Convert integer array to adjusted predecessors string.
        For i = 0 To numPreds - 1
            adjustedPreds += adjustedPredValues(i).ToString()
            If i < numPreds - 1 Then
                adjustedPreds += COMMA_SEPARATOR.ToString()
            End If
        Next
    End If

    Return adjustedPreds
End Function

    private string AdjustPredecessors(string predecessors, int inTaskId, int outTaskId)
{
   string adjustedPreds = string.Empty;

   if (predecessors != string.Empty)
   {
       string[] predsArray = predecessors.Split(COMMA_SEPARATOR);
       int numPreds = predsArray.GetLength(0);

       int idAdjustment = inTaskId - outTaskId;
       int i;

       // Create integer array of adjusted predecessor values.
       int[] adjustedPredValues = new int[numPreds];

       for (i = 0; i < numPreds; i++)
       {
           adjustedPredValues[i] = Convert.ToInt32(predsArray[i]) - idAdjustment;

           // Do not use a predecessor value less than the first task.
           if (adjustedPredValues[i] < 1) adjustedPredValues[i] = 1;
       }

       // Convert integer array to adjusted predecessors string.
       for (i = 0; i < numPreds; i++)
       {
           adjustedPreds += adjustedPredValues[i].ToString();
           if (i < numPreds - 1)
               adjustedPreds += COMMA_SEPARATOR.ToString();
       }
   }

   return adjustedPreds;
}

The btnCancel and the btnCreateTasks buttons both call the HideImportDialogBox method. The btnCreateTasks_Click event handler also calls the ThisAddIn.CreateTasks method and passes it the custom field column number and the excelColumns and taskRows arrays.

Procedure 7. To clean up and call the ThisAddIn.CreateTasks method

  1. Create the HideImportDialogBox method to store the user settings and close the dialog box.

    Sub HideImportDialogBox()
    MySettings.Default.ExcelDirectory = xlDirectory
    MySettings.Default.ExcelFile = xlFile
    MySettings.Default.Column1 = mtxtTaskId.Text.Trim()
    . . .
    MySettings.Default.Column9 = mtxtNotes.Text.Trim()

    MySettings.Default.RowHeader = Convert.ToInt32(mtxtRowHeader.Text.Trim())
    MySettings.Default.RowBegin = Convert.ToInt32(mtxtRowBegin.Text.Trim())
    MySettings.Default.RowEnd = Convert.ToInt32(mtxtRowEnd.Text.Trim())

    If (xlApp IsNot Nothing) Then
        Dim saveChanges As Boolean = False
        xlWorkbook.Close(saveChanges)
        xlApp.Quit()
    End If

    Me.Hide()
End Sub

    private void HideImportDialogBox()
{
   Properties.Settings.Default.ExcelDirectory = xlDirectory;
   Properties.Settings.Default.ExcelFile = xlFile;
   Properties.Settings.Default.Column1 = mtxtTaskId.Text.Trim();
   . . .
   Properties.Settings.Default.Column9 = mtxtNotes.Text.Trim();

   Properties.Settings.Default.RowHeader = Convert.ToInt32(mtxtRowHeader.Text.Trim());
   Properties.Settings.Default.RowBegin = Convert.ToInt32(mtxtRowBegin.Text.Trim());
   Properties.Settings.Default.RowEnd = Convert.ToInt32(mtxtRowEnd.Text.Trim());

   if (!(null == xlApp))
   {
       bool saveChanges = false;
       xlWorkbook.Close(saveChanges, xx, xx);
       xlApp.Quit();
   }
   this.Hide();
}

  2. Create the btnCreateTasks_Click event handler. Globals is a sealed class that includes the ThisAddIn property. The Globals.ThisAddIn property enables access to the public methods of the ThisAddIn class.

    Private Sub btnCreateTasks_Click(ByVal sender As System.Object, _
                                 ByVal e As System.EventArgs) _
                                 Handles btnCreateTasks.Click
    ' Get the zero-based index for the task custom field column.
    Dim customFieldColumn As Integer
    customFieldColumn = DirectCast(Column.TaskCF, Integer) - 1

    Globals.ThisAddIn.CreateTasks(customFieldColumn, excelColumns, taskRows)

    HideImportDialogBox()

End Sub

    private void btnCreateTasks_Click(object sender, EventArgs e)
{
   // Get the zero-based index for the task custom field column.
   int customFieldColumn = (int)Column.TaskCF - 1;

   Globals.ThisAddIn.CreateTasks(customFieldColumn, excelColumns, taskRows);

   HideImportDialogBox();
}

Developing the CreateTasks Method

The ThisAddIn.CreateTasks method manages all the work for creating a new project, setting the project start date, renaming the local task custom field, and adding tasks to the project.

Procedure 8. To develop the CreateTasks method

  1. Add an outline of the CreateTasks method to the ThisAddIn class. The headerList parameter is the array of ColumnInfo objects, the taskList parameter is the array of TaskRow objects, and the taskCFItem parameter is the index of the custom field item in headerList. Method variables include task data that must be available throughout the CreateTasks method.

    Public Sub CreateTasks(ByVal taskCFItem As Integer, _
                      ByVal headerList As ArrayList, _
                      ByVal taskList As ArrayList)
    Dim taskName As String = String.Empty
    Dim predecessorName As String = String.Empty
    Dim taskId As Integer = -1
    Dim outlineLevel As Integer = -1
    Dim predecessorId As Integer = -1
    Dim previousLevel As Integer = 0
    Dim errorMsg As String

    ' Create a new project.
    Application.FileNew()
    Dim col As ColumnInfo = TryCast(headerList(taskCFItem), ColumnInfo)

    ' Set the project start date to the start date of the first task.
    Dim task1 As TaskRow = TryCast(taskList(0), TaskRow)
    Dim thisProject As MSProject.Project = Application.ActiveProject
    thisProject.ProjectStart = task1.StartDateUTC

    thisProject.Application.CustomFieldRename(MSProject.PjCustomField.pjCustomTaskText1, _
                                              DirectCast(col.Name, Object))

    ' Add each task in the taskList array.
    For i As Integer = 0 To taskList.Count - 1
        ' See Step 2.
    Next i
End Sub

    public void CreateTasks(int taskCFItem, ArrayList headerList, ArrayList taskList)
{
    string taskName = string.Empty;
    string predecessorName = string.Empty;
    int taskId = -1;
    int outlineLevel = -1;
    int predecessorId = -1;
    int previousLevel = 0;
    string errorMsg = string.Empty;

    // Create a new project.
    Application.FileNew(missing, missing, missing, missing);

    ColumnInfo col = (ColumnInfo)headerList[taskCFItem];

    // Set the project start date to the start date of the first task.
    TaskRow task1 = (TaskRow)taskList[0];
    MSProject.Project thisProject = Application.ActiveProject;
    thisProject.ProjectStart = task1.StartDateUTC;

    thisProject.Application.CustomFieldRename(
        MSProject.PjCustomField.pjCustomTaskText1, (object)col.Name, missing);

    // Add each task in the taskList array.
    for (int i = 0; i < taskList.Count; i++)
    {
        // See Step 2.
    }
}

  2. Add code in the for loop that iterates through the number of tasks to create the tasks in the new project. The code does the following jobs:

    • Gets the TaskRow object from taskList and reads the task name, ID, and outline level.

    • Adds the task to the current project. The task is added at the same outline level as the previous task.

    • Sets the Text1 custom field value for the task.

    • Gets the list of predecessors. To add the task predecessors and handle incorrect predecessors, see Procedure 9.

    • Indents or outdents the task to the correct outline level.

    • Handles COM exceptions such as incorrect arguments to Project methods, and separately handles other exceptions such as an out-of-bounds index to an array.

    Try
    Dim tRow As TaskRow = DirectCast(taskList(i), TaskRow)
    taskName = tRow.TaskName
    taskId = tRow.TaskId
    outlineLevel = tRow.Level

    Dim tsk As MSProject.Task = thisProject.Tasks.Add(Name:=tRow.TaskName)
    tsk.Duration = tRow.Duration

    ' Add the task custom field.
    tsk.Text1 = tRow.TaskCustomField
    ' See Procedure 9 for the GetPredecessors method.
    Dim predecessors() As Integer = GetPredecessors(tRow.Predecessors)

    For p As Integer = 0 To predecessors.GetLength(0) - 1
        ' See Procedure 9 for code to handle task predecessors.
    Next p

    ' Indent task to the outline level.
    If outlineLevel < previousLevel Then
        Dim numOutdents As Integer = previousLevel - outlineLevel
        For outdent As Integer = 0 To numOutdents - 1
            tsk.OutlineOutdent()
        Next outdent

    ElseIf outlineLevel > previousLevel Then
        ' Can indent only one level from previous task.
        tsk.OutlineIndent()
    End If

    previousLevel = outlineLevel

Catch ex As System.Runtime.InteropServices.COMException
    errorMsg = ex.Message + vbCrLf + vbCrLf
    errorMsg += ex.StackTrace + vbCrLf + vbCrLf
    errorMsg += "Task ID:" + vbTab + vbTab + taskId.ToString()
    errorMsg += vbCrLf + "Task name:" + vbTab + taskName
    errorMsg += vbCrLf + "Outline level:" + vbTab + outlineLevel.ToString()
    MessageBox.Show(errorMsg, ex.GetType().ToString(), _
                    MessageBoxButtons.OK, MessageBoxIcon.Error)

Catch ex As Exception
    . . .
End Try

    try
{
    TaskRow tRow = (TaskRow)taskList[i];
    taskName = tRow.TaskName;
    taskId = tRow.TaskId;
    outlineLevel = tRow.Level;

    MSProject.Task tsk = thisProject.Tasks.Add(tRow.TaskName, missing);
    tsk.Duration = tRow.Duration;

    // Add the task custom field.
    tsk.Text1 = tRow.TaskCustomField;
    // See Procedure 9 for the GetPredecessors method.
    int[] predecessors = GetPredecessors(tRow.Predecessors);

    for (int p = 0; p < predecessors.GetLength(0); p++)
    {
        // See Procedure 9 for code to handle task predecessors.
    }

    // Indent task to the outline level.
    if (outlineLevel < previousLevel)
    {
        int numOutdents = previousLevel - outlineLevel;
        for (int outdent = 0; outdent < numOutdents; outdent++)
            tsk.OutlineOutdent();
    }
    else if (outlineLevel > previousLevel)
    {
        // Can indent only one level from previous task.
        tsk.OutlineIndent();
    }
    previousLevel = outlineLevel;
}
catch (System.Runtime.InteropServices.COMException ex)
{
    errorMsg = ex.Message + "\n\n";
    errorMsg += ex.StackTrace + "\n\n";
    errorMsg += "Task ID:\t\t" + taskId.ToString();
    errorMsg += "\nTask name:\t" + taskName;
    errorMsg += "\nOutline level:\t" + outlineLevel.ToString();
    MessageBox.Show(errorMsg, ex.GetType().ToString(), 
        MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (Exception ex)
{
    . . .
}

A task can have zero, one, or multiple task predecessors. The predecessors variable is an array of task ID integers that is initialized with the GetPredecessors method. The TaskDependencies.Add method in the MSProject primary interop assembly throws a COM exception if the predecessor is a summary task.

The for loop to set predecessor tasks checks whether each predecessor is a summary task; if so, it calls the ChoosePredecessorTask method, which resets the predecessorId value to a non-summary task ID or to zero. If the predecessorId is zero, the loop constrains the task start date rather than adding a task dependency.

Procedure 9. To add task predecessors

  1. Create the GetPredecessors method that returns an array of task predecessors. The sPredecessors parameter can be an empty string ("") or can have one or more predecessor tasks (for example, "34,38").

    Private Function GetPredecessors(ByVal sPredecessors As String) _
                    As Integer()
    Dim separators() As Char = New Char() {" "c, ","c}
    Dim predecessorList() As String = sPredecessors.Split(separators, _
    StringSplitOptions.RemoveEmptyEntries)

    Dim numList As Integer = predecessorList.GetLength(0)
    Dim result() As Integer = New Integer() {}
    ' If numList = 0, then ReDim result(-1) creates an array 
    ' with zero length.
    ' To test COM exception handling, use:
    '     ReDim result(numList)
    ReDim result(numList - 1)

    For i As Integer = 0 To numList - 1
        result(i) = Convert.ToInt32(predecessorList(i))
    Next

    Return result
End Function

    private int[] GetPredecessors(string sPredecessors)
{
    char[] separators = { ' ', ',' };
    string[] predecessorList = sPredecessors.Split(separators,
        StringSplitOptions.RemoveEmptyEntries);

    int numList = predecessorList.GetLength(0);
    // To test COM exception handler, use:
    //     int[] result = new int[numList + 1]
    int[] result = new int[numList];

    for (int i = 0; i < numList; i++)
    {
        result[i] = Convert.ToInt32(predecessorList[i]);
    }

    return result;
}

  2. Create the ChoosePredecessorTask method.

    Private Function ChoosePredecessorTask(ByVal proj As MSProject.Project, _
                                       ByVal task As MSProject.Task, _
                                       ByVal oldPredecessorId As Integer, _
                                       ByVal predecessorName As String) _
                                       As Integer
    Dim newPredecessorId As Integer = -1
    Dim title As String = "Predecessor is a Summary Task"
    Dim msg As String = "Cannot link task to a summary task." + vbCrLf + vbCrLf
    msg += "Task ID: " + vbTab + vbTab + task.ID.ToString()
    msg += vbCrLf + "Task name: " + vbTab + task.Name
    msg += vbCrLf + "Outline level: " + vbTab + task.OutlineLevel.ToString()
    msg += vbCrLf + "Predecessor ID: " + vbTab + oldPredecessorId.ToString()
    msg += vbCrLf + "Predecessor name: " + predecessorName
    msg += vbCrLf + vbCrLf + "Choose a different precedessor task, "
    msg += "or constrain the start date?"

    Dim result As DialogResult = _
        MessageBox.Show(msg, title, MessageBoxButtons.YesNo, MessageBoxIcon.Question)

    If result = DialogResult.Yes Then
        Dim frmChoosePredecessor As ChoosePredecessor = New ChoosePredecessor(proj)
        result = frmChoosePredecessor.ShowDialog()

        If result = DialogResult.OK Then
            newPredecessorId = frmChoosePredecessor.PredecessorId
        ElseIf result = DialogResult.Ignore Then
            newPredecessorId = 0
        End If
    End If

    Return newPredecessorId
End Function

    private int ChoosePredecessorTask(MSProject.Project proj, MSProject.Task task,
    int oldPredecessorId, string predecessorName)
{
    int newPredecessorId = -1;
    string title = "Predecessor is a Summary Task";
    string msg = "Cannot link task to a summary task.\n\n";
    msg += "Task ID:\t\t" + task.ID.ToString();
    msg += "\nTask name:\t" + task.Name;
    msg += "\nOutline level:\t" + task.OutlineLevel.ToString();
    msg += "\nPredecessor ID:\t" + oldPredecessorId.ToString();
    msg += "\nPredecessor name: " + predecessorName;
    msg += "\n\nChoose a different predecessor task, or constrain the start date?";

    DialogResult result =
        MessageBox.Show(msg, title, MessageBoxButtons.YesNo, MessageBoxIcon.Question);

    if (result == DialogResult.Yes)
    {
        ChoosePredecessor frmChoosePredecessor = new ChoosePredecessor(proj);
        result = frmChoosePredecessor.ShowDialog();

        if (result == DialogResult.OK)
            newPredecessorId = frmChoosePredecessor.PredecessorId;
        else if (result == DialogResult.Ignore)
            newPredecessorId = 0;
    }
    return newPredecessorId;
}

  3. Create the ChoosePredecessor dialog box. Add a Windows Form named ChoosePredecessor that uses the proj parameter in the constructor to show all of the non-summary tasks of the current project in a list (see Figure 7). The user can select one of the non-summary tasks for the predecessor, or choose to have no predecessor and constrain the task start date.

    Because the ChoosePredecessorTask method uses frmChoosePredecessor.ShowDialog, the ChoosePredecessor dialog box can return one of three DialogResult values. For the ChoosePredecessor dialog box sample code, see the download.

    Figure 7. Using the ChoosePredecessor dialog box

    Using the ChoosePredecessor dialog box

  4. Add code to the for loop that iterates over the task predecessors in the CreateTasks method. For information about the values for constraining task dates, search for PjConstraint in Project VBA Help, or see PjConstraint Enumeration in the MSDN Library.

    predecessorId = predecessors(p)

Dim isSummary As Boolean = _
    DirectCast(thisProject.Tasks(predecessorId).Summary, Boolean)

' Do not use a summary task as a predecessor.
If isSummary Then
    ' Choose a different predecessor task, or constrain the start date.
    predecessorName = _
        DirectCast(thisProject.Tasks(predecessorId).Name, String)
    predecessorId = ChoosePredecessorTask(thisProject, tsk, _
                                          predecessorId, predecessorName)
End If

If predecessorId > 0 Then
    ' Set the task predecessor.
    tsk.TaskDependencies.Add(thisProject.Tasks(predecessorId), _
                             MSProject.PjTaskLinkType.pjFinishToStart)
ElseIf predecessorId = 0 Then
    ' Start the task on the specified start date.
    tsk.ConstraintType = MSProject.PjConstraint.pjSNET
    tsk.ConstraintDate = tRow.StartDate
End If

    predecessorId = predecessors[p];

bool isSummary = (bool)thisProject.Tasks[predecessorId].Summary;

// Do not use a summary task as a predecessor.
if (isSummary)
{
    // Choose a different predecessor task, or constrain the start date.
    predecessorName = (string)thisProject.Tasks[predecessorId].Name;
    predecessorId = ChoosePredecessorTask(thisProject, tsk,
        predecessorId, predecessorName);
}
if (predecessorId > 0)
{
    // Set the task predecessor.
    tsk.TaskDependencies.Add(thisProject.Tasks[predecessorId],
        MSProject.PjTaskLinkType.pjFinishToStart, missing);
}
else if (predecessorId == 0)
{
    // Start the task on the specified start date.
    tsk.ConstraintType = MSProject.PjConstraint.pjSNET;
    tsk.ConstraintDate = tRow.StartDate;

Finally, you can insert a column in the Gantt Chart view to show the task custom field. The ColumnInsert method simply opens the Column Definition dialog box in Project. Because the Project object model does not include a way to programmatically set fields in the Column Definition dialog box, the sample code uses a MessageBox that explains to the user what to do.

Procedure 10. To add a column to the Gantt Chart view for the task custom field

  1. Add code at the bottom of the CreateTasks method, after the for loop that creates tasks, for a MessageBox that explains what to do in the Column Definition dialog box. The col variable is the ColumnInfo object for the task custom field, which includes the column name in Excel.

    Dim title As String = "Add the Text Custom Field Column"
errorMsg = "In the Column Definition dialog box:"
errorMsg += vbCrLf + vbTab + "1. Select the Field name:  Text1 (" + col.Name + ")"
errorMsg += vbCrLf + vbTab + "2. Type the column title:  " + col.Name
errorMsg += vbCrLf + vbTab + "3. Click OK"
MessageBox.Show(errorMsg, title)

    string title = "Add the Text Custom Field Column";
errorMsg = "In the Column Definition dialog box:";
errorMsg += "\n\t1. Select the Field name:  Text1 (" + col.Name + ")";
errorMsg += "\n\t2. Type the column title:  " + col.Name;
errorMsg += "\n\t3. Click OK";
MessageBox.Show(errorMsg, title);

  2. Set a column number and show the Column Definition dialog box. For example, insert a column to the left of column four (Duration).

    Dim columnNum As Integer = 4
Application.SelectColumn(Column:=columnNum)
Application.ColumnInsert()

    int columnNum = 4;
Application.SelectColumn(columnNum, missing, missing, missing);
Application.ColumnInsert();

Building and Testing the ImportProject Add-In

Each sample add-in project in the download needs a valid test certificate for the ClickOnce manifest files. Before you build the project, create your own test certificate.

Caution noteCaution

When you create a test certificate, Visual Studio adds a security certificate to your collection of personal certificates. If you create more than one test certificate in the course of building and testing the add-in, keep only the most recent certificate and delete the older test certificates. To see your personal certificates, type certmgr.msc in the Run dialog box, double-click Certificates - Current User, expand the Personal folder, and then click Certificates. A test certificate is issued to DOMAIN\YourAlias, has an expiration date one year from the date you create it, and shows <All> in the Intended Purposes column. To delete a test certificate, right-click the certificate, and then click Delete. Be sure to delete only the unused certificates you created for the add-in.

Procedure 11. To create a test certificate

  1. On the Project menu, click ImportProject Properties (or ImportProject_VB Properties).

  2. On the Signing tab, select Sign the ClickOnce manifests, and then click Create Test Certificate.

  3. In the Create Test Certificate dialog box, you can leave the password blank, and then click OK. Visual Studio creates a test certificate issued to you.

    NoteNote

    For testing purposes, you do not need to install the certificate. If you want to install the certificate, click More Details, and then click Install Certificate in the Certificate dialog box.

After you build either of the Visual Studio add-in projects in the download, to run the add-in by using the Visual Studio debugger, press F5. Project 2007 starts and the ImportProject add-in creates the toolbar and button. When you click Import Project (or Import Project VB), the Import From Excel dialog box (see Figure 2) comes up.

The default columns and rows on the Settings tab of the Visual Studio project properties page correspond to the EPM_Example.xlsx (or the EPM_Example.xls) test file provided with the download. Select the test file, and then click the Task_Table worksheet. The header data is on row 1 and the default set of tasks to import are rows 3–12. Figure 8 shows the EPM_Example.xlsx file opened in Excel 2007. There is no need to import task 1 (row 2) in the test Excel file, because that is effectively the project summary task.

Figure 8. EPM_Example.xlsx test file in Excel 2007

EPM Example.xlsx test file in Excel 2007

In the Import From Excel dialog box, click 3. Read Excel Data, and then click 4. Create Tasks in Project. The add-in imports the tasks and then closes the Import From Excel dialog box. Figure 9 shows the import results in Project. Task ID 2 (row 3) in Excel, with WBS value 1.1, becomes task 1 in the imported project. The WBS, outline level, and predecessor values of all the tasks are adjusted accordingly.

Figure 9. Results of importing the Excel task rows 3–12

Results of importing the test file

You can even run two instances of Visual Studio and test both the ImportProject and the ImportProject_VB add-ins at the same time. Each add-in checks whether a toolbar named ImportBar exists, creates the toolbar if it does not exist, and then adds a button to the toolbar. Figure 9 shows both of the buttons for the ImportProject and the ImportProject_VB add-ins running in Project.

To test cases where a predecessor task is a summary task, try the following:

  • Select task rows 3–235 in the Excel test file. Imported task 76 ("Milestone 5 - Proof of Concept Deployment Complete") has a predecessor summary task. Figure 7 shows the resulting Choose Predecessor Task dialog box. For example, select imported task 75, and then click Set Predecessor Task.

  • Select task rows 13–135 in the Excel test file. In this case, imported task 4 has a predecessor task that is before row 13. Try using the Choose Predecessor Task dialog box to constrain the task start date.

You can test the exception handlers in several ways. For example, in the ThisAddIn.GetPredecessors method (see Procedure 9, Step 1), create an extra number of predecessors by using the statement ReDim result(numlist) in Visual Basic, or int[] result = new int[numList + 1] in Visual C#. Figure 10 shows the result when you import the default set of tasks. The imported task 1 has no predecessors, but the for loop that iterates through predecessors in the CreateTasks method tries to set an empty predecessor. The clause thisProject.Tasks[predecessorId] (the clause thisProject.Tasks(predecessorId) in Visual Basic) throws a COM exception.

Figure 10. Testing the COM exception handler

Testing the COM exception handler

Because the modified add-in creates an extra empty predecessor for every task, to quit the test, click the Stop Debugging button in Visual Studio, and then fix the code in the GetPredecessors method.

To test the general exception handler in the CreateTasks method, for example, use an incorrect index to iterate over the taskList array. In Visual Basic, use For i As Integer = 0 To taskList.Count. In Visual C#, use for (int i = 0; i <= taskList.Count; i++).

Configuring ClickOnce for Deployment

Make sure you have created your own test certificate on the Signing tab of the ImportProject Properties page (see Procedure 11). After you configure ClickOnce properties, you can publish the project for others to install. Procedure 12 shows how to publish to a network share. For more information about specifying publishing locations and other configuration settings, see Publishing ClickOnce Applications.

Procedure 12. To configure ClickOnce for deployment on a network share

  1. On the ImportProject Properties page in Visual Studio, click the Publish tab.

  2. Type the UNC path of the share in the Publishing Folder Location drop-down list. For example, type \\ServerName\Published\ImportProject\.

  3. Click Prerequisites. In the Prerequisites dialog box, click Download prerequisites from the same location as my application, and then click OK.

    NoteNote

    By default, the selected prerequisites include Windows Installer 3.1, .NET Framework 3.5, and Microsoft Visual Studio Tools for Office Runtime 3.0.

  4. Click Updates. In the Customization Updates dialog box, click Check every time the customization runs.

    For testing purposes, it helps to check whether an installation automatically updates to a new published version. Because it takes time for an installed copy of the ImportProject add-in to check the installation site for updates, normally, for example, you could check at intervals of 7 days.

  5. In the Publish Version section, you can manually set the build and revision numbers, or select Automatically increment revision with each release.

  6. When all settings are correct, and the project builds without errors, click Publish Now. You can alternately click Publish ImportProject on the Build menu.

A user who has access to the network share and also has Project 2007 and Excel 2007 installed can install the ImportProject add-in. The ClickOnce Deployment section in this article describes the general user experience of installing the add-in by using ClickOnce.

Next Steps

The ImportProject add-in is a sample that works with a specific Excel file if the user correctly selects a set of task rows to import. There are probably some things you need to change to import your own Excel files. For example, an Excel file could show task levels using different columns rather than using a WBS field. Following are some of the things you might need to do:

  • Set the types of actual task data to import in the Excel Columns section of the Import From Excel dialog box.

  • Modify the ColumnInfo and TaskRow class properties, and the Column enumeration, to match the actual column data in your Excel file.

  • Adjust the columnLetters hash table initialization.

  • Adjust the fields on the Settings tab of the ImportProject Properties dialog box in Visual Studio, and add initialization and saving of user settings in the ImportDialogBox class.

  • Modify the ThisAddIn.CreateTasks method to handle the specific task data.

There are many additional things you could do to improve and extend the add-in, such as the following:

  • Read the range of contiguous data on the selected worksheet and allow the user to select the header and task data rows from a grid.

  • Allow the user to select the task text custom field, and interactively rename the custom field.

  • Add additional validation of task data and ways to programmatically or interactively modify data that would otherwise create COM exceptions on import.

  • Create custom dialog boxes for importing resource, assignment, and cost data.

  • Save and load maps of Excel to Project data imports.

  • Add a dialog box that shows enterprise task custom fields from Project Server by using the PSI, and allow the user to select enterprise task custom fields.

  • Save the imported project to Project Server, by using the Project object model (there is no need to use the PSI when you programmatically save with the Project client).

Conclusion

The Project 2007 and Project 2003 add-in templates in Visual Studio Tools for the Office system (3.0) greatly improve the process of developing managed code add-ins for Project and help to integrate Project with other Microsoft Office system client and server applications. Although Project includes an Import Wizard for importing task, resource, and assignment data from Excel, a custom add-in can do additional jobs and save time when there are many similar Excel files to import. ClickOnce publishing in Visual Studio 2008 makes it easier to deploy, install, and update add-in solutions.

The choice between Visual C# and Visual Basic for developing add-ins depends on personal preference. Both languages are fully capable of creating complex add-ins and integrating Microsoft Office system applications. The ImportProject and ImportProject_VB samples in the download are functionally identical.

Because there are many possible arrangements of project data in Excel files, the sample code is designed to work with the sample Excel files in the download. The code shows only an example of how to integrate Project and Excel. To use the sample for importing task data from other Excel files would probably require changes in the code, as described in Next Steps.

Additional Resources

For more information, see the following additional resources. These resources include duplicates of SDK topic references and external links found in this document.