automatic arrangement in MS Project for imported Excel-lists

[zdena]

Hello,

I am an absolute Beginner with VBA and ms Project. I ask you to
help me for my problem. In advance the best thanks.

I have a large Excel list with project-taken part and their working
dates. I import this list as follows in ms Project


MapEdit Name:="Kapaz_EMA_P_basis", DataCategory:=0,
FieldName:="Ressourcennamen", ExternalFieldName:="tbProjekteure.Name"
MapEdit Name:="Kapaz_EMA_P_basis", DataCategory:=0,
FieldName:="Text1", ExternalFieldName:="Angebotsnummer"
MapEdit Name:="Kapaz_EMA_P_basis", DataCategory:=0,
FieldName:="Name", ExternalFieldName:="Angebotsbezeichnung"
MapEdit Name:="Kapaz_EMA_P_basis", DataCategory:=0,
FieldName:="Text3", ExternalFieldName:="Erzeugnis"
MapEdit Name:="Kapaz_EMA_P_basis", DataCategory:=0,
FieldName:="Text2", ExternalFieldName:="AGAbteilung"
MapEdit Name:="Kapaz_EMA_P_basis", DataCategory:=0,
FieldName:="Ende", ExternalFieldName:="ZusageEMA"
MapEdit Name:="Kapaz_EMA_P_basis", DataCategory:=0,
FieldName:="Dauer", ExternalFieldName:="Dauer"
MapEdit Name:="Kapaz_EMA_P_basis", DataCategory:=0,
FieldName:="Text4", ExternalFieldName:="AbgabeIst"

FileOpen Name:="G:\FlexCon_Prozessteams\Einbindung_Prozessteams\PAV_Uebertrag\Puel_Test\1_kapaz.xls",
ReadOnly:=False, Merge:=0, map:="Kapaz_EMA_P_basis"
FileOpen Name:="G:\FlexCon_Prozessteams\Einbindung_Prozessteams\PAV_Uebertrag\Puel_Test\1_kapaz.xls",
ReadOnly:=False, Merge:=1, map:="prozent_arbeit_einlesen"



That goes so far well. Now I would like to make an outline
for the complete project. All tasks with same resources are to be
grouped in each case over VBA automatically in the following way


ressource1 task1
ressource1 task2
ressource1 task3
ressource2 task4
ressource2 task5

etc..

Now to:

Ressource1 (bold)
ressource1 task1
ressource1 task2
ressource1 task3
Ressource2 (bold)
ressource2 task4
ressource2 task5

Thus still a line must be introduced with the designation of
resources and under it arranged the tasks for this resources.

Can anyone help me? Thanks: -)



Zdena

Unfortunetely my english is so bad like my vba-knowledge

 

[JackD]

Hello,

I am an absolute Beginner with VBA and ms Project. I ask you to
help me for my problem. In advance the best thanks.

I have a large Excel list with project-taken part and their working
dates. I import this list as follows in ms Project


MapEdit Name:="Kapaz_EMA_P_basis", DataCategory:=0,
FieldName:="Ressourcennamen", ExternalFieldName:="tbProjekteure.Name"
MapEdit Name:="Kapaz_EMA_P_basis", DataCategory:=0,
FieldName:="Text1", ExternalFieldName:="Angebotsnummer"
MapEdit Name:="Kapaz_EMA_P_basis", DataCategory:=0,
FieldName:="Name", ExternalFieldName:="Angebotsbezeichnung"
MapEdit Name:="Kapaz_EMA_P_basis", DataCategory:=0,
FieldName:="Text3", ExternalFieldName:="Erzeugnis"
MapEdit Name:="Kapaz_EMA_P_basis", DataCategory:=0,
FieldName:="Text2", ExternalFieldName:="AGAbteilung"
MapEdit Name:="Kapaz_EMA_P_basis", DataCategory:=0,
FieldName:="Ende", ExternalFieldName:="ZusageEMA"
MapEdit Name:="Kapaz_EMA_P_basis", DataCategory:=0,
FieldName:="Dauer", ExternalFieldName:="Dauer"
MapEdit Name:="Kapaz_EMA_P_basis", DataCategory:=0,
FieldName:="Text4", ExternalFieldName:="AbgabeIst"

FileOpen Name:="G:\FlexCon_Prozessteams\Einbindung_Prozessteams\PAV_Uebertrag\Puel_Te
st\1_kapaz.xls",
ReadOnly:=False, Merge:=0, map:="Kapaz_EMA_P_basis"
FileOpen Name:="G:\FlexCon_Prozessteams\Einbindung_Prozessteams\PAV_Uebertrag\Puel_Te
st\1_kapaz.xls",
ReadOnly:=False, Merge:=1, map:="prozent_arbeit_einlesen"



That goes so far well. Now I would like to make an outline
for the complete project. All tasks with same resources are to be
grouped in each case over VBA automatically in the following way


ressource1 task1
ressource1 task2
ressource1 task3
ressource2 task4
ressource2 task5

etc..

Now to:

Ressource1 (bold)
ressource1 task1
ressource1 task2
ressource1 task3
Ressource2 (bold)
ressource2 task4
ressource2 task5

Thus still a line must be introduced with the designation of
resources and under it arranged the tasks for this resources.

Can anyone help me? Thanks: -)



Zdena

The answer is to write your own export routine.
It is not so hard.
For an example look at the "export heirarchy to excel" macro on my website:

http://masamiki.com/project/macros.htm

It will need a little modification.

The exporting code would be something like this:

For Each Resource In ActiveProject.resources
'write resource.name to excel
'move down one row and right one row
For Each Assignment In Resource.Assignments
'Write Assignment.TaskName
'move down one row
Next Assignment
'move left one column
Next Resource

With a little study of what is going on in my macro you should be able to
put this in the body of the macro instead of the task information.
If you are having trouble with that, let me know and I'll give you more
help.

-Jack

 

[zdena]

Hello Jack,
thank you for your fast helpful shank. I am the first
time in this forum and I was not sure whether I get an indication from
there outside...

I already know your program code. I found and read it still
before my Posting. It however doesn´t point my problem completely.

The list, which I have after the import from Excel in the ms Project,
is a simple, linear list. Thus no arrangement! I would like to creat now
however over VBA an arrangement in the ms Project(!) . I would
like to combine all task with the same editor/ressource into a group. This
group has a new line in the ms Project with the designation of the
editor/ressource. The picture in the ms Project (!) after the macro:


I am very grateful, if you would concern yourself again with my problem. If
you need still something, ask me simply. Thanks.

Zdena