I have a number of examples of exporting to Excel from MS Project (exporting task hierarchy and exporting monte carlo simulation data) but many people want to do just the opposite. They want to open a Project file from Excel. So here is a short example which shows opening the Project application from excel, creating a new file and adding a task.
The first thing that you need to do is to set a reference to the Microsoft Project Object Library. To do this go to 'the "Tools Menu" in the Visual Basic Editor (hit ALT+F11 to get there from Excel).
This should bring up a dialog box showing all of the available libraries. You will probably have to scroll down a bit to find the project library. Here you can see that I'm using Project 2002 as it is version 10.
Once you have set the reference the code is pretty simple:
Sub openMSProjectFromExcel()
Set pjApp = CreateObject("MSProject.application")
'this checks to see if a valid object has been created. If not it pops up
'a warning and then quits. Users without Project installed will see this message.
If pjApp Is Nothing Then
MsgBox "Project is not installed"
End
End If
'now that we have an application we make it visible
pjApp.Visible = True
'we add a new project
Set newProj = pjApp.Projects.Add
'we set the title property (you can do whatever you want here.
newProj.Title = "My New Project"
'we make the new project the active project
Set ActiveProject = newProj
'and finally we add a new task to the project
newProj.Tasks.Add ("My First Task")
End Sub
Obviously you will want to do more where this leaves off, but it should be enough to get you started with using Project from Excel.
Comments (6)
i do it this project
Posted by manoj kumar d | September 24, 2007 10:39 PM
Posted on September 24, 2007 22:39
Project2Excel is an reporting and integration tool for Microsoft Project and Microsoft Excel. User defined templates provide users the ability to import/export data to/from Excel while retaining the hierarchical structure contained within Microsoft Project. This product provides an extended Excel formula language to support calculations based on the hierarchical structure of the project plan. VBA Source Code is included.
The product is only $29.95 and can be found at http://www.project2excel.com
Posted by Jeff Dalton | November 14, 2007 6:13 AM
Posted on November 14, 2007 06:13
dear sir
i need help for Microsoft Project Object Library
i need a refrence or text on this subject
i want access to my .mpp file data in excel please help me
Posted by Gelareh Mansouri | September 7, 2008 2:49 AM
Posted on September 7, 2008 02:49
Works a treat, many thanks appreciate you posting the info sir
Posted by ben phillips | January 17, 2009 4:30 AM
Posted on January 17, 2009 04:30
It is missing a carriage return inserted in the following line:
'we add a new project Set newProj = pjApp.Projects.Add
which will give the following new lines:
'we add a new project
Set newProj = pjApp.Projects.Add
Thanks for posting the code.
--------------------
Good catch. I fixed it above. -Jack
Posted by Crawford Young | June 15, 2009 7:03 AM
Posted on June 15, 2009 07:03
i want excel project
Posted by Ahsan Abbas | July 2, 2011 10:55 AM
Posted on July 2, 2011 10:55