none
Using MS Project map to update Enterprise Resource Pool from Excel RRS feed

  • Question

  • Hi,

    Trying to make updates to my ERP (Project Online) through MS Project Pro and coming very unstuck. 

    I have an Excel sheet which holds a couple of thousand resources. It doesn't cover the entire ERP because we try and only maintain one department but there are a few exceptions. There's no real logic to the exceptions so for the purpose of this exercise I've ignored them. I'm wondering if this is causing issues though...

    I confirm my resources already in exist in Project so there should be no 'new' resources on the excel list. I open my entire resource pool into MS Project (so that includes resources that aren't in the excel sheet), then I open my excel sheet and use the MAP to merge into active project. Fields are mapped and key is set. I've tried with a new map and a saved map - same result.

    Various resources are getting duplicated with wrong info in the rest of the fields. It's not all of them though - it seems random. One particular person is having his current resource semi-updated, and then a duplicate record is created with his name but ID number and other fields are wrong. It actually seems to be merging him with another resource as on my last test I used myself and noticed I had disappeared but he (and others) then appeared twice.

    Staff ID number is my key and I'm looking at my excel sheet and my resource pool and 100% certain there are no duplications there that are feeding into this.

    I'm at a loss now. I thought using the map would save me lots of manual updating but I don't understand how to get it working!!

    Thursday, April 7, 2016 12:48 AM

Answers

  • I actually forgot I asked this question but just spotted the reply 'unread' so came back to the thread.

    It turns out the issue I was having with using the import wizard to update my resources was caused by a bug which gave incorrect updates when merging with a custom field as the merge key.

    Microsoft have now fixed this and I'm waiting to get my version of Project updated so I can test it.

    Thursday, July 28, 2016 10:53 PM

All replies

  • I felt uncomfortable reading that you were merging into the Enterprise Resource pool. Given that the Enterprise pool affects all projects, I would stop using Excel and only use Project pro to update the Enterprise pool. There should only be one set of master data and Project online has to be one, so make it the only one. Its a good idea to have resource managers set up so they can edit their own resource's data and you can enter a department name for each resource.

    Make sure your version of Project has the latest updates.

    If you really have to use Excel as a duplicate master then I would use VBA to do the Project update so the VBA code can mark all rows where data is changed so you can confirm the change is accurate and valid. An Excel merge doesn't show this so it will be too easy for invalid or inaccurate changes to happen.

    Good luck!


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Saturday, April 9, 2016 9:20 PM
    Moderator
  • I actually forgot I asked this question but just spotted the reply 'unread' so came back to the thread.

    It turns out the issue I was having with using the import wizard to update my resources was caused by a bug which gave incorrect updates when merging with a custom field as the merge key.

    Microsoft have now fixed this and I'm waiting to get my version of Project updated so I can test it.

    Thursday, July 28, 2016 10:53 PM