none
Replace resource name in file with name from pool RRS feed

  • Question

  • I am running MSOP 2003 Standard. I have a company resource pool that contains all the resources
    with their coorsponding employee number in the resource text 3 field.  I receive some exports from our corporate DB that
    has the employee number listed as the resource in the .mpp file instead of the actual resource name.  Is there an
    easy way to replace the employee number with the employee name.  I am currently manually copying and pasting the
    resource name from the pool over to the individual files.  This is very time consunming.  I would like to be able to do something like a look up (Like in excel) so
    that if the individual file has 201201as the resource name the look up would go to the resource pool master file
    and find 201201 in the resource text 3 field and replace 201201 in the individual file with that resource name...Joe Brown and
    also populate the text 3 field with 201201.

    Can this be done?

    Tuesday, February 15, 2011 4:51 PM

Answers

  • Hi Blue Angel,

    If I write a VBA proicedure would you install it?

    You can read instructions about installing a macro at masamiki.com, site owned by fellow MVP Jack Dahlgren.

    In the following code I assume the active project having the numbers as "Names" and the reference project is called "MyPool"

    sub SwitchNames

    Dim Reso as resource

    Dim Poule as project

    Dim PoolRes as resource

    Dim Here as project

    set here=activeproject

    set poule=projects("MyPool")

    for each reso in here

    if len(reso.text3)=0 then

    reso.text3=reso.name

    for each poolres in poule.resources

    if poolres.text3=reso.text3 then

    reso.name=poolres.name

    exit for

    end if

    next poolres

    end if

    next reso

    end sub

     

    Greetings,

     

     

     

     

    Tuesday, February 15, 2011 5:37 PM
    Moderator

All replies

  • Hi Blue Angel,

    If I write a VBA proicedure would you install it?

    You can read instructions about installing a macro at masamiki.com, site owned by fellow MVP Jack Dahlgren.

    In the following code I assume the active project having the numbers as "Names" and the reference project is called "MyPool"

    sub SwitchNames

    Dim Reso as resource

    Dim Poule as project

    Dim PoolRes as resource

    Dim Here as project

    set here=activeproject

    set poule=projects("MyPool")

    for each reso in here

    if len(reso.text3)=0 then

    reso.text3=reso.name

    for each poolres in poule.resources

    if poolres.text3=reso.text3 then

    reso.name=poolres.name

    exit for

    end if

    next poolres

    end if

    next reso

    end sub

     

    Greetings,

     

     

     

     

    Tuesday, February 15, 2011 5:37 PM
    Moderator
  • Hello,

    I did try the macro you supplied and I get a run-time error '438'.  Object doesn't support this property or method.  When I hit the debug button it highlights the following line in yellow:

    for each reso in here

    I had both files open.  Did I do something wrong?

    Thanks for your help.

     

    Tuesday, February 15, 2011 6:19 PM
  • Sorry. That should read for each reso in here.resources

    Tuesday, February 15, 2011 6:56 PM
    Moderator
  • I have tried this on a small project and it works perfectly.  Thanks so much for the help.

    Tuesday, February 15, 2011 7:32 PM
  • Hi again Jan,

    I tried this on the same small project with only 3 tasks and pool with only 3 resources.  When I removed one resource the macro didn't seem to work, I didn't get an error but no data was transferred.  Since I will be using a pool and not all resources will be utilitzed on every project will this work?

    Tuesday, February 15, 2011 7:50 PM
  • Hi,

    When you read the code you will see that it only works for resources where text3 is empty. I suspect you tried to re-run it a second time on the same file.

    Greetings,

    Tuesday, February 15, 2011 8:14 PM
    Moderator
  • Hi,

    I did go back and use a clean project plan and pool and this is working.  Thanks for the help.  This will save a lot of time.

    Tuesday, February 15, 2011 8:49 PM