CODEWITHSUNDEEP
excelvbams-project
VikingR
VikingR

Reputation: 301

Keeping an MS Project and an MS Excel File synchronized via the MS Project import function

I have an Excel file containing tasks that is exported from an Access database (master). This Excel file then serves as an import file for MS Project. Subsequently, MS Project is then used for the actual tracking and reporting and enhances the task list with typical MS Project information (due dates, progress). The workflow goes like this:

1) Execute an initial import into MS Project.
2) Start working with the data in MS Project (and thereby enhancing it)
3) Synchronizing the Project file with a recent Excel import file at regular intervals (since tasks may be modified or set to inactive/active)

Step 3 is where the problem comes in. I tried using the Project standard functionality by importing an updated Excel (Merge the data into the active project*), matching the respective columns and defining a fusion-key (Task ID) to make sure the changes are imported properly into MS Project.

However, Project never imports the changes correctly. I've tried different variants of changes (modifying a simple active/inactive value in a column, adding different tasks inbetween, leaving blank lines in Project and then filling them with new tasks via a second import).

I'm also making sure that the matched columns have the same data format.

Does anyone have an idea on what I could be doing wrong?
Maybe I need to go a different route altogether?

Upvotes: 3

Views: 3000

Answers (1)

Rachel Hettinger
Rachel Hettinger

Reputation: 8442

The import/merge functionality is very powerful and generally works very well. The number one rule is to use an immutable field for the merge key (fusion key). The Task Unique ID field is almost always used. Since the Task ID field changes as tasks are inserted or deleted, it is a poor choice for the merge key.

Change your merge to use the Unique ID field and do a simple test where you only update a single field such as a text field (e.g. Text 1). Once you have that working, expand the import file to include more fields. If the merge fails, you'll get an error message indicating the row and column of the data that failed to merge.

Upvotes: 1

Related Questions