Macro to update MS Project Values using Data in Excel

[David Altemir]

I want to update Duration, Work, %Complete by running a macro within
the MS Project that opens an Excel file, reads the values in the
spreadsheet, and copies those values into MS Project.

For example, the Excel file will look like:

TaskName | Duration
-------------------------------
3267123 | 6
3673214 | 12
3679182 | 3
3670319 | 10

where the column headers are contained in Row 1.

Can someone clue me in as to how to write VBA code in MS Project that
parses through the spreadsheet and updates the tasks' parameters
accordingly? Any code examples somewhere?

 

[JackD]

I have a number of examples which write from project to excel. It won't be
hard to make them work in reverse.
The code you will need to right will use the Find function and then once the
task is found assign the duration

Here is an example which sets the task named "c" to 9999 minutes long

Sub taskCisLong()
Find Field:="Name", Test:="equals", Value:="c"
ActiveSelection.Tasks(1).Duration = 9999
End Sub

Remember, in project the units for duration are in minutes, so if those are
days in your list you need to multiply by 480 (60 minutes x 8 hours).

Examples are here: http://masamiki.com/project/macros.htm