Rod Gill said:
The only safe way to copy actual data from Excel is if the Excel data has the
Task's and Resource's Unique ID. If it doesn't, then you can't guarantee to
get the correct task/resource combo (assignment), especially if some edits a
task name during the week, inserts and deletes tasks or more. There are a
lot of scenarios to handle, EG a new resource doing work not previously
scheduled on a new task for the project.
I agree that there are many scenarios to handle and many posibilities for
errors. For me it was worth the programming effort to not enter data manually
that was already collected in our company timesheet program (Track-It).
The method I used was to first set the project status date then search for a
match on WBS codes (Outline Numbers) and record in a file if no match was
found (& msgbox the user). Extract of macro shown below. Once a WBS was found
(ie task) then check if the resource name matched any names already assigned.
If not add the resource using 0% utilisation to not affect costs. For adding
resources the correct resource ID is found by looping through the resource
file until the name matches.
Once task is found and resource found or added, the actual costs for that
period can be inserted using TimeScaleData.
'***********************************************************
' Search the Project file for this WBS
'***********************************************************
WBS_Found = False
Name_Found = False
For Each My_Task In ActiveProject.Tasks
If Not (My_Task Is Nothing) Then
If My_Task.OutlineNumber = Res_WBS Then 'correct WBS found
WBS_Found = True
For Each My_Assn In My_Task.Assignments
If Not (My_Assn Is Nothing) Then
If My_Assn.ResourceName = Res_Name Then ' correct name
found
Name_Found = True
Exit For
End If
End If
Next My_Assn
Exit For
End If
End If
Next My_Task
