Find all linked projects in ressource pool file RRS feed

  • Question

  • Hi,

    I would like to find all linked projects that are present in a resource pool.

    I've found Jon's post see (*) which doesn't do the trick. I have one linked plan and one resource pool (no master). I only open the pool with option 1, run the code and ActiveProject.Subprojects.Count sees Zero ;-). Any other ideas? I mean project displays me a nice list that certainly is stored somewhere?

    Best, Stefan.


    this website /Forums/en-US/cfcf4663-27e0-4731-92aa-e1928a6ebd18/vba-to-unlink-a-project-from-its-resource-pool?forum=project2010custprog

    Sunday, December 8, 2019 11:45 AM

All replies

  • swa72,

    For reference it's John with an "h". The reason that macro doesn't help is that is it for a different scenario, a master with linked subprojects.

    I don't quite understand what you mean by "one linked plan and one resource pool". That seems to say you have one sharer file and one resource pool, yet it sounds like you want to see "all" projects linked to the pool. What am I missing?

    If you simply want to see what sharer files are linked to a resource pool, display the Project field in the Resource Usage view of the pool file, as shown in the screen shot below.


    Sunday, December 8, 2019 5:19 PM
  • Hi John,

    sorry for missing the "h" ;-)

    Your assumption was right, I had a sharer and a pool as a demo to try our your macro - I simply 
    overlooked that it was designed to work with a master project.

    My goal is to have a number of project managers use the same pool (not to address overallocation or somesuch, but
    to simply have everyone use the same cost rates, or other meta information pertaining to the resource).

    Keeping in mind your recommendation ...

    "Linked structures, (master/subproject and pool/sharer), in Project are prone to corruption. These are the rules to minimize 
    (note I said minimize, not eliminate) the probability of corruption. Never rename, move, overwrite, or save off to another 
    location any of the files in the structure. Ideally, all files should reside in a single folder on a local hard drive and 
    never be operated over a network."

    In order to do that, I need a VBA way to unlink the pool completely and later re-establish links with a given set of projects. 

    Here is our intended setup.

    A) One SharePoint directory with
    - Serves as a hub to PMO and project managers
    - Masterplan, n Plans and Pool linked together
    - Pool read only
    - PMO copies pool from local drive to sharepoint
    - remove all links from pool and projects
    - link pool to all projects

    B) Local harddrive PMO with
    - Masterplan, n Plans and Pool linked together (locally)
    - Pool read/write, adds new resources, resolves overallocation, updates resource meta information (cost, ...)
    - copy pool from sharepoint to local drive
    - remove all links from pool and projects
    - add or change resouces
    - link pool to all local projects
    - then copy to SharePoint

    C) Local harddrive of project manager with
    - One or more of the n plans and pool linked together (locally)
    - Pool read only, no adding of resources
    - will only send plans to SharePoint (not receive one)
    - will only receive a pool (not send one)
    - removed all links
    - links pool to all local projects

    File names will not be changed while copying, no operation over a network.

    I hope that the general setup minimizes corruption. 

    One could break and re-establish the links manually, but that is a pita. So I hope I can write some VBA code that does the job.

    I was about to post an image of the process but learned that my account is not verified yet, grrr. Will update this post, once account is verified.


    Sunday, December 8, 2019 8:38 PM
  • Stefan,

    I think you will strike out with the process you outline. For example, group A), item 2 seems to say you want to have a master, subprojects and resource pool all linked together on a SharePoint site. I don't use SharePoint but my understanding is that linked structures don't work on SharePoint.

    My other concern is your process sounds like it will have multiple link/un-link operations and that will require an extreme amount of file discipline to insure structures don't become corrupt. It will be hard enough with a single person managing the structure but with multiple users, it gets really dicey. Have you considered moving to Project Server or Project Online which are specifically structured for enterprise project management?

    Nonetheless, I do have a macro that un-links resource pool/sharer files. It does not re-link them but at least you can see if it is helpful.


    Sub UnlinkSharerFil()
    'written by John-Project Date: unknown
    Dim Shar() As String
    Dim sp As Subproject
    Dim NumShar As Integer, i As Integer
    Dim PoolNam As String
    'open all sharer files into temporary master, count them and
    ' set up array to hold sharer file path names
    ResourceSharingPoolAction Action:=pjOpenAllSharers
    NumShar = ActiveProject.Subprojects.count
    ReDim Shar(NumShar)
    i = 0
    'fill array with sharer file path names
    For Each sp In ActiveProject.Subprojects
        Shar(i) = sp.SourceProject.FullName
        i = i + 1
    Next sp
    'close temporary master and get name of pool file
    FileCloseEx Save:=pjDoNotSave
    PoolNam = ActiveProject.Name

    'open sharer files
    For i = 0 To NumShar - 1
        FileOpen Name:=Shar(i)
    Next i
    'activate pool file and unlink all sharer files
    For i = 0 To NumShar - 1
        ResourceSharingPoolAction Action:=pjUnlinkSharer, FileName:=Shar(i)
    Next i
    'save changes
    FileCloseAllEx Save:=pjSave
    End Sub

    Sunday, December 8, 2019 11:42 PM
  • Hi John,

    thanks for the quick reply. You're right that linked structures don't work particularly well on SharePoint. We intend to use SharePoint only as a file sharer (think of it as an online dropbox folder). People copy files to/from that folder, but never reference or link to that folder.

    And yes, we intend to move to project server in the summer of 2020 and are in desperate need of something that works until then (god knows if it will be summer ...).

    For clarification, here is an image ...

    I've tried your macro one of two ways

    - one sharer plan, one pool; open pool only with option1: NumShar = ActiveProject.Subprojects.count gives me 0.

    - one sharer plan, one pool, one master;
    master constains sharer plan, master linked to pool
    sharer plan linked to pool
    pool shows two sharers
    open pool only with option1: NumShar = ActiveProject.Subprojects.count gives me 1 and exits at
    ResourceSharingPoolAction Action:=pjUnlinkSharer, FileName:=Shar(i)

    Basically, I'm looking for a list that contains the elements in the red box :-)

    Best, Stefan

    Monday, December 9, 2019 6:45 AM
  • Stefan,

    I'm still a little confused by the information in your diagram. You show SharePoint as containing the master, N plans and the pool. Unless the master is a static master (i.e. no links), then as far as I know, it can't viably exist on SharePoint since SharePoint doesn't support linked structures.

    However, I can see a structure wherein the PMO and/or the PM downloads the pool and sharer files only to their local drive, creates a dynamic master, edits data as necessary and saves updates to all files. Then, unlinks the structure including converting the dynamic master to a static master and uploads all files back to SharePoint. The static master stored in SharePoint is for reference only.

    With regard to your tests with the macro, whenever I run tests with a resource pool I always have at least two sharer files. Regardless of how you open the pool file (option 1, 2 or 3), the macro automatically opens a new master. Unbeknownst to me, (until now), the command, ResourceSharingPoolAction Action:=pjOpenAllSharers, does no work when there is only a single sharer file, it only works with two or more sharer files. That's why you see NumShar=0. I could modify the code to work around this but practically, a resource pool with only a single sharer file seems pointless.

    As far as the ultimate "list" you are seeking, it looks like what you see by looking at the Project field in the Resource Usage view of the pool file, as I suggested before. Why doesn't that work for you?


    Monday, December 9, 2019 7:55 PM