VBA to unlink a Project from it's Resource Pool RRS feed

  • Question

  • Good afternoon!

    My problem is simple:

    I'm trying to create a "flat" project i.e. unlink all sub-projects, which I have managed successfully with a while loop.

    I'm also trying to ensure that the resultant uses it's own resources, and is not linked to the old Resource Pool any longer. (this is because we have made a new copy of the project previously to avoid over-writing).

    Any ideas?

    Here's what I've got, and a few things I've tried:

    Sub CreateStandAlone()
    'Convers the Master with Sub-Projects into a single large project document.
    'User must save as something else first, to avoid over-writing the master programme.
    Dim FName As String
    Dim Respool As String
    On Error GoTo Last
    MsgBox "You will have to Save As next!", vbCritical, "Warning!"
    While ActiveProject.Subprojects.Count > 0
    For Each subproj In ActiveProject.Subprojects
    subproj.LinkToSource = False
    Next subproj
    End Sub

    And I've tried all sorts of things such as:

    sub test ()
    FName = ActiveProject.FullName
    Respool = ActiveProject.ResourcePoolName
    Application.ResourceSharing False,FName,Respool

    Please help!!


    • Edited by PhilipDay Friday, September 30, 2016 3:21 PM even more typos!
    Friday, September 30, 2016 3:16 PM

All replies

  • PhilipDay,

    Okay, I'm a little confused. You say you have several subprojects in a dynamic master with a separate resource pool. Right? Then you say you were able to successfully un-link the subprojects from the master. Right? If that's the case, did you save all subprojects and the master? If not, you should have to insure all links were properly broken.

    If all that worked, then what exactly do you need/want to do next?

    For reference, here is a macro I wrote some time back to unlink a resource pool from all sharer files.


    Sub UnlinkSharerFil()
    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

    Friday, September 30, 2016 5:52 PM
  • Hi John,

    Sorry not to be too accurate in the description of my problem. I'll make a take 2!

    Take 2

    I have a master project with many sub-projects, linked to a Resource Pool.

    I wish to export the master project as a single MPP file to send to my client who wants to receive a single MPP detailing the whole programme.

    To achieve this, I have been performing a "Save As", on the Master. Let's call it "Exported Master".

    In the new Exported Master I have then unlinked each of the Sub-Projects, to create one single large MPP.

    Then, I have going into Resource Sharing and selected "use own resources" (A task for which I cannot find a VBA solution).

    Why do I need to "use own resources" on the "Exported Master"?

    I don't want to duplicate Resource usage in the pool when the "Exported Master" will be sent to a memory stick and never 'seen' again. My Master Programme, with the Pool and the many sub-projects must remain in-tact.

    So what's wrong with my attempted solution (What's up Doc?!)

    The Sub-Projects are "unlinked" from the master perfectly. :D

    The Master still shows that it is using the Resource Pool when I check after running this VBA :(

    You're code inspiration:

    I notice that in your solution you had to activate the Pool file, which I didn't realise was 'a thing'. So I'm going to try inserting that line of yours before I hit the pjUnlinkSharer command.

    I'll come back to you with my result


    Monday, October 3, 2016 10:55 AM
  • PhilipDay,

    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.

    Given those "rules" and based on the process you describe, you violate the "rename" and "save off" when you Save As your linked master and then unlink it. If you want to do that, first un-link the subprojects from the master and THEN Save As to a new name.


    Monday, October 3, 2016 3:07 PM
  • Hi John,

    OK that's a tough one for me. I'm nervous of "Messing around" with the back-end mechanics of the master programme though.

    I hear what you're saying. Perhaps there is an altogether better solution?

    Can it be Exported to a different file-type, and then imported safely and meaningfully?


    Tuesday, October 4, 2016 7:20 AM
  • Phil,

    Okay, my first question is, why do you need a dynamic (i.e. linked) master? The linked structure of a pool/sharer is tentative enough but adding a master/subproject structure to it multiplies the potential for corruption.

    I suggest you do this. If you have multiple projects that need a common resource pool, go ahead and create a resource pool/sharer file structure. When you need to send a composite of all files to your client, run the macro to un-link the sharer files. Then create a new static master of all the sharer files (i.e. un-check the "link to project" option in the lower right corner of the Insert Project window). You will need to re-create the static master each time you submit to the client but that process can be automated with it's own macro, or if you're game, incorporate it into the code that un-links the pool/sharer files.


    Tuesday, October 4, 2016 3:34 PM