Automated importing from Excel to Project

[emadden]

Hello,
The company I work for has recently purchsed MS Project 2007 to help
organize and control their production process. I am not an expert in the use
of this software, but I am in charge of this task. One of the things that
they would like to be able to do is have an Excel file (in which all the
tasks are listed) that all employees can access via an internal network.
After an employee marks a task in this Excel file as complete, we would like
for this to be updated within MS Project (either automatically or the first
time the Project file is opened after the change occurs).

Is this possible? If so, how would I go about doing it?

I know that Project Server is a very simple solution, but at this point it
is too expensive to offset any of the reduced operating cost.


Thank you

 

[John]

Hello,
The company I work for has recently purchsed MS Project 2007 to help
organize and control their production process. I am not an expert in the use
of this software, but I am in charge of this task. One of the things that
they would like to be able to do is have an Excel file (in which all the
tasks are listed) that all employees can access via an internal network.
After an employee marks a task in this Excel file as complete, we would like
for this to be updated within MS Project (either automatically or the first
time the Project file is opened after the change occurs).

Is this possible? If so, how would I go about doing it?

I know that Project Server is a very simple solution, but at this point it
is too expensive to offset any of the reduced operating cost.


Thank you

emadden,
Right before I read your last sentence I was going to recommend Project
Server. However, you apparently already considered that option and
discounted it.

First of all, although Project is a planning and scheduling application,
it isn't all that great for scheduling production processes that have a
large number of like deliverables. There are better applications better
suited for production planning and scheduling but at the moment I can't
recall the names of any of them. Project is best for design and
development or other types of projects that have a defined start and
finish with a limited number of deliverables.

Also, if your employees are planning and tracking "progress" in Excel it
sounds like you will be using Project as an "after thought" schedule
tracker. Why bother? If you are going to use Project for your
scheduling, then I first suggest you and your fellow employees learn how
to use Project and move away from Excel, although Excel is a much more
user friendly application.

To answer your basic question, the best way to update a project file
from Excel data on regular intervals is to use VBA. One time transfers
can be accomplished with export/import maps that shuffle data between
the two applications, but for regular continuous transfers, VBA is much
better and more flexible. You may be tempted to use paste links between
the two files but I strongly recommend against it. Paste links are just
too fragile and corrupt easily.

John
Project MVP

 

[emadden]

Thank you John,

I have forwarded this information and we are now considering possibly
outsourcing the VBA coding to a third party source. Before I go ahead and do
this, I would like to ask you for your advice. Is the task of making this
macro too difficult/complex for a beginner like myself?

For further detail, the company I work for consults with companies regarding
their retirement plans. The production process involves the completion of
fund evaluation and analysis as well as some quality control steps. This
quarter we had a little over 500 funds. Do you not think that this is within
the realm of MS Project?

Employees currently update progress through an Excel "Completion Sheet" in
which they date and initial each step as it progresses. We obtained MS
Project in the hopes that our Investment Analysis Manager could gain a better
understanding of the length ocurring at each step and for the controlling
features that it offers.

 

[Hisham]

Hi Emadden:

What you are asking is possible, you can upload changes from excel to your
Project Gantt by using the Project Import Wizard, which automitically maps
the imported data to appropriate fields in your project, to do so please
follow the steps below:

1- Before we start, you need to have a key column in the excel file that
will be used as the merge key during the import process, usually this is the
ID column in the project gantt, also use approporiate headers in your excel
preferrably the same names used in the Gantt column headers.

For example, I will assume that your excel file has the columns (ID, Name,
and % Complete). Use these names as headers on top of the excel columns.

2- From the MS Project, open the project file that you want to update, and
lets first review the security settings. Go to (Tools -> Options -> Security)
and note the "Legacy Formats" section and then select the second option
"Prompt when loading files .. etc", then click "OK"

3- Now we are ready to import, Go to (File -> Open) and note the field
(Files of Type) choose (Microsoft Excel Workbook *.xls) then specify the file
name, which is the excel file you want to import, then click Open.

This will initiate the Project Import Wizard.

3- On the import wizard, click Next, then select New Map, click Next, then
select "Merge the Data into the Active Project" this will allow updating the
active project with the imported excel data. Clieck Next.

4- From the Map Options screen, select the checkbox of Tasks, and keep the
default "Import Includes Headers" then click Next.

5- Now you are on the Task Mapping screen, from the field "Source Worksheet
Name" select which sheet in the workbook you want to import , this will
generate a list of columns as in the excel sheet with the correspondent
fields in MS Project. Review and correct the mapping fields by selecting the
project fields if any missings, in our exmple you should have the Project
fields (ID, Name, % Complete) respectively.

6- Before we continue, you need now to set the merge key, stand on the row
that has the ID field, then select the button "Set Merge Key" and note the
changes on the ID field which is now prefixed with "Merge Key".

7- You are ready now to import, click Next, you can save the map for future
use, click Finish. You might get a warning message here that you are opening
a file of old format asking if you want to continue, Click Yes.

The project will import and reflect the changes based on the above steps.

Hope this helps!

Regards,

Hisham
http://epmforum.wordpress.com

 

[emadden]

But is there a way that I can automate this process (especially the
percentages complete) in the future?

 

[John]

Thank you John,

emadden,
You're welcome and let me answer your items in line.

I have forwarded this information and we are now considering possibly
outsourcing the VBA coding to a third party source. Before I go ahead and do
this, I would like to ask you for your advice. Is the task of making this
macro too difficult/complex for a beginner like myself?

Difficult is a relative term. If you know nothing about VBA, then it
would be a challenging undertaking. However if you already have some VBA
experience, developing the necessary macro shouldn't be too difficult.
Fellow MVP, Rod Gill, has an excellent book on Project VBA. For more
information, go to, http://www.projectvbabook.com. Besides being the
best book I've seen on Project VBA, Rod's book contains several examples
of interfacing Project with Excel using VBA.

For further detail, the company I work for consults with companies regarding
their retirement plans. The production process involves the completion of
fund evaluation and analysis as well as some quality control steps. This
quarter we had a little over 500 funds. Do you not think that this is within
the realm of MS Project?

The "production" I was thinking of was factory based, that is, a company
that runs a production line to produce some type of end product or
products (e.g. automobiles, TVs, cookie cutters, etc.). Your "product"
is more of an evaluation effort with probably some type of yea or nay
concerning the suitability of a given fund for a client company's
retirement plan. More of a score card process than scheduling - at least
that's how I interpret what you do.

Employees currently update progress through an Excel "Completion Sheet" in
which they date and initial each step as it progresses. We obtained MS
Project in the hopes that our Investment Analysis Manager could gain a better
understanding of the length ocurring at each step and for the controlling
features that it offers.

Reading your further description is doesn't appear that you are
scheduling anything, rather, you need some type of data analysis to
provide metrics on your evaluation process. For that type of data
analysis, Excel is probably a better application. Project really shines
when it comes to interactive scheduling a set of varied interrelated
tasks to achieve primarily one end result given limited resources.
Project may work in your instance, but I'd need to know and understand
what "controlling features" you hope to use.

John
Project MVP

 

[Hisham]

You can automate the process as John suggested by using VB coding. The
complexity here depends on the level of sophistication you require for the
process automation. However, the easiest way for this is to record a Macro
then recalling this macro as needed.

Hope this helps!