none
Project Pro 2010: Issue with Importing Actual Hours by Resource When a Task Has More than 1 Resource Assigned RRS feed

  • Question

  • Using Microsoft Project Professional 2010, Version 14.0.6129.5000 (32-bit)

    I am attempting to import/merge the actual work hours worked into Project Professional 2010 (from Excel).  Unfortunately, the actual hours do not import for the resources in the case where more than 1 resource has been assigned to a task.  Therefore, either I have structured the import map incorrectly, selected the incorrect merge key, or Project 2010 does not allow the actual hours to be updated for each resource (via the import/merge of external data) when multiple resources are assigned to a task.  

    1) What specific task, resource, and/or assignment-related data fields are required in the import map to insure the actual hours are merged with the correct assignment? (I assume an assignment is defined by a combination of the project, Task GUID, and Resource GUID data fields?)

    2) When performing the aforementioned merge, which data field needs to be selected as the “Merge Key?” (Evidently, MS Project does not allow you to select include more than 1 data field in the Merge Key.)

    3) Can actual hours be imported/merged into multiple subprojects at the same time (as in a master/consolidated project) or do the actuals need to be merged with each individual subproject?

    Friday, May 31, 2013 10:27 PM

Answers

  • kep33,

    No I don't think you structured the import map incorrectly. The import feature only work with one resource per task and "merge" may not be quite what you assume.

    The import process basically reads each row of the Excel worksheet and then transfers that date to the mapped fields in Project. As far as I can tell there is simply no way to read the Excel data such that it will insert more than one piece of data (e.g. actual work) for any given task assignment. Further, subsequent imports using merge will NOT add the new values to the existing, rather, the new values will overwrite the existing, so if your actual work data is cumulative, then it will work fine. And if you only want to import periodic data (i.e. not cumulative), "append" won't help because that only adds new (i.e. non-existing) data to the Project file.

    On your last question, no you cannot import data into multiple subprojects at the same time - at least using an import map. For reference, a dynamic master does NOT actually contain the data of each subproject file, the master only contains pointers to each subproject file. Attempting to import subproject data via a master will therefore fail.

    The bottom line is that an import map probably won't do what you want. In my opinion the most flexibility in importing/exporting data from Project is with VBA.

    Hope this helps.

    John

    • Marked as answer by kep33 Wednesday, June 5, 2013 2:38 PM
    Saturday, June 1, 2013 3:49 PM
  • kep33,

    You're welcome. Yes, your reiteration of what I said is accurate.

    I know it can be done with VBA but I can't speak for Project Server since I don't use it. There is a separate Project Server forum at, http://social.technet.microsoft.com/Forums/en-US/projectserver2010general. You might consider posting your question there.

    John

    • Marked as answer by kep33 Wednesday, June 5, 2013 2:38 PM
    Wednesday, June 5, 2013 2:32 PM

All replies

  • kep33,

    No I don't think you structured the import map incorrectly. The import feature only work with one resource per task and "merge" may not be quite what you assume.

    The import process basically reads each row of the Excel worksheet and then transfers that date to the mapped fields in Project. As far as I can tell there is simply no way to read the Excel data such that it will insert more than one piece of data (e.g. actual work) for any given task assignment. Further, subsequent imports using merge will NOT add the new values to the existing, rather, the new values will overwrite the existing, so if your actual work data is cumulative, then it will work fine. And if you only want to import periodic data (i.e. not cumulative), "append" won't help because that only adds new (i.e. non-existing) data to the Project file.

    On your last question, no you cannot import data into multiple subprojects at the same time - at least using an import map. For reference, a dynamic master does NOT actually contain the data of each subproject file, the master only contains pointers to each subproject file. Attempting to import subproject data via a master will therefore fail.

    The bottom line is that an import map probably won't do what you want. In my opinion the most flexibility in importing/exporting data from Project is with VBA.

    Hope this helps.

    John

    • Marked as answer by kep33 Wednesday, June 5, 2013 2:38 PM
    Saturday, June 1, 2013 3:49 PM
  • Thank you for the prompt response, John.  To confirm my understanding of the first part of your response, Project Pro 2010 does not provide the functionality to update the actuals for multiple resources when more than 1 resource is assigned to a task (via an import routine).  Therefore, I cannot update my actual hours worked via an import routine for multiple resources (on tasks with more than 1 assigned resource) regardless of whether I use an append or merge configuration and/or how I structure the import map.

    In addition to using VBA as a potential solution, it sounds like the MS Project Server product provides the capability to update actuals for multiple resources assigned to one task (via the timesheet feature)?

    Wednesday, June 5, 2013 2:24 PM
  • kep33,

    You're welcome. Yes, your reiteration of what I said is accurate.

    I know it can be done with VBA but I can't speak for Project Server since I don't use it. There is a separate Project Server forum at, http://social.technet.microsoft.com/Forums/en-US/projectserver2010general. You might consider posting your question there.

    John

    • Marked as answer by kep33 Wednesday, June 5, 2013 2:38 PM
    Wednesday, June 5, 2013 2:32 PM
  • Do you know if this functionality has been added to Project 2013? (The functionality to update the actuals for multiple resources via the import routine when more than 1 resource is assigned to a task.)
    Wednesday, June 5, 2013 2:48 PM
  • kep33,

    Sorry, I don't have Project 2013. It might be listed in the typical table of new stuff but I wouldn't bet on it. I'll ask one of the MVPs to jump in.

    John

    Wednesday, June 5, 2013 3:52 PM
  • No, I don't believe this has changed any in Project 2013.  However, (and I haven't tested) each assignment (pairing of a resource to a task) has a unique assignment ID.

    John - is it not possible to update actual and remaining work by mapping the unique assignment ID?

    Julie

    Wednesday, June 5, 2013 8:42 PM
    Moderator
  • Okay - just answered my own question - nope.  Importing Actual and Remaining work at the assignment level does not work.  I believe VBA will be the best method and John is certainly adept at that part.

    • Proposed as answer by John - Project Wednesday, June 5, 2013 9:06 PM
    Wednesday, June 5, 2013 8:56 PM
    Moderator
  • Julie,

    Thanks for checking in.

    John

    Wednesday, June 5, 2013 9:06 PM
  • Thank you for the response, Julie.  Based on my initial test, your solution appears to work correctly (in MS Project Pro 2010). 

    In summary, I created an assignment-based import map with the following fields:  unique ID, actual start, actual work, and remaining work.  Then, I selected unique ID as the merge key. 

    As the next step, I will do more testing (particularly to see if I need to include incremental or accumulative values in future update cycles) but it appears to be working!

    Wednesday, June 5, 2013 9:33 PM
  • Great.  I didn't try the Actual Start - that could be the key.
    Wednesday, June 5, 2013 9:42 PM
    Moderator
  • kep33,

    I tried the method you describe without success. Could you share more details on exactly what you did. Maybe I can learn something.

    John

    Thursday, June 6, 2013 4:57 PM