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 · Accepted Answer · 2017-06-22 17:35:42Z

1

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).

edited Jun 22, 2017 at 17:35

answered Jun 21, 2017 at 17:27

Rachel Hettinger

8,4872 gold badges25 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Peel Over a year ago

To make this whole process easier, the dates from excel are already in their own columns under "Actual Start" and "Actual Finish." Many of the actual start and end dates are "NA" will this make the DateDifference not work? If it will work would I be able to keep this same For To function and change the date variables to equal the cells in MS Project? for example: date1 = .Cells(i, "Actual Start")

Peel Over a year ago

Also where would the result be printed? Do I need to Make a column before hand?

Rachel Hettinger Over a year ago

Are you actually needing to update the Actual Start and Actual Finish dates rather than the duration? You can store the TotalMinutes or WorkingMinutes variables in Excel if you want; I'll update the code for that.

Peel Over a year ago

There is currently not an Actual Duration in minutes column and the Actual Duration Column in MS Project doesn't put out the right output. So originally I exported the data to excel and did the DateDiff calculation there then tried to output that back into MS Project using the ID because copy and pasting the column won't attach the Durations to the correct ID number.

Rachel Hettinger Over a year ago

If you are trying to status your schedule with actuals data from Excel, set the Actual Start, Actual Finish, and/or Percent Complete fields. It is uncommon to directly set the Actual Duration column.

|

Collectives™ on Stack Overflow

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

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related