How to replace single tasks with multiple resource assignments with multiple tasks with single assig

[ERIM]

Hi

I'm currently using MSP2003 for simple resource management demand v
supply purposes and have a number of processes for extracting data for
use in excel macros.

However, all my processes require projects to be made up of tasks with
a single resource assignment.

Recently I was sent a resource profile for a new project in the form
of a .MPP project file, which I was able to import to MSP2003 without
problems. The trouble is, the project in question is made up of a
number of sub-sections, and all the tasks have multiple resource
allocations.

Never mind, I thought, I'll knock up a macro in VBA (MSP) to run
through the tasks one by one, extract each resource assignment on each
task into an array, then add as many new tasks as there are entries in
the array with the same task information, but with each of the
resource allocations.

I have been successful in extracting the resource information from
each of the tasks into an array, but when I try writing new tasks,
things go wrong!

Here is my macro. I have inserted a comment highlighting where the
processing goes wrong ...


Sub Extract_multiple_row_assignments()
'
Dim Temp As Long, A As Assignment, n As Long, x As Long, z As
Long, w As Long
Dim TaskName As String, Assigned As String, Results As String
Dim res(30), unt(30), st, fn, dur, v As Long
w = 999999
'
' loop round all tasks from bottom to top
'
For Temp = ActiveProject.Tasks.Count To 1 Step -1
nxt = Temp - 1
If w = 999999 Then
w = nxt
End If
'
' Extract task data
'
TaskName = ActiveProject.Tasks(Temp).Name
st = ActiveProject.Tasks(Temp).start
fn = ActiveProject.Tasks(Temp).finish
dur = ActiveProject.Tasks(Temp).Duration
id = ActiveProject.Tasks(Temp).id

n = 0
'
' copy each resource assignment on the task into an array
'
For Each A In ActiveProject.Tasks(Temp).Assignments
n = n + 1
res(n) = A.ResourceName
unt(n) = A.Units
Next A
'
' if more than one resource allocation has been identified,
add new task
'
If n > 0 Then
For x = 1 To n
z = 0
v = unt(n)
Do While unt(n) > 0
ActiveProject.Tasks.Add TaskName, id + 1
'
' update new task with stored information
'
'
' ****** this is where it all goes wrong: I'm trying to select
the new row added above
' ****** to add the stored information, but the macro doesn't
select the row I'm expecting!
' ****** I've tried various methods, but with no success.
'
SelectRow (w - nxt)
z = z + 1
SetTaskField Field:="Start", Value:=st
SetTaskField Field:="Finish", Value:=fn
SetTaskField Field:="Resource Names",
Value:=res(x)
w = w + 1
unt(n) = unt(n) - 1
Loop
w = w - v
Next x
End If
TaskName = ""
Next Temp
End Sub


.... can anyone out there help me out - please!

regards

ERIM

 

[Mike Glen]

Hi ERIM,

Try posting on the developer newsgroup as this one is closing down. Please
see FAQ Item: 24. Project Newsgroups. FAQs, companion products and other
useful Project information can be seen at this web
address:http://project.mvps.org/faqs.htm .

Mike Glen
Project MVP