VBA routine for Project to Edit Taskname field

[Ken Wright]

LOL - Friend asked me to help him with a routine iin Project, but then i saw
this very alien looking object model and figured I'd better leave the safety
of the Excel groups and come talk to you folks

2 Cols of data, Col 1 being Taskname, Col 2 being Duration

Would like a routine to start at first Taskname, look at Duration, and if
duration is 0.2 then Taskname gets the text ' (WBS Placeholder) tacked onto
it and becomes

Taskname (WBS Placeholder)

ie, just tack a space and '(WBS Placeholder)' onto the existing taskname.

Number of tasks random so looking for some kind of loop, or For Next etc,
and in each case, if the duration is 0.2 then

Taskname ==> Taskname (WBS Placeholder)

 

[JackD]

Here you go:

sub ken()
for each task in activeproject.tasks
if not task is nothing then
if task.duration = .2*480 then
task.name = task.name & " WBS Placeholder"
end if
end if
next task
end sub

The only tricky part is testing for blank rows (if not task is nothing). You
need to determine what the "0.2" really means. Project stores duration
internally in minutes. So a day is 480 minutes. If that is a .2 weeks then
duration would be 480.

 

[Ken Wright]

Cheers Jack, that does exactly what he wanted - Very much appreciated.

Have to say though, that code looks like normal VBA to me and NOTHING like
what the damn Macro recorder gave me when i tried <g>

Cheers
Ken...................

Here you go:

sub ken()
for each task in activeproject.tasks
if not task is nothing then
if task.duration = .2*480 then
task.name = task.name & " WBS Placeholder"
end if
end if
next task
end sub

The only tricky part is testing for blank rows (if not task is nothing). You
need to determine what the "0.2" really means. Project stores duration
internally in minutes. So a day is 480 minutes. If that is a .2 weeks then
duration would be 480.

 

[JackD]

Macro recorders are good for discovering syntax. They aren't much good for
procedural stuff.


--
-Jack ... For project information and macro examples visit
http://masamiki.com/project

..

Cheers Jack, that does exactly what he wanted - Very much appreciated.

Have to say though, that code looks like normal VBA to me and NOTHING like
what the damn Macro recorder gave me when i tried <g>

Cheers
Ken...................

 

[Ken Wright]

Hi Jack, my fault for playing but now I'm curious. Even if I have blank
tasks but have a 0.2 in the duration (Academic because I can't think why he
would I guess) it puts the text (WBS placeholder) in there. What am I
missing?

 

[JackD]

What do you mean you have blank tasks?
In the project world that means the line is completely blank.
You can't have a blank task with a duration because if it has a duration it
is not a blank task.
You could add another test to see if there is a nonblank task name

if not task.name = "" then
'do stuff
end if

--
-Jack ... For project information and macro examples visit
http://masamiki.com/project

..

Hi Jack, my fault for playing but now I'm curious. Even if I have blank
tasks but have a 0.2 in the duration (Academic because I can't think why he
would I guess) it puts the text (WBS placeholder) in there. What am I
missing?

 

[Ken Wright]

LOL - Apologies Jack - my bad. What I was trying to get to was what the
'testing for blank rows' did. I couldn't get any difference running with or
without the 'If task not nothing' statement.

If it was Excel I could either run through cell by cell, or use specialcells
just to hit the cells with values in, so i was trying to understand what that
line in the code was actually doing.

I very rarely have to use Project so may just not be envisaging the right
scenario for when that line would make a difference though.

Regards
Ken...............

What do you mean you have blank tasks?
In the project world that means the line is completely blank.
You can't have a blank task with a duration because if it has a duration it
is not a blank task.
You could add another test to see if there is a nonblank task name

if not task.name = "" then
'do stuff
end if

 

[Jan De Messemaeker]

Hi,

A completely blank line gives a task which is a Nothing object
Since that has no properties it would give a Run Time Error.
HTH

 

[JackD]

This is a common problem when Excel people start using project. They often
use blank lines to format their project.
For most project VBA it is better to work on a task collection rather than
going through the cells in the "table" so while you could do it similar to
excel, it is better not to.

 

[Ken Wright]

Ahhhhh - Now I understand

Thanks again guys, very much appreciated.

 

[JackD]

I just put together a short post on this which explains the basics.
http://zo-d.com/blog/archives/project_management/microsoft_project/programming/index.html