Filter MSP with data from excel

[TooN]

Hi..

Im new to MSP. Ive got a problem filtering the right data from a
Projectfile. I recently started a planning job in a big company. I received a
huge MSP file that contains the planning of the whole company. This file
contains several columns. One of those colums is called "Activity". In excell
ive got almost the same idea. A huge list (2000+ lines) that have all there
Activity number. The Activity number is Unique. In excell i can filter by
workcenter. Lets say i want a MSP planning that only is made for a specific
workcenter. First i filter the workcenter in Excell. That leaves me a list
with all several Activity's. I give an example of how that looks like:

EA02
EA12
EA13
EA14
EA16
EA22
EA23
EA24
EA26
K110
K115
K120
K125
K130
K135
K140
K145

This is one of the columns ofcourse, there are a lot more. So basicly after
filtering in excel ive got a list of activity's that are unique for a
workcenter. What i want to do now is open MSP, than select the column in
excel and paste the data (the unique activities) in a macro. Than the macro
filteres the data. There is a possibility to do this in excel and MSP. Just
select filter and than use "custom" BUT you can only fill in two options, ive
got at least a hundred evrytime!!!

I hope you understand a little bid what i want to do, if not let me know and
i explain again. I will give a short explanation:
1. Open MSP that contains the WHOLE project file
2. Open Excel that shows all activities of the company.
3. Filter the excel file for workcenter (or whatever)
4. select and copy the unique activities for a specific workcenter

so far no problem for me.

4. Filter the MSP.

After filtering i dont have the WHOLE company planning anymore but only for
a specific workcenter. Ofcourse i want the summary tasks to be there after
filtering and ofcourse the predecessors......

i hope anybody can help me with this...!!!

Thanks in advance.

 

[John]

Hi..

Im new to MSP. Ive got a problem filtering the right data from a
Projectfile. I recently started a planning job in a big company. I received a
huge MSP file that contains the planning of the whole company. This file
contains several columns. One of those colums is called "Activity". In excell
ive got almost the same idea. A huge list (2000+ lines) that have all there
Activity number. The Activity number is Unique. In excell i can filter by
workcenter. Lets say i want a MSP planning that only is made for a specific
workcenter. First i filter the workcenter in Excell. That leaves me a list
with all several Activity's. I give an example of how that looks like:

EA02
EA12
EA13
EA14
EA16
EA22
EA23
EA24
EA26
K110
K115
K120
K125
K130
K135
K140
K145

This is one of the columns ofcourse, there are a lot more. So basicly after
filtering in excel ive got a list of activity's that are unique for a
workcenter. What i want to do now is open MSP, than select the column in
excel and paste the data (the unique activities) in a macro. Than the macro
filteres the data. There is a possibility to do this in excel and MSP. Just
select filter and than use "custom" BUT you can only fill in two options, ive
got at least a hundred evrytime!!!

I hope you understand a little bid what i want to do, if not let me know and
i explain again. I will give a short explanation:
1. Open MSP that contains the WHOLE project file
2. Open Excel that shows all activities of the company.
3. Filter the excel file for workcenter (or whatever)
4. select and copy the unique activities for a specific workcenter

so far no problem for me.

4. Filter the MSP.

After filtering i dont have the WHOLE company planning anymore but only for
a specific workcenter. Ofcourse i want the summary tasks to be there after
filtering and ofcourse the predecessors......

i hope anybody can help me with this...!!!

Thanks in advance.

TooN,
First there is something I don't quite understand. If you have a Project
file will all the activities, including a field (column) specifying each
work center code, then why do you need Excel?

You say you want to filter the project data by work center and you have
several hundred of those. Instead of creating a simple filter that will
be unique for each work center, you could create what is called an
interactive filter. The structure will be something like this
(Project/Filter for/More filters):
And/Or Field Name Test Value
[your field] Contains "Enter work center"?
Where [your field] is the field in Project that contains the unique work
center code (e.g. Text1). You should also check the option to "show
related summary rows" in the lower left corner of the filter definition
window.

When the above filter is applied, a user input box will pop up asking
for the work center value. What this simple filter will NOT provide is
the list of predecessors to each of the tasks for that work center.
Doing that will require something a little more advanced - like VBA.

Hope this helps.
John
Project MVP

 

[TooN]

John,

thanks for the response. I think there is a little misunderstanding. The
list that ive got in excel is a small part of the entire planning ive got in
MSP. After filtering it in excel ive got a specific list of one workcenter.
That list is a small part of the whole planning of that workcenter thats in
MSP. lets say ive got a planning for one workcenter thats got 500 Activity's.
The excel list got about 300. The activity's are unique, so how do i get the
same activity's in MSP that ive got in that excel file. There is a way to
filter it. I just activate the "autofilter" and than click "custom". Than i
can fill in a activity "AND" another activity. That are only two but i need
at least 100...

The thing is that ive got different MSP files for all workcenters. That
makes it a bit easier. The Excell file im getting is straight out of SAP and
includes different Activities for ALL workcenters. So i filter out the
workcenter i want and than it gives me a list of activities for that specific
workcenter. I open the MSP that is for that workcenter and than the problem
starts....

I hope i explained a bit better now!!

Thanks

Hi..

Im new to MSP. Ive got a problem filtering the right data from a
Projectfile. I recently started a planning job in a big company. I received a
huge MSP file that contains the planning of the whole company. This file
contains several columns. One of those colums is called "Activity". In excell
ive got almost the same idea. A huge list (2000+ lines) that have all there
Activity number. The Activity number is Unique. In excell i can filter by
workcenter. Lets say i want a MSP planning that only is made for a specific
workcenter. First i filter the workcenter in Excell. That leaves me a list
with all several Activity's. I give an example of how that looks like:

EA02
EA12
EA13
EA14
EA16
EA22
EA23
EA24
EA26
K110
K115
K120
K125
K130
K135
K140
K145

This is one of the columns ofcourse, there are a lot more. So basicly after
filtering in excel ive got a list of activity's that are unique for a
workcenter. What i want to do now is open MSP, than select the column in
excel and paste the data (the unique activities) in a macro. Than the macro
filteres the data. There is a possibility to do this in excel and MSP. Just
select filter and than use "custom" BUT you can only fill in two options, ive
got at least a hundred evrytime!!!

I hope you understand a little bid what i want to do, if not let me know and
i explain again. I will give a short explanation:
1. Open MSP that contains the WHOLE project file
2. Open Excel that shows all activities of the company.
3. Filter the excel file for workcenter (or whatever)
4. select and copy the unique activities for a specific workcenter

so far no problem for me.

4. Filter the MSP.

After filtering i dont have the WHOLE company planning anymore but only for
a specific workcenter. Ofcourse i want the summary tasks to be there after
filtering and ofcourse the predecessors......

i hope anybody can help me with this...!!!

Thanks in advance.

TooN,
First there is something I don't quite understand. If you have a Project
file will all the activities, including a field (column) specifying each
work center code, then why do you need Excel?

You say you want to filter the project data by work center and you have
several hundred of those. Instead of creating a simple filter that will
be unique for each work center, you could create what is called an
interactive filter. The structure will be something like this
(Project/Filter for/More filters):
And/Or Field Name Test Value
[your field] Contains "Enter work center"?
Where [your field] is the field in Project that contains the unique work
center code (e.g. Text1). You should also check the option to "show
related summary rows" in the lower left corner of the filter definition
window.

When the above filter is applied, a user input box will pop up asking
for the work center value. What this simple filter will NOT provide is
the list of predecessors to each of the tasks for that work center.
Doing that will require something a little more advanced - like VBA.

Hope this helps.
John
Project MVP

 

[Jan De Messemaeker]

Hi Toon,

First, you would need Or and not And for your filter, but that's not the way
I suggest you to go.
You have to setup a fiter based on (for instane) a flag field, say flag1,
and set that field through a macro.
I took some assumptions, namely that the selected activities are the onlty
values in a sheet in Excel (I know this isn't true but you will have to
adapt your code at that point)
The macro could look like this:

sub ForToon
dim Xlapp as excel.application
dim Bk as workbook
Dim Sht as excel.worksheet
Dim Rg as excel.range
dim Job a task
Dim sel as excel.range

set xlapp=createobject("Excel.Application")
set bk=xlapp.workbooks.add("The nameoftheworkbook)
set Sht=Bk.sheets(1)
set Rg=sht.usedrange

for each job in activeproject.tasks
if not job is nothing then
job.flag1=false
for each sel in rg.cells
if job.text1=sel.value then
job.flag1=true
exit for
end if
next sel
end if
next job

end sub

This will probably run slow. You can speed it up by rading the excel values
into an array first and consult that one.

Hope this helps,


--
Jan De Messemaeker, Microsoft Project Most Valuable Professional
http://users.online.be/prom-ade/
For FAQs: http://www.mvps.org/project/faqs.htm

John,

thanks for the response. I think there is a little misunderstanding. The
list that ive got in excel is a small part of the entire planning ive got in
MSP. After filtering it in excel ive got a specific list of one workcenter.
That list is a small part of the whole planning of that workcenter thats in
MSP. lets say ive got a planning for one workcenter thats got 500 Activity's.
The excel list got about 300. The activity's are unique, so how do i get the
same activity's in MSP that ive got in that excel file. There is a way to
filter it. I just activate the "autofilter" and than click "custom". Than i
can fill in a activity "AND" another activity. That are only two but i need
at least 100...

The thing is that ive got different MSP files for all workcenters. That
makes it a bit easier. The Excell file im getting is straight out of SAP and
includes different Activities for ALL workcenters. So i filter out the
workcenter i want and than it gives me a list of activities for that specific
workcenter. I open the MSP that is for that workcenter and than the problem
starts....

I hope i explained a bit better now!!

Thanks

Hi..

Im new to MSP. Ive got a problem filtering the right data from a
Projectfile. I recently started a planning job in a big company. I received a
huge MSP file that contains the planning of the whole company. This file
contains several columns. One of those colums is called "Activity". In excell
ive got almost the same idea. A huge list (2000+ lines) that have all there
Activity number. The Activity number is Unique. In excell i can filter by
workcenter. Lets say i want a MSP planning that only is made for a specific
workcenter. First i filter the workcenter in Excell. That leaves me a list
with all several Activity's. I give an example of how that looks like:

EA02
EA12
EA13
EA14
EA16
EA22
EA23
EA24
EA26
K110
K115
K120
K125
K130
K135
K140
K145

This is one of the columns ofcourse, there are a lot more. So basicly after
filtering in excel ive got a list of activity's that are unique for a
workcenter. What i want to do now is open MSP, than select the column in
excel and paste the data (the unique activities) in a macro. Than the macro
filteres the data. There is a possibility to do this in excel and MSP. Just
select filter and than use "custom" BUT you can only fill in two options, ive
got at least a hundred evrytime!!!

I hope you understand a little bid what i want to do, if not let me know and
i explain again. I will give a short explanation:
1. Open MSP that contains the WHOLE project file
2. Open Excel that shows all activities of the company.
3. Filter the excel file for workcenter (or whatever)
4. select and copy the unique activities for a specific workcenter

so far no problem for me.

4. Filter the MSP.

After filtering i dont have the WHOLE company planning anymore but only for
a specific workcenter. Ofcourse i want the summary tasks to be there after
filtering and ofcourse the predecessors......

i hope anybody can help me with this...!!!

Thanks in advance.

TooN,
First there is something I don't quite understand. If you have a Project
file will all the activities, including a field (column) specifying each
work center code, then why do you need Excel?

You say you want to filter the project data by work center and you have
several hundred of those. Instead of creating a simple filter that will
be unique for each work center, you could create what is called an
interactive filter. The structure will be something like this
(Project/Filter for/More filters):
And/Or Field Name Test Value
[your field] Contains "Enter work center"?
Where [your field] is the field in Project that contains the unique work
center code (e.g. Text1). You should also check the option to "show
related summary rows" in the lower left corner of the filter definition
window.

When the above filter is applied, a user input box will pop up asking
for the work center value. What this simple filter will NOT provide is
the list of predecessors to each of the tasks for that work center.
Doing that will require something a little more advanced - like VBA.

Hope this helps.
John
Project MVP

 

[TooN]

Hello Jan,

thanks for responding. This sounds even better than what i had in mind.
Unfortunally i cant get the thing running! I must do something wrong. I
adjusted the code to this:

Sub ForToon()
Dim Xlapp As excel.Application
Dim Bk As workbook
Dim Sht As excel.worksheet
Dim Rg As excel.Range
Dim Job As Task
Dim sel As excel.Range

Set Xlapp = CreateObject("Excel.Application")
Set Bk = Xlapp.workbooks.Add("KAM")
Set Sht = Bk.sheets(ED06)
Set Rg = Sht.usedrange

For Each Job In ActiveProject.Tasks
If Not Job Is Nothing Then
Job.Flag20 = False
For Each sel In Rg.cells
If Job.Text3 = sel.Value Then
Job.Flag20 = True
Exit For
End If
Next sel
End If
Next Job

End Sub

When i run the macro it stops here:

Dim Xlapp As excel.Application

I adjusted this as well:
Set Xlapp = CreateObject("Excel.Application")
Set Bk = Xlapp.workbooks.Add("KAM")
Set Sht = Bk.sheets(ED06)
Set Rg = Sht.usedrange

The filename of the excelworkbook is KAM.xls
The sheetname with the activitys is called ED06 and its in column E. Do i
have to put (columnE) behind Sht.usedrange? If you can help me out with this
i really apreciate it.

Thanks in advance!

Hi Toon,

First, you would need Or and not And for your filter, but that's not the way
I suggest you to go.
You have to setup a fiter based on (for instane) a flag field, say flag1,
and set that field through a macro.
I took some assumptions, namely that the selected activities are the onlty
values in a sheet in Excel (I know this isn't true but you will have to
adapt your code at that point)
The macro could look like this:

sub ForToon
dim Xlapp as excel.application
dim Bk as workbook
Dim Sht as excel.worksheet
Dim Rg as excel.range
dim Job a task
Dim sel as excel.range

set xlapp=createobject("Excel.Application")
set bk=xlapp.workbooks.add("The nameoftheworkbook)
set Sht=Bk.sheets(1)
set Rg=sht.usedrange

for each job in activeproject.tasks
if not job is nothing then
job.flag1=false
for each sel in rg.cells
if job.text1=sel.value then
job.flag1=true
exit for
end if
next sel
end if
next job

end sub

This will probably run slow. You can speed it up by rading the excel values
into an array first and consult that one.

Hope this helps,


--
Jan De Messemaeker, Microsoft Project Most Valuable Professional
http://users.online.be/prom-ade/
For FAQs: http://www.mvps.org/project/faqs.htm

John,

thanks for the response. I think there is a little misunderstanding. The
list that ive got in excel is a small part of the entire planning ive got in
MSP. After filtering it in excel ive got a specific list of one workcenter.
That list is a small part of the whole planning of that workcenter thats in
MSP. lets say ive got a planning for one workcenter thats got 500 Activity's.
The excel list got about 300. The activity's are unique, so how do i get the
same activity's in MSP that ive got in that excel file. There is a way to
filter it. I just activate the "autofilter" and than click "custom". Than i
can fill in a activity "AND" another activity. That are only two but i need
at least 100...

The thing is that ive got different MSP files for all workcenters. That
makes it a bit easier. The Excell file im getting is straight out of SAP and
includes different Activities for ALL workcenters. So i filter out the
workcenter i want and than it gives me a list of activities for that specific
workcenter. I open the MSP that is for that workcenter and than the problem
starts....

I hope i explained a bit better now!!

Thanks

Hi..

Im new to MSP. Ive got a problem filtering the right data from a
Projectfile. I recently started a planning job in a big company. I received a
huge MSP file that contains the planning of the whole company. This file
contains several columns. One of those colums is called "Activity". In excell
ive got almost the same idea. A huge list (2000+ lines) that have all there
Activity number. The Activity number is Unique. In excell i can filter by
workcenter. Lets say i want a MSP planning that only is made for a specific
workcenter. First i filter the workcenter in Excell. That leaves me a list
with all several Activity's. I give an example of how that looks like:

EA02
EA12
EA13
EA14
EA16
EA22
EA23
EA24
EA26
K110
K115
K120
K125
K130
K135
K140
K145

This is one of the columns ofcourse, there are a lot more. So basicly after
filtering in excel ive got a list of activity's that are unique for a
workcenter. What i want to do now is open MSP, than select the column in
excel and paste the data (the unique activities) in a macro. Than the macro
filteres the data. There is a possibility to do this in excel and MSP. Just
select filter and than use "custom" BUT you can only fill in two options, ive
got at least a hundred evrytime!!!

I hope you understand a little bid what i want to do, if not let me know and
i explain again. I will give a short explanation:
1. Open MSP that contains the WHOLE project file
2. Open Excel that shows all activities of the company.
3. Filter the excel file for workcenter (or whatever)
4. select and copy the unique activities for a specific workcenter

so far no problem for me.

4. Filter the MSP.

After filtering i dont have the WHOLE company planning anymore but only for
a specific workcenter. Ofcourse i want the summary tasks to be there after
filtering and ofcourse the predecessors......

i hope anybody can help me with this...!!!

Thanks in advance.

TooN,
First there is something I don't quite understand. If you have a Project
file will all the activities, including a field (column) specifying each
work center code, then why do you need Excel?

You say you want to filter the project data by work center and you have
several hundred of those. Instead of creating a simple filter that will
be unique for each work center, you could create what is called an
interactive filter. The structure will be something like this
(Project/Filter for/More filters):
And/Or Field Name Test Value
[your field] Contains "Enter work center"?
Where [your field] is the field in Project that contains the unique work
center code (e.g. Text1). You should also check the option to "show
related summary rows" in the lower left corner of the filter definition
window.

When the above filter is applied, a user input box will pop up asking
for the work center value. What this simple filter will NOT provide is
the list of predecessors to each of the tasks for that work center.
Doing that will require something a little more advanced - like VBA.

Hope this helps.
John
Project MVP

 

[Jan De Messemaeker]

Hi Toon,

I must say I had hoped you would go a little further yourself, since you
post in the developer group and seemed to know some on Excel... but I now
have the impression you never saw a VBA macro from near ofr far... makes the
explanation a bit more difficult. The objective here IMHO is that I try to
help people doing it themselves - making a duly finished and tested program
is a bit beyond

By all means, I can already point to some changes you will need.

Do you try to run the application on Project VBA or in Excel VBA?
If in Project, in VB Editor go to Tools, References, and check on Microsoft
Excel.
That should take care of the Dim showstopper.


If you have the address of Bk, it is not a new book (which is what the .add
method provides) but you can define it as follows:

workbooks.open "Theaddressofthebook"
set bk=activeworkbook

I think the syntax is sheets("ED06") but not completely sure bout that

If the values to be considered are the only ones in column E, the syntax
could be
Set Rg=sht.columns("E")
If you cannot locate the right value yyou better buid the selection criteria
in the macro itself rather than in a filter.

Hope this helps,

--
Jan De Messemaeker, Microsoft Project Most Valuable Professional
http://users.online.be/prom-ade/
For FAQs: http://www.mvps.org/project/faqs.htm

Hello Jan,

thanks for responding. This sounds even better than what i had in mind.
Unfortunally i cant get the thing running! I must do something wrong. I
adjusted the code to this:

Sub ForToon()
Dim Xlapp As excel.Application
Dim Bk As workbook
Dim Sht As excel.worksheet
Dim Rg As excel.Range
Dim Job As Task
Dim sel As excel.Range

Set Xlapp = CreateObject("Excel.Application")
Set Bk = Xlapp.workbooks.Add("KAM")
Set Sht = Bk.sheets(ED06)
Set Rg = Sht.usedrange

For Each Job In ActiveProject.Tasks
If Not Job Is Nothing Then
Job.Flag20 = False
For Each sel In Rg.cells
If Job.Text3 = sel.Value Then
Job.Flag20 = True
Exit For
End If
Next sel
End If
Next Job

End Sub

When i run the macro it stops here:

Dim Xlapp As excel.Application

I adjusted this as well:
Set Xlapp = CreateObject("Excel.Application")
Set Bk = Xlapp.workbooks.Add("KAM")
Set Sht = Bk.sheets(ED06)
Set Rg = Sht.usedrange

The filename of the excelworkbook is KAM.xls
The sheetname with the activitys is called ED06 and its in column E. Do i
have to put (columnE) behind Sht.usedrange? If you can help me out with this
i really apreciate it.

Thanks in advance!

Hi Toon,

First, you would need Or and not And for your filter, but that's not the way
I suggest you to go.
You have to setup a fiter based on (for instane) a flag field, say flag1,
and set that field through a macro.
I took some assumptions, namely that the selected activities are the onlty
values in a sheet in Excel (I know this isn't true but you will have to
adapt your code at that point)
The macro could look like this:

sub ForToon
dim Xlapp as excel.application
dim Bk as workbook
Dim Sht as excel.worksheet
Dim Rg as excel.range
dim Job a task
Dim sel as excel.range

set xlapp=createobject("Excel.Application")
set bk=xlapp.workbooks.add("The nameoftheworkbook)
set Sht=Bk.sheets(1)
set Rg=sht.usedrange

for each job in activeproject.tasks
if not job is nothing then
job.flag1=false
for each sel in rg.cells
if job.text1=sel.value then
job.flag1=true
exit for
end if
next sel
end if
next job

end sub

This will probably run slow. You can speed it up by rading the excel values
into an array first and consult that one.

Hope this helps,


--
Jan De Messemaeker, Microsoft Project Most Valuable Professional
http://users.online.be/prom-ade/
For FAQs: http://www.mvps.org/project/faqs.htm

John,

thanks for the response. I think there is a little misunderstanding. The
list that ive got in excel is a small part of the entire planning ive

got
in

MSP. After filtering it in excel ive got a specific list of one workcenter.
That list is a small part of the whole planning of that workcenter

thats
in

MSP. lets say ive got a planning for one workcenter thats got 500 Activity's.
The excel list got about 300. The activity's are unique, so how do i

get
the

same activity's in MSP that ive got in that excel file. There is a way to
filter it. I just activate the "autofilter" and than click "custom".

Than
i

can fill in a activity "AND" another activity. That are only two but i need
at least 100...

The thing is that ive got different MSP files for all workcenters. That
makes it a bit easier. The Excell file im getting is straight out of

SAP
and

includes different Activities for ALL workcenters. So i filter out the
workcenter i want and than it gives me a list of activities for that specific
workcenter. I open the MSP that is for that workcenter and than the problem
starts....

I hope i explained a bit better now!!

Thanks

:

Hi..

Im new to MSP. Ive got a problem filtering the right data from a
Projectfile. I recently started a planning job in a big company. I received a
huge MSP file that contains the planning of the whole company.

This
file

contains several columns. One of those colums is called

"Activity". In
excell

ive got almost the same idea. A huge list (2000+ lines) that have

all
there

Activity number. The Activity number is Unique. In excell i can

filter
by

workcenter. Lets say i want a MSP planning that only is made for a specific
workcenter. First i filter the workcenter in Excell. That leaves

me a
list

with all several Activity's. I give an example of how that looks like:

EA02
EA12
EA13
EA14
EA16
EA22
EA23
EA24
EA26
K110
K115
K120
K125
K130
K135
K140
K145

This is one of the columns ofcourse, there are a lot more. So

basicly
after

filtering in excel ive got a list of activity's that are unique for a
workcenter. What i want to do now is open MSP, than select the

column
in

excel and paste the data (the unique activities) in a macro. Than

the
macro

filteres the data. There is a possibility to do this in excel and

MSP.
Just

select filter and than use "custom" BUT you can only fill in two options, ive
got at least a hundred evrytime!!!

I hope you understand a little bid what i want to do, if not let

me
know and

i explain again. I will give a short explanation:
1. Open MSP that contains the WHOLE project file
2. Open Excel that shows all activities of the company.
3. Filter the excel file for workcenter (or whatever)
4. select and copy the unique activities for a specific workcenter

so far no problem for me.

4. Filter the MSP.

After filtering i dont have the WHOLE company planning anymore but only for
a specific workcenter. Ofcourse i want the summary tasks to be

there
after

filtering and ofcourse the predecessors......

i hope anybody can help me with this...!!!

Thanks in advance.

TooN,
First there is something I don't quite understand. If you have a Project
file will all the activities, including a field (column) specifying each
work center code, then why do you need Excel?

You say you want to filter the project data by work center and you have
several hundred of those. Instead of creating a simple filter that will
be unique for each work center, you could create what is called an
interactive filter. The structure will be something like this
(Project/Filter for/More filters):
And/Or Field Name Test Value
[your field] Contains "Enter work center"?
Where [your field] is the field in Project that contains the unique work
center code (e.g. Text1). You should also check the option to "show
related summary rows" in the lower left corner of the filter definition
window.

When the above filter is applied, a user input box will pop up asking
for the work center value. What this simple filter will NOT provide is
the list of predecessors to each of the tasks for that work center.
Doing that will require something a little more advanced - like VBA.

Hope this helps.
John
Project MVP