Macro

[Brian Stebbins]

Hello all,

I'm by no means a project guru like the MVP's, however I have a good base
understanding of what project can do and most of the why's behind its
functions. However, I know next to nothing about VBA. So, I'm posting this
to ask for some advice on where to look for info pertaining to the Macro I
need to create. So without further digress...

My company has many different project files, and I'm only the manager of
some of them. The one's I have no interaction with are changing constantly
and certain changes in their schedules pertain to the way I run my project
schedules. Therefore I'd like to be able to know when a change is made to
some of these projects. More clearly put, when a deadline date changes in
Project A for Task 4, I'd like some sort of automation (macro if neccessary)
to notify me that the date has been changed so I can go look at the project
and find the changes made. I do NOT want notification if other tasks change
in the project.

1. Is this possible with a macro or other means?
2. If it IS possible with a macro, where's a good place to look for VBA
macro help/advice on how to do this?
3. If it's possible by some other means, what are those options?

Thank you all greatly, I don't post often on the site, but I read ALL posts
on it... You guys are great and I've learned a lot from other people's posts
and your solutions to them...

-Brian Stebbins

P.S.(The posts on 10/21/04 Re: Summary Task oder Milestones between dsz and
Steve House made my day... Steve... right on!! Oh, and one more thing, I
can't help but grin every time someone posts here and mentions using MUST
finish/start on constraints because I know that a Steve House reply is
coming. I find this newsgroup educational AND a good pick me up for a bad
day, humor helps make my world go 'round)

 

[John]

Brian,
First of all, we're glad the newsgroup provides both good information
and entertainment for you. At least from my viewpoint, that's the whole
purpose.

Although you said you have no interaction with other schedules, if
changes in them "pertain" to the way you administer your plan then there
is certainly interaction. A very easy solution may exist for you. If you
have access to the other project files, why not build a dynamically
consolidated master so all changes in the other entity's schedules is
reflected in your master? Then you can probably use a simple filter
process to detect changes.

However, assuming the master file idea is not possible, you can
basically always get what you need through VBA if what you need is not
available through Project's built-in functionality (e.g. views, reports,
etc.). With respect to getting VBA help, our sister newsgroup
(microsoft.public.developer) is the place to post although most, if not
all, of the MVPs check all newsgroups anyway. Whether you post here or
there, give us a little more information about your file structure (i.e.
your accessibility to the other files, etc.) and we can give more help.

John
Project MVP

 

[Brian Stebbins]

Thanks for the reply John... I right now have a Master Consolidated file
of all 28 projects i'm handling... there are about 40-50 other projects that
I have no interaction with. However, in about 10-15 of them (each project
has about 50-100 tasks) I need to know when only one or two tasks deadline
dates change. It would be a pain to have MORE than 30 consolidated projects
tacked into my master, especially when I only need to know about 20-30 tasks
changes out of 500-1000. I'm trying to take the work load off the poor
communication between departments. That's why ultimately I'm trying to
automate something so when the project's relevent task gets changed, I'll
just recieve an e-mail.
Like I said I need to know about the 20-30 tasks, but a change will be
made to ONE out of all of them MABYE twice in a month. 10 weeks ago I was
in fact DOING what you suggested, but it became to much of a time hassle for
the little amount of changes that actually were happening. Previously I had
the 10-15 projects in their own master file but keeping up with them was
hard when I have nothing to do with them exept print package release dates
We're re-doing a LOT of policies and procedures throughout my company and
during this phase of change the communication between different departments
has become... well... less than efficient. So, this is my attempt to fix at
least this ONE problem. That's why I think the way to go with this is to
make a macro to e-mail me if a task changes, however, this may not be the
best solution. That's why I put this out to other... for advice. I think a
macro like this would be possible, but I have no idea if I'm right, or where
to start looking. I appreciate your reply very much John, you were right on
in your thinking... it's what I've been doing (and will continue doing if
nothing comes of this). I just keep thinking, "there has to be a better
way."
Thank you to all who take the time to read my posts!!

-Brian Stebbins

 

[Brian Stebbins]

Oh and... one more thing... M-F (8AM -5PM Eastern-standard time) I check
this newsgroup about every 2 hours, so if anyone with advice has ANY
questions for me, I will answer as promptly as possible and to the best of
my ability. I thought I'd post this this week so next week I know what
direction to take this, and I'll have all next week to actually write the
macro, or do whatever else is necessary. Thanks again!!

 

[Rod Gill]

Hi,

What I've done for one client, is to create a project control centre. This
is a separate project file. Then there is one task per project with a
hyperlink to the .mpp file.

Then I have a macro that uses a VBA macro and oledb to read all summary data
directly from the source files when the update button is clicked. I have
another macro that creates a new consolidated file (no link to source files
so a snapshot is created). It wouldn't be too hard to set Flag1 to Yes for
every task whose deadline you want to monitor and have a macro that exports
all flagged task data to Excel.

I have also created Excel templates that do similar things so the template
imports flagged data from as many projects as you want.

--
For VBA posts, please use the public.project.developer group.
For any version of Project use public.project
For any version of Project Server use public. project.server

Rod Gill
Project MVP
For Microsoft Project companion projects, best practices and Project VBA
development services
visit www.project-systems.co.nz/
Email rodg AT project-systems DOT co DOT nz

 

[John]

Brian,
Thanks for the additional information, it helps us understand what you
want much better. I agree that a full up consolidated master can be
quite unweildy. The largest I've worked with was 70+ files with just shy
of 10K lines total and it was a challenge. Another simple solution you
might want to look at is to create a separate milestone in your master
for each external item you want to track. Then use an external link from
the source file to yours. When you open you file Project may or may not,
depending on version and settings, ask if you want to update links.
Doing so will give you the latest input.

From a VBA standpoint, it looks like Rod has given you some input.

Hope this helps.
John

 

[Brian R. Stebbins]

Thank you all for your time and thoughts... I'll keep you posted on what
direction I end up taking this... I have a meeting with my boss this
afternoon...

-Brian Stebbins

 

[Brian Stebbins]

Hello again fellow newsgroup readers!!

We've determined a course of action and I thought I would let anyone
who's curious know what direction we took it. I'm not experienced enough
with VBA to automate an e-mail notification, so... what I ended up doing
was automating (with help of my IT dept.) an indicator attached to a task.
If the deadline date for one of the tasks concerning me changes, an
indicator lets me know. I created a separate master project and only linked
what concerned me to it. I just check it every couple of days, and all I
have to do is open the file, look for a red indicator, and close it. It's
the best solution we have at this point givin my limited knowlege of VBA,
and the VBA guru at my company's limited knowlege of MS Project. I hope to
eventually work with him to get a system running so I just get an e-mail if
the date changes... but time has a funny way of slipping away from me while
I'm at work, so we'll see. If I DO get it up and running I'll post it here.
Well thanks to all for their input, and happy PMing!!

-Brian Stebbins