MS Project Excel Import Problem

Question

Hello,

I am trying to Import an excel file which includes the actual work and work columns but I am facing a problem. When I import the actual work data of a continuing task, the Project distributes the actual work hours to the upcoming days which are in future. For example my actual work data date is 15/Feb but MS distributes the actual work hours to March and April also since the estimated finish date of the task is May. I think MS makes this because it doesnt know the data date of the actual work. Is there a way to Show the data date in import file? How can i solve this issue?

Thanks

Onur

Answer 1

Onur,

Sorry, I should have been more explicit. Most of Microsoft's applications include Visual Basic for Applications (VBA). In my opinion it is one of the most useful features as it allows users to customize how the application works and provides a robust path for integrating applications. For example, a VBA macro can take Project data, revise it as necessary and export the result to Excel to create custom reports. Conversely, a macro can transfer data from Excel to Project in whatever form the user needs.

Import/Export maps and Visual Reports does provide for some Project/Excel interaction but for the ultimate customization, neither can match the capability or flexibility of a macro.

Learning VBA does require some effort but Rod Gill has an excellent book on subject and you can read more about it at: http://www.project-systems.co.nz.

I've written countless macros exporting Project data to Excel and a few from Excel to Project. Going from Excel to Project is more challenging since Excel can have data arranged in virtually any form (i.e. rows & columns) whereas Project requires a very specific form (i.e. tasks, resources, assignments).

Hope this helps

John

Answer 2

John,

Thanks a lot for quick answers in deed. You are very helpful.

Yess, the VBA is what i need exactly. Can you recommend me some manual or site to learn how to create VBA excel? Cause I have no idea what VBA is :)

Thans very much

Regards,

Onur

Answer 3

Onur,

There isn't really enough information to give an exact answer. For example, what is the Project calendar? What is the setting for hours per day? What is the Base Calendar for the resource Turgay Durace? What is the assignment level of that resource (e.g. 100% or something less)?

The total work for task ID 217 is 250 hours. You are importing 161.5 actual hours which gives 64.4% work complete. The duration of the task is approximately 135 days depending on your calendar. So 64.4% of 135 days is 87.2 days. If the task start is Dec 20, 2017, those 87.2 days will spread to the middle of March 2018.

You say the "data date" is 15/02/18 but what is that? The status date? That may be the status date but the status date has absolutely no relevance when using an import map. The imported data is treated as a single value and will be spread linearly over the time span as noted above. If you need/want to control the import to a specific time span then you cannot use an import map, you will need to import the Excel data using VBA.

Hope this helps.

John

Answer 4

Hello John,

Thx for answering. I am using MS 2013 Project server. I guess fully updated all. I am merging with Task ID key. I am importing several rows. Most of the rows are imported correctly but some of them are failing. You can find some screenshots below;

Excel file; (see the last row)

Project file after import;

As you can see even if the data date is 15/02/18, the actual work is distributed to march. How can i avoid this?

Regards,

Onur

Answer 5

Onur,

First of all, what version of Project are you using? Is it fully updated? What version of Office (i.e. Excel) are you using? Is Office fully updated.

How exactly are you importing? Are you importing as a new project, appending to an existing project, or merging the data? If merging, what is the merge key?

Are you only importing work and actual work or are you importing other fields as well? If so, what other fields?

Here is a simple Project file similar to what you describe and an Excel file similar to what you describe.

Now I import (merge) the Excel data to Project using the below import map.

And the end result is the following.

As you can see the import loads the Excel data into Project as you would expect.

John