VBA - Add task to MS project from Excel

Question

I am currently opening a project in MS-Project from Excel sheet using VBA. I am also adding tasks, dates, duration and some more data.

My problem is that when I am adding a new task the default start time of the task is not as defined in the project (7:00 am) but 00:00. Here is the code i am using:

'Collect data
strValue = Worksheets("Display").Range("B" & i)
strStartDate = Worksheets("Display").Range("G" & i)
strEndDate = Worksheets("Display").Range("G" & i)
Strresource = Worksheets("Display").Range("C" & i)
DurTim = CDec(Worksheets("Display").Range("E" & i))
ActDurTim = CDec(Worksheets("Display").Range("F" & i))

'Define project defaults
newproj.DefaultStartTime = "7:00"
newproj.DefaultFinishTime = "16:00"
newproj.HoursPerDay = "7"

'Enter data to task
newproj.Tasks.Add (strValue)
'Check for milestone
If Worksheets("Display").Range("H" & i) = "Y" Then
    newproj.Tasks(i - 6).Milestone = False
End If
newproj.Tasks(i - 6).Start = strStartDate
newproj.Tasks(i - 6).Duration = DurTim & " hours"
newproj.Tasks(i - 6).ActualDuration = ActDurTim & "hours"
If i <> 7 Then
    newproj.Tasks(i - 6).Predecessors = newproj.Tasks(i - 6 - 1)
End If
If Not ExistsInCollection(newproj.Resources, Strresource) Then _
newproj.Resources.Add.Name = Strresource
newproj.Tasks(i - 6).ResourceNames = Strresource

I have tried to look in the Ms-Project objects of the tasks and the project itself for a solution but no luck. Any help would be grateful.

Rachel Hettinger Rachel Hettinger · Accepted Answer · 2015-01-26T23:40:49

When you set a task's Start property through code you are actually setting a Constraint Date and Constraint Type in order to drive the calculated Start field. And the date that is supplied has a time component, either explicitly (e.g. 1/26/2015 3:25 PM) or implicitly (e.g. 1/26/2015 12:00 AM).

On the other hand, if you type in a Start date in the user interface and you don't supply a time component, the Default Start Time is added to the date to create the Constraint Date.

What you actually see in a task's Start field depends on these factors that Microsoft Project uses to calculate the date: constraints, predecessor/successor tasks, and calendars (project, task, and resource).

From what you are reporting it appears that your project calendar is set to 7d24h or a similar calendar with working time starting at midnight. So when you set the Start date through code, a constraint date is added with a midnight start time and since the project calendar has that as a working time, the task starts at midnight.

If you want to emulate the user interface method of setting a Start date, use the SetField method:

newproj.Tasks(i - 6).SetField pjTaskStart, strStartDate

VBA - Add task to MS project from Excel

1 Answers