Resource Usage View Zeroing Work in a Date Range Macro

[Darrell]

In the Resource Usage view I recorded a macro where I simply changed a single
value from 8hrs to 0 hrs for a selected resource and selected assignment. I
then modified this macro to change all values in a date range to 0hrs for the
same assignment. And, for that one assignment it worked great – it zeroed all
values in the date range I entered. However, I could not make this same macro
work on another assignment when I changed the UID field to a different
assignment. Here is what I started with and what I modified it too. I do not
understand why I cannot change the UID field and rerun the macro on a
different assignment. I need someone who knows more than me, well that
probably includes everyone in this news group, to help me understand what I
am missing so that I can do a one-time fix on a schedule I inherited. Any and
all input will be greatly appreciated.

Original Macro –
Sub Test_2()
' Macro Post
' Macro Recorded Tue 10/28/08 by MCIGOV\dmarkham.

ActiveProject.Resources.UniqueID(453).Assignments.UniqueID(2097263).TimeScaleData(StartDate:="7/2/07
12:00 AM", EndDate:="7/3/07 12:00 AM", Type:=8, TimeScaleUnit:=4,
Count:=1).Item(1).Value = "0"
SelectTimescaleRange Row:=140, StartTime:="Tue 7/3/07", Width:=1,
Height:=1
End Sub

Changed to -
Sub Test2()
' Macro Test2
' Macro Recorded Tue 10/28/08 by MCIGOV\dmarkham.
Dim tsv As TimeScaleValue
Dim tsvs As TimeScaleValues
Set tsvs =
ActiveProject.Resources.UniqueID(453).Assignments.UniqueID(2097263).TimeScaleData(StartDate:="7/2/07
12:00 AM", EndDate:="9/6/08 12:00 AM", Type:=8, TimeScaleUnit:=4, Count:=1)
For Each tsv In tsvs
tsv.Value = "0"
Next tsv
End Sub

Like I said this worked great for the assignment I originally recorded the
macro for… I need it to work for any assignment I select by whatever method
is best so that I can remove some bad data and re-enter it work the correct
values in an Administrative schedule that has 3 years of information the
company needs to report against.

Thx for any help.
Darrell

 

[Rod Gill]

Hi,

I would do it something like:

Dim Res as Resource
Dim Assgn as Assignment
for each Res in ActiveProject.Resources
if Assgn.StartDate <= endofdaterange AND Assgn.Finish >=
startofdaterange Then
Set tsvs=
for each tsv in tsva

etc.



--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

 

[Darrell]

Rod,

Thanks for the answer, your book has been a great help as well.

If I do it the way you suggest would it still allow me to select the
specific resource? The problem I have is that I need to zero values for a
specific date range for a specific set of resources and not all resources.
That is why I thought I could use the Resource and Assignment UIDs to
designate the values I wanted to delete. I know I would have to change the
UIDs for each set of values I want to zero out but that is much quicker than
trying to manually do it line by line.

Could I use a text field and enter the resource names I want to zero values
for and then have it only use that list? If so how would that look?

Thx again,
Darrell

 

[Darrell]

Another thought.... could I use a flag field to identify the resources I want
to zero values for and then use an If statement to select those assignments?

Darrell

 

[Jack Dahlgren MVP]

Certainly.
You can test for anything with an if statement

if Res.flag1 = True then
'do stuff
end if

 

[Darrell]

Thanks Jack,

Now if I can just put it all together and make it work I will be free of
this task of fixing/cleaning up someone else's mess and can move on to bigger
and better deliverables.

Darrell

 

[Darrell]

So am I even getting close with this? It doesn't work... actually it doesn't
do anything.. not even generating an error which is frustrating. Am I missing
something that should select the first cell in the data grid?

Sub Test1()
Dim Res As Resource
Dim tsv As TimeScaleValue
Dim tsvs As TimeScaleValues
For Each Res In ActiveProject.Resources
If Res.flag1 = True then
Set tsvs =
ActiveProject.Resources.Assignments.TimeScaleData(StartDate:=â€7/2/07 12:00
AMâ€, EndDate:=â€9/6/08 12:00 AMâ€, Type:=8, TimeScaleUnit:=4, Count:=1)
For Each tsv In tsvs
If Tsv.Value > “0†then
Tsv.Value = “0â€
End if
Next tsv
end if
Next Res
End Sub

 

[Jack Dahlgren MVP]

Good luck!

-Jack

Thanks Jack,

Now if I can just put it all together and make it work I will be free of
this task of fixing/cleaning up someone else's mess and can move on to
bigger
and better deliverables.

Darrell

 

[Jack Dahlgren MVP]

The problem is when you are setting the tsvs.
You should be doing something like

Sub Test1()

Dim Res As Resource
Dim tsv As TimeScaleValue
Dim tsvs As TimeScaleValues Dim asgt as assignment
For Each Res In ActiveProject.Resources
If Res.flag1 = True then

for each asgt in res.assignments
set tsvs = asgt.timescaledata ...
'do the other things
next asgt

 

[Darrell]

Well at least now I get an error

Sub Test1()
Dim R As Resource
Dim A As Assignment
Dim tsv As TimeScaleValue
Dim tsvs As TimeScaleValues
For Each R In ActiveProject.Resources
If A.flag1 = True then
Set tsvs = R.TimeScaleData(StartDate:=â€7/2/07 12:00 AMâ€, EndDate:=â€9/6/08
12:00 AMâ€, Type:=8, TimeScaleUnit:=4, Count:=1)
For Each tsv In tsvs
If Tsv.Value > “0†then
Tsv.Value = “0â€
End if
Next tsv
end if
Next R
End Sub

If anyone can help me out here to get this to work (without spending hours)
I would really be greatful. I cannot and do not know how.

Darrell

 

[Darrell]

Thanks again Jack,

I think I am out of my depth here and will have to take some classes to be
able to understand everything I know. As for the code I think I will change
the part about tsv.Value = "0" to a counter and then set the counter.Value =
"0".

Again your help is much appreaciated,
Darrell

 

[Rod Gill]

Yes, flagging the resources you want to zero is the way to go. You can use
tsv.clear to remove a value completely. Setting it to zero (unless it is the
last time slice) just sets the work for that time to zero, it does not
necessarily delete assignment time slice.

Another way is to delete the assignment then re-create it.

Glad the book's useful, happy coding!

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

 

[Darrell]

Rod,

Thx for the much appreaciated help. The problem I face is an old
Administrative schedule that was migrated from 2003 into 2007 and then
continued to be used for about 9 months, before I arrived and convenced them
to stop using the schedule and to use the administrative categories in the
timesheets.

However, this schedule still has some valuable data they want to report
against so I am trying to clean out the bad data resulting from the migration
and poor maintenance after the migration so that I can re-enter that data
correctly and use it for reporting purposses.

This is why I am trying to delete values in a date range on an assignment
that spans several years. I want to delete about 6 months for specific
resources and then re-enter it from a different data source. Anyways, I can
work around it and do the reporting but it would make the process much easier
if I could get this macro to work.

I have other fish to fry as well so I will work on it later and perhaps post
again when I have made more progress.

Thx again for your help,
Darrell

 

[Darrell]

Oh, and congratulations on the MPUF narrator gig!!