Insert tasks in Excel file indented under specific Summary tasks

[wacNTN]

I am know enough about VBA to be dangerous but I think it's the only way to
do what I need to do.

I have an Excel file that has task name, resource name, start, and finish
date in it. The task may or may not already be on a schedule in Project.

I need to read the row in Excel and using the resource name, find the
summary task it should be indented under, check that it doesn't already exist
and if it doesn't, insert it under the associated Summary task.

Even better, I need to also check the every task in the Excel file does in
fact exist in the Project file, indented under the right Summary task, and
list any that don't somewhere - perferable on a work sheet in the source
Excel file.

I appreciate any ideas on this one.

Thanks,

Walter

 

[John]

I am know enough about VBA to be dangerous but I think it's the only way to
do what I need to do.

I have an Excel file that has task name, resource name, start, and finish
date in it. The task may or may not already be on a schedule in Project.

I need to read the row in Excel and using the resource name, find the
summary task it should be indented under, check that it doesn't already exist
and if it doesn't, insert it under the associated Summary task.

Even better, I need to also check the every task in the Excel file does in
fact exist in the Project file, indented under the right Summary task, and
list any that don't somewhere - perferable on a work sheet in the source
Excel file.

I appreciate any ideas on this one.

Thanks,

Walter

Walter,
Well it's pretty clear that you are in fact dangerous. My first question
is, why do you have a schedule plan in Project and Excel? Second, how
are the two related?

Now down to the heart of your question. A resource name and a summary
line do not go together. Summary lines are related to tasks, not
resources. If a resource is assigned to a task then that task will most
likely be under a summary line, but if that same resource is assigned to
multiple tasks, (a very likely scenario), then more than one summary
line may be applicable.

Instead of telling us what you want to do, why not give us some
background and explain your end goal - what are you doing and why? Then
we might be in a better position to help you.

John
Project MVP

 

[wacNTN]

John,

Thanks and good point. Here is the scoop.

To your first question, we use Project Server 2003 and Remedy. In Remedy,
some of our resources maintain their "out of office" calendars. A typical
record will have the person's name, the type of absence, start and end date
of the absence. This can be easily exported to Excel. We have an ODBC
connection setup for it already.

In Project Server, we use a project to record each person's out of the
office time. We do not use the Administrative project or individual
calendars for every resource. Our Microsoft partner advised against both of
those as well as several references we spoke to about Project Server.

Simply put, I need to get each out of office record in Remedy in to Project
Server on a once-per-week update basis. We don't want our staff to have to
maintain their calendars in two places.

After the first update, some records will exist in both systems - hence the
need to make sure the record is not already in Project Server before adding
it.

I follow what you are saying about a summary line and a resource not going
together. Where I probably confused you is that in the specific project that
has the out of office events listed, as individual tasks, each individual
resource's out of office events/tasks are indented under a summary task. The
name of the summary task is the resource's name. Like this...

Mary Jones
PTO 1 10/1/06 10/2/06 Mary Jones
Training1 11/6/06 11/10/06 Mary Jones
John Smith
Vacation 12/1/06 12/2/06 John Smith

etc. This is where my statement that I need to find the appropriate
summary row and insert the new row comes from. In our situation, and this
may be a first, a summary task and detail task do indeed "go together."

Thanks for any help you can give.
WAC

 

[Rod Gill]

MSProject Experts has a tool called Timeaway for updating calendars. See:
http://www.msprojectexperts.com/solutions/TimeAway.aspx

If this does not specifically fit your needs, then VBA code can do it for
you. Are you wanting Enterprise Resource calendars updated?

--

Rod Gill
Project MVP

NEW!! Project VBA Book, for details visit: http://www.projectvbabook.com

 

[Gary L. Chefetz [MVP]]

Walter:

I don't think you'll be very satisfied with the approach. I don't think it
would be a big deal to adapt our tool to your requirements. If you record
the away time in project plans, you'll simply be creating an overallocation.
If you update the resource calendars, Project automatically reschedules task
work around the calendar exceptions.

 

[wacNTN]

What does TimeAway cost?

We don't want another interface for individuals to use to record their PTO.
We would have to take the data from Remedy and run it through TimeAway in
the backend somehow. What would that effort cost?

We were steered clear of updating resource calendars as too much
maintenance. I am not sure I want a centralized calendar update to impact a
project manager's schedule. I would rather they recognize the
overallocation and respond to it. In many cases, a person may take a couple
of days off and still be able to meet the deadline because they have made
some arrangements to get it done.

Walter

 

[Gary L. Chefetz [MVP]]

Walter:

If you don't want to update calendars, then do what you're doing.