Automating Project

There have been a few posts on the newsgroups about automating MS Project from Excel, so I thought a simple example might be in order. This example takes task information from a spreadsheet and creates tasks in a project. The worksheet looks like this:


This macro opens a Project and loops through cells, adding Tasks along the way.

Sub TransferTasks()

    Dim prjApp As MSProject.Application
    Dim prjProject As MSProject.Project
    Dim prjTask As MSProject.Task
    Dim rCell As Range
    Set prjApp = New MSProject.Application
    prjApp.FileOpen “C:DickTestProject.mpp”
    Set prjProject = prjApp.ActiveProject
    For Each rCell In Sheet1.Range(“A2:A6?).Cells
        Set prjTask = prjProject.Tasks.Add(rCell.Value)
        With prjTask
            .Start = rCell.Offset(0, 1).Value
            .Duration = rCell.Offset(0, 2).Value
        End With
    Next rCell
End Sub

The resulting project looks like this:


By the way, what’s the deal with the FileOpen method? Is Projects.Open too complicated? I think I need to post a scathing rant of other object models and how they should be more like Excel.

Posted in Uncategorized

4 thoughts on “Automating Project

  1. Hi,

    when I execute the code
    prjApp.FileOpen “C:DickTestProject.mpp”

    in my excel file and run the macro nothing seems to happen. WHy is the project file not opening. The execution just seems to pass right through this line. THere is no error message.

    I am trying to search for a way to:
    1. Open a MS Project File using a macro from MS Excel
    2. Run a macro (within MS Project) using a macro from MS Excel

    Is there any way I can do these things?

    Please email me if you know.


  2. instead of

    Set prjApp = New MSProject.Application
    prjApp.FileOpen “C:DickTestProject.mpp”
    Set prjProject = prjApp.ActiveProject


    Set prjApp = New MSProject.Application
    prjApp.FileNew SummaryInfo:=False
    Set prjProject = prjApp.ActiveProject

  3. to run a macro in MS project use

    ‘Run a macro.
    prjApp.Macro “name_of_macro_here”

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.