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
prjApp.FileSave
prjApp.Quit
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.
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.
Thanks.
instead of
Set prjApp = New MSProject.Application
prjApp.FileOpen “C:DickTestProject.mpp”
Set prjProject = prjApp.ActiveProject
use
Set prjApp = New MSProject.Application
prjApp.FileNew SummaryInfo:=False
Set prjProject = prjApp.ActiveProject
to run a macro in MS project use
‘Run a macro.
prjApp.Macro “name_of_macro_here”
Thank You