Appending and Concatenating Text Fields

[Jim Aksel]

Appending text between two fields is easy:

dim tsk as task
for each tsk in activeproject.tasks
tsk.Text1=tsk.Text1 & tsk.Text2
next tsk

This code will change the tsk.Text1 to a combination of text1 and text2 for
each task.

Now, move to the next layer of abstraction. I have a form that asks the
user which two fields he wants. So the user may independently select "Name"
and Text24 from two different dop downs...cboSource and cboTakeFrom

So, I end up with a double nested Select Case:

Select Case cboSource.Value
Case "Name"
Select Case cboTakeFrom
Case "Name"
tsk.Name=tsk.Name + tsk.Name
Case "Text1"
tsk.Name = tsk.Name + tsk.Text1
Case "Text2"
tsk.Name=tsk.Name + tsk.Text2
....
End Select (cboTakeFrom)

Case "Text1" (Source is Text1)
Select Case cboTakeFrom
Case "Name"
tsk.Text1=tsk.Text1 + tsk.Name
....
Case "Text30"
tsk.Text1=tsk.Text1+tsk.Text30
End Select (cboTakeFrom)
Case "Text2"

..... (all the text based fields)
End Select

This will be hard to maintain.

What I would like to do is see if there is a different approach where I can
pass the arguments and avoid the double nested Select Case statements.
Something lazy like this:

private myFunction(byRef tsk as task, Source as ??, TakeFrom as ??)
tsk.Source=tsk.Source+tsk.TakeFrom
end function

So my call would look like this:

call myFunction(tsk, tsk.Name, tsk.Text24)

Any ideas? Just trying to avoid the brute force approach with something
having a little more finese.

Jim

 

[Rod Gill]

This solution works I think from 2002 onwards only. Try:

Sub Concat()
Dim FirstFieldName As String
Dim SecondFieldName As String
Dim DestFieldName As String
Dim Tsk As Task
'''''Get fieldnames here
For Each Tsk In ActiveProject.Tasks
If Not Tsk Is Nothing Then
Tsk.SetField FieldID:=FieldNameToFieldConstant(DestFieldName,
pjTask), _
Value:=Tsk.GetField(FieldNameToFieldConstant(FirstFieldName,
pjTask)) & _
Tsk.GetField(FieldNameToFieldConstant(SecondFieldName,
pjTask))
End If
Next Tsk
End Sub

Note that this technique is the same required to red/write custom fields in
Project Professional 2007.
--

Rod Gill
Project MVP

Project VBA Book, for details visit:
http://www.projectvbabook.com

NEW!! Web based VBA training course delivered by me. For details visit:
http://projectservertraining.com/learning/index.aspx

 

[Jim Aksel]

Thanks Rod. I was able to make it work. One minor change and that allows me
to use it bi-directionally so I can concatenate with either a prefix or a
suffix.

Have yourself a fine NZ beer, probably a bit early for that though.
--
If this post was helpful, please consider rating it.

Jim
It''s software; it''s not allowed to win.

Visit http://project.mvps.org/ for FAQs and more information
about Microsoft Project

 

[Rod Gill]

Hi Jim,

Great. Yes a little early for a beer: 10:00am at time of writing! Mind
you........

--

Rod Gill
Project MVP

Project VBA Book, for details visit:
http://www.projectvbabook.com

NEW!! Web based VBA training course delivered by me. For details visit:
http://projectservertraining.com/learning/index.aspx