Using VBA with Microsoft Project to access projects and tasks

Introduction

If you are using another Office application like Excel to manipulate Microsoft Project then you will need to first create an application.  Please follow my first  blog post to learn how to create an Application Object and open a Project file.

Part 1: Opening and Using Microsoft Project from Excel

To access projects tasks we need to have the right Project object.  We can get this from two methods.

Project Object

First, we can loop through all the projects that are open against the Microsoft Project application.

For Each prj In App.Projects
  Debug.Print "Project Name: " + prj.Name
Next prj

Alternatively, if we are running our VBA Macro from inside the Project application we may want to automatically select the active project.  To do this, we can use the ActiveProject variable that always points to the  current project which is active withing Microsoft Project.

 Debug.Print "Project Name: " + ActiveProject.name

Project Attributes

There are a lot of properties and methods that can be accessed through the Project object.  There are a few common ones that will be really useful in your scripts that are listed below but for a full set you will need to look at the Project VBA Reference material online.

' The FullName is the path name and file name of the project file
Debug.Print "Project Full Name: " + ActiveProject.FullName
Debug.Print "Project No Tasks: " + CStr(ActiveProject.NumberOfTasks)
Debug.Print "Project Start: " + CStr(ActiveProject.ProjectStart)

Tasks

One of the properties of the Project class is a Collection of Task objects.  We can loop through this collection to access all the tasks in the MSP Project Plan.

Dim t As Task

For Each t In ActiveProject.Tasks
    If Not t Is Nothing Then
        Debug.Print "Task ID: " + CStr(t.ID)
        Debug.Print "Task: " + t.Name
        Debug.Print "Duration: " + CStr(t.Duration)
        Debug.Print "Start Date: " + CStr(t.Start)
        Debug.Print "Finish Date: " + CStr(t.Finish)
        Debug.Print "Work: " + CStr(t.Work)
    End If
Next t

Above I have included again a subset of the many Task properties that you may wish to use in your code.  You can also write to these properties, for example, to update a task name through your code.

Resources and Assignments

When you deal with tasks within Microsoft Project, there are different tables of information that Project uses in the background to resource the task.  The Resource object holds information about the resource (you can view on the Resource Sheet view of MSP) and the Assignment object deals with the assignment of a Resource to a Task to do some work.

The Task object has a Resources collection of Resource objects which you can use to list out the resources assigned to the task.

Dim r As Resource

For Each r In t.Resources
   Debug.Print "Resource: " + r.Name
Next r

In my next blog post on VBA with Projects, I will explore in more detail Assignments and how to work with them.

Download Full File

The full file for the code below can be downloaded from here (It uses a proof of work formula before opening):

MSP VBA - Part 2 - Using projects and tasks in VBA

About Me

If you like this post then please comment below.  Please also make a small contribution so that I can post more code snippets and samples here.

Bitcoin:      1MhaZvTqftdebwNihisrAbz22zaSG6tFcN

Ethereum:  0xe69b176A1A5492f19E3595389EA094C732723b69