Joining elements of a String Array

[Michael A.]

Hello,

Does anyone know how I could search for an element within a string array? I
have Rod Gill's book - he mentions just splitting strings. If I have an
array containing ("A" "B" "C" "D") and I want to find "B" without having to
always use a for loop.

I have tried using Join and xlapp.worksheetfunction.match both of which
returns a compiling error.

Thanks
Michael Andersen

 

[Steve]

If you define that element as the index of the array, then you can just
refer to array["B"]. Of course, that assumes that there will only be one
"B".

If you truly want "B" to be the value and may store multiple entries of it
in the array, then you have to search for it, and that means looping through
the array.

HTH

 

[Michael A.]

Thanks Steve. I did some more on this through the early hours...

I hear you in that I just have to loop through the array to find an element
which confirms that neither Join or Match works in Project VBA.

What I am trying to do is summarise all the task assignments for a given
deliverable/work package. Task 1 may have "A" and "B assigned, Task 2 may
also have "A" assigned, Task 3 may have "C" assigned.

The top level logic of course is to loop through all the tasks then loop
through all the assignments.

I am thinking that I would store all the assignment values for a deliverable
in an array, however I don't know the size of the array until I get to the
last task for the deliverable.

For Task 1, that is easy redefine the array of size 2 (in this example);
Task 2, loop through find "A" and store the required values; Task 3 finds a
new assignment which I then need to add to the array but it has already been
defined.

I am thinking I could risk using the "preserve" keyword and redefine the
array again, or go through all the tasks for a deliverable, once to find the
total assignments, and second, to store the values, which slows things down.

That is the extent of my problem.


Thanks and regards
Michael A.

If you define that element as the index of the array, then you can just
refer to array["B"]. Of course, that assumes that there will only be one
"B".

If you truly want "B" to be the value and may store multiple entries of it
in the array, then you have to search for it, and that means looping through
the array.

HTH

Hello,

Does anyone know how I could search for an element within a string array?
I
have Rod Gill's book - he mentions just splitting strings. If I have an
array containing ("A" "B" "C" "D") and I want to find "B" without having
to
always use a for loop.

I have tried using Join and xlapp.worksheetfunction.match both of which
returns a compiling error.

Thanks
Michael Andersen

 

[Rod Gill]

You could just append each resource name to a string with a special
character (EG "|") as a divide. You can then search the string using Instr
then divide the string into an array at the end using Split().

--

Rod Gill
Microsoft MVP for Project

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

Thanks Steve. I did some more on this through the early hours...

I hear you in that I just have to loop through the array to find an
element
which confirms that neither Join or Match works in Project VBA.

What I am trying to do is summarise all the task assignments for a given
deliverable/work package. Task 1 may have "A" and "B assigned, Task 2 may
also have "A" assigned, Task 3 may have "C" assigned.

The top level logic of course is to loop through all the tasks then loop
through all the assignments.

I am thinking that I would store all the assignment values for a
deliverable
in an array, however I don't know the size of the array until I get to the
last task for the deliverable.

For Task 1, that is easy redefine the array of size 2 (in this example);
Task 2, loop through find "A" and store the required values; Task 3 finds
a
new assignment which I then need to add to the array but it has already
been
defined.

I am thinking I could risk using the "preserve" keyword and redefine the
array again, or go through all the tasks for a deliverable, once to find
the
total assignments, and second, to store the values, which slows things
down.

That is the extent of my problem.


Thanks and regards
Michael A.

If you define that element as the index of the array, then you can just
refer to array["B"]. Of course, that assumes that there will only be one
"B".

If you truly want "B" to be the value and may store multiple entries of
it
in the array, then you have to search for it, and that means looping
through
the array.

HTH

Hello,

Does anyone know how I could search for an element within a string
array?
I
have Rod Gill's book - he mentions just splitting strings. If I have
an
array containing ("A" "B" "C" "D") and I want to find "B" without
having
to
always use a for loop.

I have tried using Join and xlapp.worksheetfunction.match both of which
returns a compiling error.

Thanks
Michael Andersen

 

[Jan De Messemaeker]

Hi,

Why not group the assignments?

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf

Thanks Steve. I did some more on this through the early hours...

I hear you in that I just have to loop through the array to find an
element
which confirms that neither Join or Match works in Project VBA.

What I am trying to do is summarise all the task assignments for a given
deliverable/work package. Task 1 may have "A" and "B assigned, Task 2 may
also have "A" assigned, Task 3 may have "C" assigned.

The top level logic of course is to loop through all the tasks then loop
through all the assignments.

I am thinking that I would store all the assignment values for a
deliverable
in an array, however I don't know the size of the array until I get to the
last task for the deliverable.

For Task 1, that is easy redefine the array of size 2 (in this example);
Task 2, loop through find "A" and store the required values; Task 3 finds
a
new assignment which I then need to add to the array but it has already
been
defined.

I am thinking I could risk using the "preserve" keyword and redefine the
array again, or go through all the tasks for a deliverable, once to find
the
total assignments, and second, to store the values, which slows things
down.

That is the extent of my problem.


Thanks and regards
Michael A.

If you define that element as the index of the array, then you can just
refer to array["B"]. Of course, that assumes that there will only be one
"B".

If you truly want "B" to be the value and may store multiple entries of
it
in the array, then you have to search for it, and that means looping
through
the array.

HTH

Hello,

Does anyone know how I could search for an element within a string
array?
I
have Rod Gill's book - he mentions just splitting strings. If I have
an
array containing ("A" "B" "C" "D") and I want to find "B" without
having
to
always use a for loop.

I have tried using Join and xlapp.worksheetfunction.match both of which
returns a compiling error.

Thanks
Michael Andersen

 

[Michael A.]

Thanks for the tip Rod. Great book by the way - just picked it up last week.

Hello Jan,

Terrific question. That is exactly what I do in project itself is group by
outline and assignments. Then I used to do a separate (manual) copy and
paste of the two panes to get the data into Excel. Also it was a bit messy
with things like "Resource Names" preceding the resource name, etc. It
wasn't that pretty and still needed a bit of work.

So I thought I would do this using VBA. I initially wrote some simple and
lengthy code to do what I needed - it was great. It just wasn't dynamic to
work with changing resources. Now, though I wanted to smarten and simplify
the code. I did try recording the macro to do the grouping but it didn't
really improve the output.

Thanks
Michael A.

Hi,

Why not group the assignments?

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf

Thanks Steve. I did some more on this through the early hours...

I hear you in that I just have to loop through the array to find an
element
which confirms that neither Join or Match works in Project VBA.

What I am trying to do is summarise all the task assignments for a given
deliverable/work package. Task 1 may have "A" and "B assigned, Task 2 may
also have "A" assigned, Task 3 may have "C" assigned.

The top level logic of course is to loop through all the tasks then loop
through all the assignments.

I am thinking that I would store all the assignment values for a
deliverable
in an array, however I don't know the size of the array until I get to the
last task for the deliverable.

For Task 1, that is easy redefine the array of size 2 (in this example);
Task 2, loop through find "A" and store the required values; Task 3 finds
a
new assignment which I then need to add to the array but it has already
been
defined.

I am thinking I could risk using the "preserve" keyword and redefine the
array again, or go through all the tasks for a deliverable, once to find
the
total assignments, and second, to store the values, which slows things
down.

That is the extent of my problem.


Thanks and regards
Michael A.

If you define that element as the index of the array, then you can just
refer to array["B"]. Of course, that assumes that there will only be one
"B".

If you truly want "B" to be the value and may store multiple entries of
it
in the array, then you have to search for it, and that means looping
through
the array.

HTH

Hello,

Does anyone know how I could search for an element within a string
array?
I
have Rod Gill's book - he mentions just splitting strings. If I have
an
array containing ("A" "B" "C" "D") and I want to find "B" without
having
to
always use a for loop.

I have tried using Join and xlapp.worksheetfunction.match both of which
returns a compiling error.

Thanks
Michael Andersen

 

[Rod Gill]

Delighted you enjoy the book and good luck!

--

Rod Gill
Microsoft MVP for Project

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

Thanks for the tip Rod. Great book by the way - just picked it up last
week.

Hello Jan,

Terrific question. That is exactly what I do in project itself is group
by
outline and assignments. Then I used to do a separate (manual) copy and
paste of the two panes to get the data into Excel. Also it was a bit
messy
with things like "Resource Names" preceding the resource name, etc. It
wasn't that pretty and still needed a bit of work.

So I thought I would do this using VBA. I initially wrote some simple and
lengthy code to do what I needed - it was great. It just wasn't dynamic
to
work with changing resources. Now, though I wanted to smarten and
simplify
the code. I did try recording the macro to do the grouping but it didn't
really improve the output.

Thanks
Michael A.

Hi,

Why not group the assignments?

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf

Thanks Steve. I did some more on this through the early hours...

I hear you in that I just have to loop through the array to find an
element
which confirms that neither Join or Match works in Project VBA.

What I am trying to do is summarise all the task assignments for a
given
deliverable/work package. Task 1 may have "A" and "B assigned, Task 2
may
also have "A" assigned, Task 3 may have "C" assigned.

The top level logic of course is to loop through all the tasks then
loop
through all the assignments.

I am thinking that I would store all the assignment values for a
deliverable
in an array, however I don't know the size of the array until I get to
the
last task for the deliverable.

For Task 1, that is easy redefine the array of size 2 (in this
example);
Task 2, loop through find "A" and store the required values; Task 3
finds
a
new assignment which I then need to add to the array but it has already
been
defined.

I am thinking I could risk using the "preserve" keyword and redefine
the
array again, or go through all the tasks for a deliverable, once to
find
the
total assignments, and second, to store the values, which slows things
down.

That is the extent of my problem.


Thanks and regards
Michael A.

:

If you define that element as the index of the array, then you can
just
refer to array["B"]. Of course, that assumes that there will only be
one
"B".

If you truly want "B" to be the value and may store multiple entries
of
it
in the array, then you have to search for it, and that means looping
through
the array.

HTH

Hello,

Does anyone know how I could search for an element within a string
array?
I
have Rod Gill's book - he mentions just splitting strings. If I
have
an
array containing ("A" "B" "C" "D") and I want to find "B" without
having
to
always use a for loop.

I have tried using Join and xlapp.worksheetfunction.match both of
which
returns a compiling error.

Thanks
Michael Andersen

 

[Steve]

Michael,

Have you had a look at the various reports you can generate. I suspect that
one of them might do what you want without programming.

HTH

Thanks Steve. I did some more on this through the early hours...

I hear you in that I just have to loop through the array to find an
element
which confirms that neither Join or Match works in Project VBA.

What I am trying to do is summarise all the task assignments for a given
deliverable/work package. Task 1 may have "A" and "B assigned, Task 2 may
also have "A" assigned, Task 3 may have "C" assigned.

The top level logic of course is to loop through all the tasks then loop
through all the assignments.

I am thinking that I would store all the assignment values for a
deliverable
in an array, however I don't know the size of the array until I get to the
last task for the deliverable.

For Task 1, that is easy redefine the array of size 2 (in this example);
Task 2, loop through find "A" and store the required values; Task 3 finds
a
new assignment which I then need to add to the array but it has already
been
defined.

I am thinking I could risk using the "preserve" keyword and redefine the
array again, or go through all the tasks for a deliverable, once to find
the
total assignments, and second, to store the values, which slows things
down.

That is the extent of my problem.


Thanks and regards
Michael A.

If you define that element as the index of the array, then you can just
refer to array["B"]. Of course, that assumes that there will only be one
"B".

If you truly want "B" to be the value and may store multiple entries of
it
in the array, then you have to search for it, and that means looping
through
the array.

HTH

Hello,

Does anyone know how I could search for an element within a string
array?
I
have Rod Gill's book - he mentions just splitting strings. If I have
an
array containing ("A" "B" "C" "D") and I want to find "B" without
having
to
always use a for loop.

I have tried using Join and xlapp.worksheetfunction.match both of which
returns a compiling error.

Thanks
Michael Andersen

 

[MichaelA]

Hello Steve,

Thanks for the advice. You are quite right; however, it only
generates a report that I have to print whereas I am needing to export
the data into a Excel readable (comma/tab delimited) file for
integration into other applications.


thanks
Michael a.

Michael,

Have you had a look at the various reports you can generate. I suspect that
one of them might do what you want without programming.

HTH

Thanks Steve.  I did some more on this through the early hours...

I hear you in that I just have to loop through the array to find an
element
which confirms that neither Join or Match works in Project VBA.

What I am trying to do is summarise all the task assignments for a given
deliverable/work package.  Task 1 may have "A" and "B assigned, Task 2 may
also have "A" assigned, Task 3 may have "C" assigned.

The top level logic of course is to loop through all the tasks then loop
through all the assignments.

I am thinking that I would store all the assignment values for a
deliverable
in an array, however I don't know the size of the array until I get to the
last task for the deliverable.

For Task 1, that is easy redefine the array of size 2 (in this example);
Task 2, loop through find "A" and store the required values; Task 3 finds
a
new assignment which I then need to add to the array but it has already
been
defined.

I am thinking I could risk using the "preserve" keyword and redefine the
array again, or go through all the tasks for a deliverable, once to find
the
total assignments, and second, to store the values, which slows things
down.

That is the extent of my problem.

Thanks and regards
Michael A.

If you define that element as the index of the array, then you can just
refer to array["B"]. Of course, that assumes that there will only be one
"B".
If you truly want "B" to be the value and may store multiple entries of
it
in the array, then you have to search for it, and that means looping
through
the array.
HTH
Hello,
Does anyone know how I could search for an element within a string
array?
I
have Rod Gill's book - he mentions just splitting strings.  If I have
an
array containing ("A" "B" "C" "D") and I want to find "B" without
having
to
always use a for loop.
I have tried using Join and xlapp.worksheetfunction.match both of which
returns a compiling error.
Thanks
Michael Andersen- Hide quoted text -

- Show quoted text -

 

[Jack Dahlgren MVP]

Michael,

If you are using Project 2007 look at the Visual Reports. They output to
Excel.

-Jack Dahlgren


Hello Steve,

Thanks for the advice. You are quite right; however, it only
generates a report that I have to print whereas I am needing to export
the data into a Excel readable (comma/tab delimited) file for
integration into other applications.


thanks
Michael a.

Michael,

Have you had a look at the various reports you can generate. I suspect
that
one of them might do what you want without programming.

HTH

Thanks Steve. I did some more on this through the early hours...

I hear you in that I just have to loop through the array to find an
element
which confirms that neither Join or Match works in Project VBA.

What I am trying to do is summarise all the task assignments for a given
deliverable/work package. Task 1 may have "A" and "B assigned, Task 2
may
also have "A" assigned, Task 3 may have "C" assigned.

The top level logic of course is to loop through all the tasks then loop
through all the assignments.

I am thinking that I would store all the assignment values for a
deliverable
in an array, however I don't know the size of the array until I get to
the
last task for the deliverable.

For Task 1, that is easy redefine the array of size 2 (in this example);
Task 2, loop through find "A" and store the required values; Task 3
finds
a
new assignment which I then need to add to the array but it has already
been
defined.

I am thinking I could risk using the "preserve" keyword and redefine the
array again, or go through all the tasks for a deliverable, once to find
the
total assignments, and second, to store the values, which slows things
down.

That is the extent of my problem.

Thanks and regards
Michael A.

If you define that element as the index of the array, then you can just
refer to array["B"]. Of course, that assumes that there will only be
one
"B".
If you truly want "B" to be the value and may store multiple entries of
it
in the array, then you have to search for it, and that means looping
through
the array.
HTH
Hello,
Does anyone know how I could search for an element within a string
array?
I
have Rod Gill's book - he mentions just splitting strings. If I have
an
array containing ("A" "B" "C" "D") and I want to find "B" without
having
to
always use a for loop.
I have tried using Join and xlapp.worksheetfunction.match both of
which
returns a compiling error.
Thanks
Michael Andersen- Hide quoted text -

- Show quoted text -