Sunday, February 24, 2008

MS Project- Use the TaskTable to Get the Fields Project Displays

If you're building an application that uses data about tasks in an MS Project project plan, you may simply need all the properties of all the tasks, or you may wish to use the data as it's currently displayed in MS Project. A task has dozens of pieces of data associated with it; only some are displayed in the current view.

Remember that Project uses views and tables. You can see these and work with them using the menus. Typically you're; working with a given project using one particular view. That view specifies the table to be used, and the table specifies the pieces of data to be shown.

The code below lists all the tasks in the active project to the immediate window. It is meant to be run as a Project macro. Worth ponting out:

  • use a Tasktable and its TableFields collection to get the fields (columns) in the current view's table
  • the CurrentTable property of your project gives you a table name, which you can use to get the Tasktable object
  • field/column names/headings
    • TableFields have long int id numbers that match up to constants, such as pjTaskName, which is the constant for the task name field
    • fields have names, but those names can be overridden in the table by the user
    • custom fields have names too, which you get at a different wany than you get the name of a regular field
    • I developed the function GetFieldName() to find the name for a field, whichever type of field it is
  • you can use a tasks GetField method to get the data for a particular field by its id number, and it comes formatted as it would be in Project

This code is not super useful on its own, but it should help you get started using the Tasktable object to get data from project the way it's displayed in Project.

Public Sub ListTasks()

  Dim objProject As MSProject.Project
  Dim objTask As MSProject.Task
  Dim objTableField As MSProject.TableField
  Dim objTaskTable As MSProject.Table

  Set objProject = Application.ActiveProject
  Set objTaskTable = objProject.TaskTables(objProject.CurrentTable)

  For Each objTask In objProject.Tasks
    If Not objTask Is Nothing Then ' check for blank rows
      Debug.Print "New task ID#" & objTask.ID
      For Each objTableField In objTaskTable.TableFields
        Debug.Print "  " & GetFieldName(objTableField) & "= " & objTask.GetField(objTableField.Field)
      Next objTableField
    End If

  Set objProject = Nothing
  Set objTask = Nothing
  Set objTableField = Nothing
  Set objTaskTable = Nothing
End Sub

Private Function GetFieldName(ByVal objField As MSProject.TableField) As String
  ' find the field name (actually colmn heading) for a field (column) in a data table

  Dim lngFieldID As Long
  Dim strResult As String

  lngFieldID = objField.Field

  With objField.Application
    strResult = Trim(objField.Title) ' first choice is to use the title specified for the column in the table
    If Len(strResult) = 0 Then
      ' try to get the custom field name- this will come back blank if it's not a custom field
      strResult = Trim((CustomFieldGetName(lngFieldID)))
    End If
    If Len(strResult) = 0 Then
      strResult = Trim(.FieldConstantToFieldName(lngFieldID)) ' use the field name
    End If
  End With

  GetFieldName = strResult
End Function
MSProject Interoperability Automation VBA


Anonymous said...

thanks a lot for sharing. This was the key to start with programming in MS Project.

Anonymous said...


This was very helpfull. Can you help me with another question?
I am writing some VBA for MS Project 2003 and I am trying to find out how to use an object property as a variable.
For example here is the intent of what I want to do (the following code does not work but it should give you an idea of what I am trying to do)
FldName = InputBox("Enter Field", vbOK)
For Each xTask In ActiveProject.Tasks
Debug.Print xTask.FldName '<-- errors out here
Next xTask