Automatically adding subtasks to Microsoft Project from Excel file

Question

So I have an Excel file where each row is a new task in Microsoft Project. The Excel file is refreshed each week with new rows. I used the Project import wizard to map the fields so when I merge the Excel file into Project the mapped fields are updated and new tasks are added when there is a new row in the Excel file.

However, now I need to automatically add three subtasks to each main task in Project after importing the Excel file without replacing the subtasks of the tasks that were already in the file. In other words, I need to automatically add the three subtasks whenever there is a new task in the Project file. Each task in Project have the same three subtasks.

Is there a way I can do this in VBA?

The image below shows how the subtasks should show under each task after you run the script. Tasks 2 to 11 should show those exact three subtasks.

https://i.stack.imgur.com/fOMrh.jpg

Sub ModifyName()

    Dim tsk As Task
    
    For Each tsk In ActiveProject.Tasks
        If Not tsk Is Nothing Then
            tsk.Name = tsk.Text2 + "-" + tsk.Name
        End If
        
    Next tsk
    
End Sub

Sub InsertSubTask()

    For Each tsk In ActiveProject.Tasks
        If tsk.Flag1 And tsk.OutlineChildren.Count = 0 Then
            With ActiveProject
                .Tasks.Add tsk.Name + " " + "name1", tsk.ID + 1
                .Tasks.Add tsk.Name + " " + "name2", tsk.ID + 2
                .Tasks.Add tsk.Name + " " + "name3", tsk.ID + 3
                
                .Tasks(tsk.ID + 1).OutlineIndent
                .Tasks(tsk.ID + 2).OutlineIndent
                .Tasks(tsk.ID + 3).OutlineIndent
                
                .Tasks(tsk.ID + 1).Start = tsk.Date1
                .Tasks(tsk.ID + 2).Start = tsk.Date2
                .Tasks(tsk.ID + 3).Start = tsk.Date3
                
                .Tasks(tsk.ID + 1).Number1 = tsk.Number1
                .Tasks(tsk.ID + 2).Number1 = tsk.Number1
                .Tasks(tsk.ID + 3).Number1 = tsk.Number1
                
                
            End With
        End If
    Next tsk
    

    
End Sub

Welcome to stackoverflow :) I think you may have misunderstood how stackoverflow operates. You may want to see [How do I ask a good question?](https://stackoverflow.com/help/how-to-ask) Please post what you have tried so far. Keep these in mind while posting **1.** What were you expecting? **2.** What is not working? Include Error message if applicable and we will take it from there :) — Siddharth Rout, Apr 11 '21 at 04:29

Rachel Hettinger · Accepted Answer · 2021-04-13T02:10:47.017

0

Here's a basic macro that adds subtasks to specific tasks if they don't already have subtasks. For this example, the Flag1 field is used to identify the tasks that should have subtasks. That logic can be easily changed to look at task name, a text field, etc.

Update: The last block of code sets the Start date for each new task based on dates saved in the flagged tasks' numbered Date fields. (Note that setting the Start date sets a Constraint Type = "Start No Earlier Than" with the Constraint Date equal to what was set as the Start date.)

Sub InsertSubTasks()

    Dim tsk As Task
    For Each tsk In ActiveProject.Tasks
        If tsk.Flag1 And tsk.OutlineChildren.Count = 0 Then
            With ActiveProject
                .Tasks.Add "Subtask 1", tsk.ID + 1
                .Tasks.Add "Subtask 2", tsk.ID + 2
                .Tasks.Add "Subtask 3", tsk.ID + 3
                
                .Tasks(tsk.ID + 1).OutlineIndent
                .Tasks(tsk.ID + 2).OutlineIndent
                .Tasks(tsk.ID + 3).OutlineIndent

                .Tasks(tsk.ID + 1).Start = tsk.Date1
                .Tasks(tsk.ID + 2).Start = tsk.Date2
                .Tasks(tsk.ID + 3).Start = tsk.Date3

            End With
        End If
    Next tsk
    
End Sub

edited Apr 13 '21 at 02:10

answered Apr 11 '21 at 03:34

Rachel Hettinger

7,927
2
21
31

Thank you Rachel! I will try playing around with this macro later today and see if it works. – hockeyhorror Apr 11 '21 at 13:58
Rachel, I do have one follow-up question. So this script works. But for each milestone I have a date field from the Excel file that needs to populate the START date column in Project for that milestone. Each parent task has a unique ID - Task Number. There are 3 date fields - Date 1, Date 2, and Date 3. So for example, in the Start Date column for Parent Task 1 I need to populate Subtask 1 with Date 1; Subtask 2 with Date 2; and Subtask 3 with Date 3 from the Excel file. When I use the import wizard I link a 4th date field to the Start Date for the parent task. Do you know how I might do this? – hockeyhorror Apr 13 '21 at 00:39
Yes, see updated code for additional 3 lines to do this. Note that it is pointless to set dates on tasks that will become summary tasks as the Start and Finish on summary tasks are calculated based on their subtasks. – Rachel Hettinger Apr 13 '21 at 02:13
Hi Rachel. Can you please look at the code I just added to the question? Now what I need to do, before or after adding the subtasks, is modify the parent task name to be the combination of two fields from the file, text1 and text2. But when I run that code the second sub overwrites the first and I only see the subtasks. Do you know why? – hockeyhorror Apr 22 '21 at 00:24

Automatically adding subtasks to Microsoft Project from Excel file

1 Answers1