T
tension
Sorry for the messed up subject. I wanted to be as descriptive as i could
within the character limit.
Here is what I want to do.
I am in MS Project 2003 Pro.
I have a project file open (project1). from this file I'll run a macro
which will do the following:
open a specific excel file called data1
set the value of A1 as (aeh)
set the value of B1 as (bee)
set the value of C1 as (cee)
set the value of D1 as (dee)
open a new excel file, name it excel1
write column headers in row1
row = row + 1
write (aeh) in the cell in columnA
write (bee) in the cell in columnB
write (cee) in the cell in columnC
write (dee) in the cell in columnD
in the project1 file
for each task(t) in the active project file in which (t.text1) contains
(aeh) [something like If Instr((aeh), (t.text1))>0 then move to the next if,
else move to the next task]
if (t.text2) = "design" then put the start date in columnE and finish date
in columnF of the current row of excel1
if (t.text2) = "test" then put date1 in columnG of the current row of excel1
if (t.text2) = "build" then put date2 in columnH of the current row of excel1
if (t.text2) = "ship" then put date3 in columnI of the current row of excel1
next (t)
after all tasks in which (t.text1) contains (aeh) have been looped through
row = row + 1 in data1
set the value of A2 as (aeh)
set the value of B2 as (bee)
set the value of C2 as (cee)
set the value of D2 as (dee)
in file excel1
row = row + 1
write (aeh) in the columnA
write (bee) in the columnB
write (cee) in the columnC
write (dee) in the columnD
back to the project file
for each task(t) in the active project file in which (t).text1 contains
(aeh) [something like If Instr((aeh), (t.text1))>0 then
move to the next if, else move to the next task]
if (t.text2) = "design" then put the start date in columnE and finish date
in columnF of the current row of excel1
if (t.text2) = "test" then put date1 in columnG of the current row of excel1
if (t.text2) = "build" then put date2 in columnH of the current row of excel1
if (t.text2) = "ship" then put date3 in columnI and finish in columnJ of the
current row of excel1
next (t)
after all tasks in which (t).text1 contains (aeh) have been looped through
....repeat until there are no more values in data1
To sum up, I need to compare a list of data with tasks in a project, then
display data on a third sheet.
As of now, I am able to perform all of the tasks above that don't involve
data1, so it just puts the dates which meet the
criteria in the correct cells, which would work perfectly for small files
like the one I am testing with, but it will be
useless without being able to cross reference the data list, as the project
file contains 8000+ tasks, and the data list (which is a list of project
numbers) has about 1300+ lines. I need to use 'contains' instead of 'equals'
because text1 could have one or many project numbers like (A012) or (A012;
B281; Z887).
I guess it would be ok to put the column headers and dates from the project
file directly in to the data file and save it as
something else, instead of writing to a third file, thereby reducing the
amount of open excel files from two to one.
Thank you immensely for any help or hope you can offer.
within the character limit.
Here is what I want to do.
I am in MS Project 2003 Pro.
I have a project file open (project1). from this file I'll run a macro
which will do the following:
open a specific excel file called data1
set the value of A1 as (aeh)
set the value of B1 as (bee)
set the value of C1 as (cee)
set the value of D1 as (dee)
open a new excel file, name it excel1
write column headers in row1
row = row + 1
write (aeh) in the cell in columnA
write (bee) in the cell in columnB
write (cee) in the cell in columnC
write (dee) in the cell in columnD
in the project1 file
for each task(t) in the active project file in which (t.text1) contains
(aeh) [something like If Instr((aeh), (t.text1))>0 then move to the next if,
else move to the next task]
if (t.text2) = "design" then put the start date in columnE and finish date
in columnF of the current row of excel1
if (t.text2) = "test" then put date1 in columnG of the current row of excel1
if (t.text2) = "build" then put date2 in columnH of the current row of excel1
if (t.text2) = "ship" then put date3 in columnI of the current row of excel1
next (t)
after all tasks in which (t.text1) contains (aeh) have been looped through
row = row + 1 in data1
set the value of A2 as (aeh)
set the value of B2 as (bee)
set the value of C2 as (cee)
set the value of D2 as (dee)
in file excel1
row = row + 1
write (aeh) in the columnA
write (bee) in the columnB
write (cee) in the columnC
write (dee) in the columnD
back to the project file
for each task(t) in the active project file in which (t).text1 contains
(aeh) [something like If Instr((aeh), (t.text1))>0 then
move to the next if, else move to the next task]
if (t.text2) = "design" then put the start date in columnE and finish date
in columnF of the current row of excel1
if (t.text2) = "test" then put date1 in columnG of the current row of excel1
if (t.text2) = "build" then put date2 in columnH of the current row of excel1
if (t.text2) = "ship" then put date3 in columnI and finish in columnJ of the
current row of excel1
next (t)
after all tasks in which (t).text1 contains (aeh) have been looped through
....repeat until there are no more values in data1
To sum up, I need to compare a list of data with tasks in a project, then
display data on a third sheet.
As of now, I am able to perform all of the tasks above that don't involve
data1, so it just puts the dates which meet the
criteria in the correct cells, which would work perfectly for small files
like the one I am testing with, but it will be
useless without being able to cross reference the data list, as the project
file contains 8000+ tasks, and the data list (which is a list of project
numbers) has about 1300+ lines. I need to use 'contains' instead of 'equals'
because text1 could have one or many project numbers like (A012) or (A012;
B281; Z887).
I guess it would be ok to put the column headers and dates from the project
file directly in to the data file and save it as
something else, instead of writing to a third file, thereby reducing the
amount of open excel files from two to one.
Thank you immensely for any help or hope you can offer.