VBA vs. "Other projects and commitments".

R

Robert

Hello,
In order to test Resource custom fields, I tried to write a VBA macro that
would transfer data into Resource[Text2] field:
But it doesn't work when a resource is assigned to another project : "Other
projects and commitments".
What would be an efficient test to avoid the error on "Other projects and
commitments"?
Thanks for your help.

What I have tried (without success):

Sub ResourceText2Field()
Dim oRes As Resource
Dim Ass As Assignment
Dim i As Integer, Vide As Boolean
'On Error Resume Next
For Each oRes In ActiveProject.Resources
i = 0
For Each Ass In oRes.Assignments
i = i + 1
'First lap...
If i = 1 Then
'If we are on an assignment row: "Autres projets et
engagements": Ass.TaskName = "Autres projets et engagements"
If oRes.Assignments(i).TaskID = -1 Then
Vide = True
GoTo skip
End If
oRes.Text2 = oRes.Assignments(i).TaskID
Else
'Attention: Assignment.Text2 is that one which appears in
Task Usage
If Vide = True Then
oRes.Text2 = oRes.Assignments(i).TaskID
Else
oRes.Text2 = oRes.Text2 & " ; " &
oRes.Assignments(i).TaskID
End If
skip:
End If
Next Ass
Next oRes
End Sub
 
R

Robert

Sorry, I failed to specify I'm working on Project Server 2007 SP2 + August
CU
Happy New Year
 
J

Jack Dahlgren MVP

Dim r As Resource
Dim a As Assignment

For Each r In ActiveProject.Resources
For Each a In r.Assignments
if not a.Project = "whatever" then

I think you can leave out the part about i and i+1

the for each will take care of stepping through the assignments

-Jack
 
R

Robert

Thanks a lot Jack,

I changed the test : If Ass.Project = "" to avoid the Assignment row with
"Other projects and commitments". It works fine now.
But after that the row: oRes.Text2 = CStr(Ass.TaskID) does not work any
more, while it is very simpe. It generates an error:
'Run time error 1101 : Argument value not valid.
I don't know why!!!
NB : I use the i + 1 to add or not a separator before the data I let in the
Text2 field
Thanks for your help

Sub ResourceText2FieldBis()
Dim oRes As Resource
Dim Ass As Assignment
Dim i As Integer

For Each oRes In ActiveProject.Resources
i = 0
For Each Ass In oRes.Assignments
i = i + 1
If Ass.Project = "" Then i = i - 1 'Si l'on est sur une
ligne d'affectation "Autres projets et engagements"
'Assignment row : "Other
projects and commitments"
If Not Ass.Project = "" Then ' If we are not on an
Assignment row : "Other projects and commitments" - "Autres projets et
engagements"
If i = 1 Then 'On first loop : no separator
oRes.Text2 = CStr(Ass.TaskID) 'Run time error
1101 : Argument value not valid
'CStr is
useless...
Else
oRes.Text2 = oRes.Text2 & " ; " &
oRes.Assignments(i).TaskID
End If
End If
Next Ass
Next oRes
End Sub

Jack Dahlgren MVP said:
Dim r As Resource
Dim a As Assignment

For Each r In ActiveProject.Resources
For Each a In r.Assignments
if not a.Project = "whatever" then

I think you can leave out the part about i and i+1

the for each will take care of stepping through the assignments

-Jack

-----------------------------------
Robert said:
Hello,
In order to test Resource custom fields, I tried to write a VBA macro
that would transfer data into Resource[Text2] field:
But it doesn't work when a resource is assigned to another project :
"Other projects and commitments".
What would be an efficient test to avoid the error on "Other projects and
commitments"?
Thanks for your help.

What I have tried (without success):

Sub ResourceText2Field()
Dim oRes As Resource
Dim Ass As Assignment
Dim i As Integer, Vide As Boolean
'On Error Resume Next
For Each oRes In ActiveProject.Resources
i = 0
For Each Ass In oRes.Assignments
i = i + 1
'First lap...
If i = 1 Then
'If we are on an assignment row: "Autres projets et
engagements": Ass.TaskName = "Autres projets et engagements"
If oRes.Assignments(i).TaskID = -1 Then
Vide = True
GoTo skip
End If
oRes.Text2 = oRes.Assignments(i).TaskID
Else
'Attention: Assignment.Text2 is that one which appears in
Task Usage
If Vide = True Then
oRes.Text2 = oRes.Assignments(i).TaskID
Else
oRes.Text2 = oRes.Text2 & " ; " &
oRes.Assignments(i).TaskID
End If
skip:
End If
Next Ass
Next oRes
End Sub
Click to expand...
Click to expand...
 
J

Jack Dahlgren MVP

I think that the assignment does not have a taskID. Maybe
assignment.task.taskID would work? I'm not at a machine with project so I'm
just guessing here.

-Jack



Robert said:
Thanks a lot Jack,

I changed the test : If Ass.Project = "" to avoid the Assignment row with
"Other projects and commitments". It works fine now.
But after that the row: oRes.Text2 = CStr(Ass.TaskID) does not work any
more, while it is very simpe. It generates an error:
'Run time error 1101 : Argument value not valid.
I don't know why!!!
NB : I use the i + 1 to add or not a separator before the data I let in
the Text2 field
Thanks for your help

Sub ResourceText2FieldBis()
Dim oRes As Resource
Dim Ass As Assignment
Dim i As Integer

For Each oRes In ActiveProject.Resources
i = 0
For Each Ass In oRes.Assignments
i = i + 1
If Ass.Project = "" Then i = i - 1 'Si l'on est sur une
ligne d'affectation "Autres projets et engagements"
'Assignment row : "Other
projects and commitments"
If Not Ass.Project = "" Then ' If we are not on an
Assignment row : "Other projects and commitments" - "Autres projets et
engagements"
If i = 1 Then 'On first loop : no separator
oRes.Text2 = CStr(Ass.TaskID) 'Run time error
1101 : Argument value not valid
'CStr is
useless...
Else
oRes.Text2 = oRes.Text2 & " ; " &
oRes.Assignments(i).TaskID
End If
End If
Next Ass
Next oRes
End Sub

Jack Dahlgren MVP said:
Dim r As Resource
Dim a As Assignment

For Each r In ActiveProject.Resources
For Each a In r.Assignments
if not a.Project = "whatever" then

I think you can leave out the part about i and i+1

the for each will take care of stepping through the assignments

-Jack

-----------------------------------
Robert said:
Hello,
In order to test Resource custom fields, I tried to write a VBA macro
that would transfer data into Resource[Text2] field:
But it doesn't work when a resource is assigned to another project :
"Other projects and commitments".
What would be an efficient test to avoid the error on "Other projects
and commitments"?
Thanks for your help.

What I have tried (without success):

Sub ResourceText2Field()
Dim oRes As Resource
Dim Ass As Assignment
Dim i As Integer, Vide As Boolean
'On Error Resume Next
For Each oRes In ActiveProject.Resources
i = 0
For Each Ass In oRes.Assignments
i = i + 1
'First lap...
If i = 1 Then
'If we are on an assignment row: "Autres projets et
engagements": Ass.TaskName = "Autres projets et engagements"
If oRes.Assignments(i).TaskID = -1 Then
Vide = True
GoTo skip
End If
oRes.Text2 = oRes.Assignments(i).TaskID
Else
'Attention: Assignment.Text2 is that one which appears in
Task Usage
If Vide = True Then
oRes.Text2 = oRes.Assignments(i).TaskID
Else
oRes.Text2 = oRes.Text2 & " ; " &
oRes.Assignments(i).TaskID
End If
skip:
End If
Next Ass
Next oRes
End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Other Projects and Commitments Feature. 1
Other projects and commitments at resource usage view 0
Other projects and commitments 11
Other projects and commitments 3
Other Projects and Commitments 1
"Other projects and commitments" in Resource Usage view 5
Problem running Project Macro VBA 4
method GetField and custom field created in EPM 5

Top