How to export Excel predecessor data to MS Project?

Question

I have four columns in my workbook. e.g. ID Column can be ignored.

ID	Task Name	Resource Names	Predecessors
1	Task1	Employee1	2
2	SubTask1	Employee2
3	Task2	Employee1	4,5
4	SubTask2	Employee2
5	SubTask3	Employee2

I am looking to create an Excel macro to export the contents into MS Project.

I can only find sharing in the web about transferring the Task.

My code, credit to #madschedules, only includes Tasks.

Sub createNewMSPFromExcelData()

' First go turn on the MS Project reference library

' Declare variables
Dim pjApp As MSProject.Application
Dim pjProject As MSProject.Project
Dim pjtasklist As MSProject.Tasks
Dim pjtask As MSProject.Task
'Dim pjpredlist As MSProject.TaskDependencies
'Dim pjpredlist As TaskDependencies
Dim pjpred As TaskDependency
Dim xlrange As Range
Dim xlrow As Long
Dim counter As Integer

'open MS Project application
Set pjApp = New MSProject.Application
pjApp.Visible = True

' Add a new project file
Set pjProject = pjApp.Projects.Add
Set pjtasklist = pjProject.Tasks


'Loop through all the Excel Data in the worksheet
counter = 2
Do Until Cells(counter, 1) = ""
    Debug.Print Cells(counter, 1).Value & "   " & Cells(counter, 2).Value & "   " & Cells(counter, 3).Value

    ' Add new task into MS Project from Excel Value
    pjtasklist.Add (Cells(counter, 2).Value)
    pjpred.Add (Cells(counter, 3).Value)

    counter = counter + 1
Loop

MsgBox ("There are " & pjtasklist.Count & vbNewLine & _
       " tasks in our new project")

End Sub

Expected data in MS Project

To answer about the predecessors, I am currently doing a project with electronic schematic design which deals with multi hierarchies. Hence to work on the hierarchy, I need to ensure the sub-hierarchy activities are done before I can proceed with the next level, hence I have the predecessors details derived from the schematic into Excel.

Answer 1

The key to adding tasks and then setting task properties is to get a reference to the task object after it's added. So instead of:

pjtasklist.Add (Cells(counter, 2).Value)

do

Set pjtask = pjtasklist.Add (Cells(counter, 2).Value)
pjtask.ResourceNames = Cells(counter, 3).Value

Before going any further, however, clarity is needed regarding tasks, subtasks, and predecessors. In scheduling, a subtask is considered to be a task under a summary task. A summary task is used to group a related subset of the tasks for the project. The summary task's start and finish dates are calculated based on the earliest start and latest finish of its subtasks.

Predecessors are tasks that must happen before another task can start. Predecessor tasks should almost always be entered first so that the schedule reads in chronological order. Therefore it is extremely rare to see a task with a predecessor ID value higher than its own. Instead, the Excel table would be better like this where OL = OutlineLevel:

ID	Task Name	Resource Names	Predecessors	OL
1	Task1	Employee1		1
2	SubTask1	Employee2		2
3	Task2	Employee1		1
4	SubTask2	Employee2		2
5	SubTask3	Employee2	4	2

This yields the typical schedule structure like this:

And finally, here is the code to turn the table above into the schedule:

Sub createNewMSPFromExcelData()

Dim pjApp As MSProject.Application
Dim pjProject As MSProject.Project
Dim pjtasklist As MSProject.Tasks
Dim pjtask As MSProject.Task
Dim counter As Integer

Set pjApp = New MSProject.Application
pjApp.Visible = True

Set pjProject = pjApp.Projects.Add
Set pjtasklist = pjProject.Tasks

counter = 2
Do Until Cells(counter, 1) = ""

    Set pjtask = pjtasklist.Add(Cells(counter, 2).Value)
    pjtask.ResourceNames = Cells(counter, 3).Value
    pjtask.Predecessors = Cells(counter, 4).Value
    pjtask.OutlineLevel = Cells(counter, 5).Value

    counter = counter + 1
Loop

MsgBox ("There are " & pjtasklist.Count & vbNewLine & _
       " tasks in our new project")

End Sub