Relative v. Absolute Addressing using selectrow function

[Ray Chiu (a1ahardware.florida at gmail)]

Background
========
* 2003 MS-Projevct
* Attempting to select, copy and paste tasks programmtically (VBA)
* SelectRow Row:=8, RowRelative:=False

Observations
=========
I have recorded macros to provide VBA examples of copy and paste. Cutting
and pasting (via VBA) seems to work well. Selecting tasks (programmatically)
for copy and paste has been another story.

When using: SelectRow Row:=8, RowRelative:=False
Instead of selecting the eighth task, it selects, the eighth displayed task.
This seems to be true whether RowRelative is true or false

Syntax
=====
The syntax for this function is:
expression .SelectRow(Row, RowRelative, Height, Extend, Add)
expression Optional. An expression that returns an Application object.


Row Optional Long. The number of the row to select. The default is the
active row.

RowRelative Optional Boolean. True if the location of the new selection
is relative to the active selection. The default value is True.

Height Optional Long. The number of rows to select in addition to the
active cell.

Extend Optional Boolean. True if the active selection is extended into
the new selection. The default value is False.

Add Optional Boolean. True if the new selection is added to the active
selection. The default value is False.


Questions
=======
1) Is there a method to select a task based on its UniqueID or its task
number (not based on its displayed ordinal position)?

2) Once a task is selected is there a simple method to APPEND the selected
task to the end of the task list? Maybe there is a simple method to get the
position of the last task?

3) Not sure that I really understand the Extend and Add arguments: are they
something that can be used to solve my address selection problem?

Constructive guidance is greatly appreciated.

 

[Jack Dahlgren]

Selecting cells can be tricky and is often unnecessary in Project VBA. Most
people prefer to work with the tasks collection.

Copy and Pasting tasks (especially those with resource actual work) is the
kiss of death. Avoid it if at all possible.

What is it you are trying to do? It sounds as if you are coming to project
from excel and expecting it to work in the same way. It doesn't.

For some basics about working with tasks in project take a look at the
articles here:

http://zo-d.com/blog/archives/programming.html

Post back with the problem you are trying to solve and it will be easier to
make a suggestion about how to go about it.

-Jack Dahlgren

 

[Ray Chiu (a1ahardware.florida at gmail)]

GOAL
====
I have to change the hierachy of the schedule from Quareter=>CDRL
CDRL=>Quarter in programmatic way. After I posted my initial message, I
immediately read your Blog. Excel is not involved with this exercise.

Questions
=======
1) Is there a smarter way (i.e. not copy and paste) to realize the goal?
2) If not, what are the symptoms associated with copy and paste?
3) I was able to copy and paste using the VBA command (EditCopy, EditPaste),
however is there technique to leverage Task Collection methods to replace:

SelectRow Row:=25, RowRelative:=False

At this point, if I could somehow select the task via the Task Collection
methods, I think I would be able to reach the goal.

Thank you for your constructive comments.

 

[Jack Dahlgren]

The easiest and best way to change the heirarchy is to insert two columns.
One of the columns holds the quarter for each task (you can derive this with
a custom formula if you like) the other holds the different CDRL (what ever
that is) values. Set the appropriate values for each task. Use CTRL+D to fill
down or cut and paste if you have repetitive values.

Now go to the Project menu, select "group by" / "more groups" / "New" and
create a group based on the quarter field. Do the same and group by CDRL. You
can even create one that groups by Q then CDRL and one which groups by CDRL
and then by Q.

Now pick the grouping you like from the group by menu.

You can assign the different groups to different views if you like.

No code necessary. Anyone can access the group at any time. No macro
security to worry about.

Moving tasks programmatically, especially switching tasks between being
regular tasks and summary tasks is not going to be a good solution.

Some of the issues with copy and paste are that it creates duplicate
assignments. If you are using Project server and you have resource updates,
their actual work will be duplicated each time you paste and your reporting
database will soon become useless.

Try out grouping. It is designed to do what you want.

-Jack Dahlgren

 

[Ray Chiu (a1ahardware.florida at gmail)]

Thank you for the excellent suggestion. I was able to perform the procedure
below, but it did not roll up the percent complete to the CDRL (Contract Data
Requirement Line) level. I should have stated that the goal was to roll-up
the percent complete of tasks to the CDRL Level (CDRL needed to be at the top
level of indenture).

That being said, I discovered how to add a blank task to the task
collection. Is there a way to copy from an existing task into the new blank
task.

Sub TaskCollection()
Dim ts As Tasks
Dim t As Task

Set ts = ActiveProject.Tasks
Set t = ts(14)
MsgBox (t.Name)
Set ts(97) = t

It's the last line of code that is giving me problems. I am trying to
assign the data in t to element 97. The error message is "Invalid use of
property".

Given that there are no resources attached to a task, this will not cause
problems (I hope).

 

[Jack Dahlgren]

Why can't you get the values to roll up in grouping? They roll up for me.
What specifically are you doing where they do not roll up?

As for your code:

Collections don't work like that so Set ts(97) = t is not going to work.

You can copy over the task properties though:

with ts(97)
..name = t.name
..duration = t.duration
'etc
end with

hard coding the numbers is going to be a problem though. Always avoid hard
coding task numbers unless it is one of the unchangable ones (task 0 is the
only example of this)

-Jack

 

[Ray Chiu (a1ahardware.florida at gmail)]

Not sure why 'physical percent complete' will not roll up, however it turns
out that they are using 'percent complete', which does roll up in the
CDRL-Quarter grouped view. Preliminary tests indicate this could achieve the
goal.

Is there an elegant way to implement the following code to transfer all the
tasks properties without listing each property explicitly (below)? Maybe
this question belongs in a VB forum and for a VB ninja.

with ts(97)
..name = t.name
..duration = t.duration
'etc
end with

Your guidance and comments are appreciated.