Exporting Calendar View & linking Excel Data to project Table

[Hobbit62]

I am using MSP 2003 Std Ed and have two problems I am not able to tackle.

1. How does one export the Calendar View to a republishable format. We ar
trying to get away from Text Lists of Program events and it would be ideal if
I could achieve BOTH program Schedule and Calendar in one MSP File

2. Is it possible to have an external cell reference, say to an Excel
computed field, for cost data in MSP 2003? For example, the client prefers
to copute cost data in Excel and then we need to move the data into project.
Yeah I know EV and cost control and management are at risk, however, they
want it done this way. Eventaully, I hope for an integrated tracking
scenario.

 

[John]

I am using MSP 2003 Std Ed and have two problems I am not able to tackle.

1. How does one export the Calendar View to a republishable format. We ar
trying to get away from Text Lists of Program events and it would be ideal if
I could achieve BOTH program Schedule and Calendar in one MSP File

2. Is it possible to have an external cell reference, say to an Excel
computed field, for cost data in MSP 2003? For example, the client prefers
to copute cost data in Excel and then we need to move the data into project.
Yeah I know EV and cost control and management are at risk, however, they
want it done this way. Eventaully, I hope for an integrated tracking
scenario.

Hobbit62,
And which Hobbit are you? I note that Sam the Hobbit is now in charge of
CTU in LA (Fox's "24" on Monday nights).

1. It depends on what you consider "re-publishable". Also I don't
understand your comment about program schedule and Calendar in one
Project file. All of Project's various views, built-in and customized,
are "in one file", it's simply a matter of choosing which view to
display. As far as replicating the Calendar view information in another
application, there are various methods. One is to use the Copy Picture
function (the little camera icon on the Standard toolbar). Another is to
use VBA to re-create the calendar graphic/data in another application.
You should also be able to capture the Calendar view as a PDF.

2. There are also various ways to link or import external data into
Project. If it is only a few cells, you could try using a Paste Link
(Copy/Paste Special/Paste Link as text) from Excel to Project. I will
warn you though, paste links have a reputation for getting corrupted,
however they generally work satisfactorily for single simple links as
you describe. Another method for tying the Excel data to Project is via
an import map. This may or may not be appropriate for your needs - read
more about importing in the Project help file. My preferred method for
tying Excel to Project is via a VBA macro, but it does of course require
a working knowledge of VBA. Finally, have you thought about showing your
client how to effectively use a formula in a custom field right in
Project. Then there is no need to link to Excel. It isn't difficult to
set up a custom field - if you have questions, we're like Home Depot,
you can do it - we can help.

Hope this helps.
John
Project MVP

 

[Hobbit62]

John,

Thanks for the initial answers. I think you have addressed #2....and yes I
am struggling with how to approach the client with "best practice" and
optimal resource use issues in general.

With regard to #1.....the schedule and the calendar are unique concepts
programmatically. I know I can handle both in MSP, but here is the rough
difference in concepts: You manage your prduct development on a schedule
[tracking WBS elements and Activities and so on], you interface with the
external community using a Calendar [Attending a Conference for a key staff
member]. So my thought is to have the top of the MSP file manage the
production of product work and use the bottom of the MSP file to track
community events, simply putting, say 10 blanks lines between them. This
helps manage total resource conflicts and provides an overview of who is
where at what time [and reduces the "I didn't know there was a meeting"
syndrome. but showing the event Calendar.

So back to the basic question: In MSP 2003, there is a view "Calendar".
You can directly print the image, but what I want to do is create the
"calendar image" as an MS object or HTML type object and import to a PPT
slide or publish on a web site portal. Using the "image capture" technique,
while it works, produces less than clear results.....it was tired prior to
the post. I have not written code in 15 years, and never VBA.....sigh.....so
I am looking for a less VBA-like answer. Thanks in advance.

Jordan....PS....I am just a Hobbit62, not of any fame, wealth, great
stories or snacks....

 

[John]

John,

Thanks for the initial answers. I think you have addressed #2....and yes I
am struggling with how to approach the client with "best practice" and
optimal resource use issues in general.

With regard to #1.....the schedule and the calendar are unique concepts
programmatically. I know I can handle both in MSP, but here is the rough
difference in concepts: You manage your prduct development on a schedule
[tracking WBS elements and Activities and so on], you interface with the
external community using a Calendar [Attending a Conference for a key staff
member]. So my thought is to have the top of the MSP file manage the
production of product work and use the bottom of the MSP file to track
community events, simply putting, say 10 blanks lines between them. This
helps manage total resource conflicts and provides an overview of who is
where at what time [and reduces the "I didn't know there was a meeting"
syndrome. but showing the event Calendar.

So back to the basic question: In MSP 2003, there is a view "Calendar".
You can directly print the image, but what I want to do is create the
"calendar image" as an MS object or HTML type object and import to a PPT
slide or publish on a web site portal. Using the "image capture" technique,
while it works, produces less than clear results.....it was tired prior to
the post. I have not written code in 15 years, and never VBA.....sigh.....so
I am looking for a less VBA-like answer. Thanks in advance.

Jordan....PS....I am just a Hobbit62, not of any fame, wealth, great
stories or snacks....

Jordan (the Hobbit),
I understand your concept about a product schedule and calendar. However
it seems you are trying to use Project to track everyday "work
maintenance" activities with Project and I think that's either overkill
or hedging on micromanagement. We all, walk the dog, read e-mail, go to
meetings, etc. and for very busy people it may be helpful to track those
activities with a appointment book, PDA, or other daily scheduling aid.
However trying to put those type of daily activities into a Project
schedule is in my opinion unwise and unnecessary.

Project is a scheduling/planning application best suited for developing
and tracking major defined (i.e. bounded) projects. When assigning
resources to work on tasks in a Project plan a project manager knows
that 100% utilization of those resources does NOT account for 100% of
their waking hours - and it shouldn't. The rule of thumb we used for
employee utilization was something like 80%. The 20% accounts for
vacation, sick days, and general inefficiency associated with being a
human being. If the feeling is that this 20% (or whatever) inefficiency
is a serious impact on the project plan, then I suggest the project plan
probably was developed with too much optimism.

To address your basic question, there may be some other methods for
capturing the Calendar view but I don't have any more suggestions.
Personally I've never had any use for the Calendar view. What I do
suggest though is that you consider using a more appropriate means of
tracking daily activities. Perhaps Outlook would be a good option.

Of course all of this is just my take on your issue. Maybe someone else
has a suggestion or two.

John
Project MVP

 

[John M.]

Regarding the 80% utilization - which I agree is a generally accepted
planning assumption... I'm curious what the most common means is for
incorporating this into a plan. I'm sure much is driven by personal
preference, but if somebody didn't have much of an opinion, which would be
recommended.

I can think of the following:

1. Set the availability for each resource to 80% and assign resources to
tasks at 80%. This would spread the non-project time evenly across the plan
by stretching durations by 25%. If you plan based on durations (e.g. 2 hour
meeting from 9-11), you would need to knowingly add 25% as hopefully people
don't only spend 1.6 hours of work in the 2 hour meeting.

2. Leave availability at 100% and account for the remaining 20% by changing
the working times. Instead of having 8 hours of working time per day,
change it to be 6hrs 24min (I always wanted to go to work at 8 and leave at
3:24).

3. Leave availability at 100% and create separate task(s) that span the
planning duration to account for the 20% of non-project time. Similar to #
1, except that the non-project tasks are included in the plan. Assumes the
project is responsible for the cost of the admin/vacation/sick time.

4. Leave availability at 100% and inflate the duration/effort for each task
by 25%...essentially spreading the 80% utilization throughout the plan.

To further complicate this, some projects have resources that are only
available part of their time (e.g. 50% available). The reality is that many
times these resources are available 100% of the time for 20 hours in a week
vs. available 50% of the time for 40 hours. This seems like semantics, but
if this resource is in a 2 hour meeting with a full-time resource, s/he
should be planned to participate for 2 hours and not just 1 hour. In these
cases, I generally push for defining time periods that the resource will be
dedicated to the project (e.g. Monday - Noon Wednesday) vs. trying to plan
based on a floating 20 hour availability. And then layer ontop of this one
of the 4+ options listed above.

John M.

John,

Thanks for the initial answers. I think you have addressed #2....and yes
I
am struggling with how to approach the client with "best practice" and
optimal resource use issues in general.

With regard to #1.....the schedule and the calendar are unique concepts
programmatically. I know I can handle both in MSP, but here is the rough
difference in concepts: You manage your prduct development on a schedule
[tracking WBS elements and Activities and so on], you interface with the
external community using a Calendar [Attending a Conference for a key
staff
member]. So my thought is to have the top of the MSP file manage the
production of product work and use the bottom of the MSP file to track
community events, simply putting, say 10 blanks lines between them. This
helps manage total resource conflicts and provides an overview of who is
where at what time [and reduces the "I didn't know there was a meeting"
syndrome. but showing the event Calendar.

So back to the basic question: In MSP 2003, there is a view "Calendar".
You can directly print the image, but what I want to do is create the
"calendar image" as an MS object or HTML type object and import to a PPT
slide or publish on a web site portal. Using the "image capture"
technique,
while it works, produces less than clear results.....it was tired prior
to
the post. I have not written code in 15 years, and never
VBA.....sigh.....so
I am looking for a less VBA-like answer. Thanks in advance.

Jordan....PS....I am just a Hobbit62, not of any fame, wealth, great
stories or snacks....

Jordan (the Hobbit),
I understand your concept about a product schedule and calendar. However
it seems you are trying to use Project to track everyday "work
maintenance" activities with Project and I think that's either overkill
or hedging on micromanagement. We all, walk the dog, read e-mail, go to
meetings, etc. and for very busy people it may be helpful to track those
activities with a appointment book, PDA, or other daily scheduling aid.
However trying to put those type of daily activities into a Project
schedule is in my opinion unwise and unnecessary.

Project is a scheduling/planning application best suited for developing
and tracking major defined (i.e. bounded) projects. When assigning
resources to work on tasks in a Project plan a project manager knows
that 100% utilization of those resources does NOT account for 100% of
their waking hours - and it shouldn't. The rule of thumb we used for
employee utilization was something like 80%. The 20% accounts for
vacation, sick days, and general inefficiency associated with being a
human being. If the feeling is that this 20% (or whatever) inefficiency
is a serious impact on the project plan, then I suggest the project plan
probably was developed with too much optimism.

To address your basic question, there may be some other methods for
capturing the Calendar view but I don't have any more suggestions.
Personally I've never had any use for the Calendar view. What I do
suggest though is that you consider using a more appropriate means of
tracking daily activities. Perhaps Outlook would be a good option.

Of course all of this is just my take on your issue. Maybe someone else
has a suggestion or two.

John
Project MVP

 

[John]

Regarding the 80% utilization - which I agree is a generally accepted
planning assumption... I'm curious what the most common means is for
incorporating this into a plan. I'm sure much is driven by personal
preference, but if somebody didn't have much of an opinion, which would be
recommended.

I can think of the following:

1. Set the availability for each resource to 80% and assign resources to
tasks at 80%. This would spread the non-project time evenly across the plan
by stretching durations by 25%. If you plan based on durations (e.g. 2 hour
meeting from 9-11), you would need to knowingly add 25% as hopefully people
don't only spend 1.6 hours of work in the 2 hour meeting.

2. Leave availability at 100% and account for the remaining 20% by changing
the working times. Instead of having 8 hours of working time per day,
change it to be 6hrs 24min (I always wanted to go to work at 8 and leave at
3:24).

3. Leave availability at 100% and create separate task(s) that span the
planning duration to account for the 20% of non-project time. Similar to #
1, except that the non-project tasks are included in the plan. Assumes the
project is responsible for the cost of the admin/vacation/sick time.

4. Leave availability at 100% and inflate the duration/effort for each task
by 25%...essentially spreading the 80% utilization throughout the plan.

To further complicate this, some projects have resources that are only
available part of their time (e.g. 50% available). The reality is that many
times these resources are available 100% of the time for 20 hours in a week
vs. available 50% of the time for 40 hours. This seems like semantics, but
if this resource is in a 2 hour meeting with a full-time resource, s/he
should be planned to participate for 2 hours and not just 1 hour. In these
cases, I generally push for defining time periods that the resource will be
dedicated to the project (e.g. Monday - Noon Wednesday) vs. trying to plan
based on a floating 20 hour availability. And then layer ontop of this one
of the 4+ options listed above.

John M.

John,
You certainly have laid out several approaches for "dealing" with the
80% rule of thumb but I never got into that level of refinement. I guess
your item 4 comes the closest to a simple approach we use. In my opinion
trying to refine the plan with resource efficiency factors is best left
to those who have a lot of time on their hands and can use it
effectively on detail analysis. For me, the world, particularly when
dealing with the human element, just isn't that easy to capture or
define.

For every project plan I have developed, my preferred approach is to
base effort on historical data, whether that data is used directly or
whether it is used as a basis for the current project. For example, say
we are developing a plan that includes development of a new power supply
(or it could be framing a house). I would first look for similar
projects that were performed in the past and review the data for
applicability. If a past project also developed a power supply, I ask
myself if the power supply for the current project is less complex,
about the same, or more complex than the previous design. Based on that
knowledge, I apply a complexity factor when estimating the work content
for the current plan. Obviously I need to have enough knowledge
(technical in this case) to make a valid assessment. If I'm an engineer
I can most likely make the assessment myself. If I'm a business person
doing the estimating for the current plan, I will need to consult with
appropriate experts. This is why I believe the best people to estimate a
plan are those who will be performing it.

So what do I do in the absence of historical data? Like everybody else I
use educated guestimation. Again, this is why I feel it is so important
when estimating to either have the people who will be performing the
work to do the estimate, or make sure their input is factored in. Plans
developed my management alone or in estimating think tanks are doomed to
failure or serious overruns.

Now, I'm sure you are thinking that approach 4 is open to estimate
"padding" by whomever, and you are right. There will always be those who
"game the system". The best guard against bogus estimates is an open
dialogue between the customer, management, and those who will perform
the actual work. Of course honesty, integrity and a commitment to be a
part of the "team" don't hurt.

John
Project MVP