Using Tasks to Add Excel Data with Same ID to MS Project - VBA

Question

In excel I have the ID' numbers in column "A" starting on the second row (first row=header). In column "T" I have a duration for that ID on the same row.

In MS Project I have an ID column and an empty duration column. I want to add the duration from excel to MS Project on the correct ID row.

I think I can do this by using tasks and a For..To loop. I will need to use the ID in excel to look up the task in MS Project then write the duration from excel in the appropriate task in MS Project. So far, with some help, the code I have is:

'Find duration and assign to ID in Excel
For i = 2 To lastRow
date1 = .Cells(i, 15)
date2 = .Cells(i, 16)
    If .Cells(i, 18).Value = "No" Then
    answer = DateDiff("n", date1, date2)
    .Cells(i, 20) = answer
    End If
durationID = .Cells(i,1).Value
Next i

'Open MS Project and add Duration column
set wb = ActiveWorkBook
Set ws = wb.Sheets("Task_Table1")
Set appProj = CreateObject("Msproject.Application")
appProj.FileOpen "File1.mpp"
Set aProg = appProj.ActiveProject
appProj.Visible = True


lastTask = ActiveProject.Tasks.Count
taskID = ActiveProject.Tasks.ID

'Load Durations into MS Project to appropriate ID task
lastTask = ActiveProject.Tasks.Count
For i = 1 to lastTask
    If taskID = Application.Workbooks("File1").Sheets("Task_Table1").Cells(i, 1).Value Then
    answer.Copy
    appProj.SelectCell.ActiveCell
    end if
Next i

Rachel Hettinger Rachel Hettinger · Accepted Answer · 2017-06-21T17:27:34

Instead of copying the duration into the user-interface, assign the duration directly to the task object. Alternatively (based on the comment below), update the Actual Start and Actual Finish date. Code to do both is included, but it doesn't make sense to update the duration if you are going to also update Actual Start and Actual Finish.

'Open MS Project
Set appProj = CreateObject("MSProject.Application")
appProj.FileOpen "File1.mpp"
Set aProg = appProj.ActiveProject
appProj.Visible = True

'Find duration and assign to task
Dim Duration As Long
Dim tsk as MSProject.Task
With ws
    For i = 2 To lastRow
        date1 = .Cells(i, 15)
        date2 = .Cells(i, 16)
        ' get a reference to the task object using the ID stored in Column A
        Set tsk = aProg.Tasks(.Cells(i, 1).Value)
        ' Update duration 
        If .Cells(i, 18).Value = "No" And IsDate(date1) And IsDate(date2) Then
            TotalMinutes = DateDiff("n", date1, date2)
            WorkingMinutes = appProj.DateDifference(date1, date2)
            .Cells(i, 20) = WorkingMinutes 
            tsk.Duration = WorkingMinutes
        End If
        ' update Actual Start and/or Actual Finish
        If IsDate(date1) Then
            tsk.ActualStart = date1
        End If
        If IsDate(date2) Then
            tsk.ActualFinish = date2
        End If
    Next i
End With

Note that there are two calculations included for the duration. The VBA DateDiff function returns the total number of minutes between two dates, whereas the MS Project DateDifference function returns the number of working minutes between two dates. The latter is likely what you want to use. Otherwise a 1 day duration (1440 total minutes) will turn into a 3 day task (1440 = 3 days * 8 hours * 60 minutes/hour).

Using Tasks to Add Excel Data with Same ID to MS Project - VBA

1 Answers