none
external dependencies - the manual way.... RRS feed

  • Question

  • Hello youve all been so good i want to try to set you a real task

    ok i have 73 plans manged by 40 odd managers, trust me there odd...

    we use a couple of text feilds to mange external dependencies, ie instead of electronically linking them we use meetings and comms to align the dates and such.

    but the bosses now want to see what the critical path is through the programme...

    so i have put all the plans into one consolidated plan, and sorted by text1 ascending (the dep code), and the text4 descending (dep type), so that where text1 is the same ie the dependency is in two plans (a give and a get), and the O/P is above the I/P, I can quickly select both and link.... great but... it took my three week to cover all 46,000 lines, and now weve just had a replan and i have to do it all again,

    i have tried to record macro but it only looks at cells and some times i may have 1 out to 3 ins, so i need to have some kind of look match then check to be different then link....

    i started by trying it in excel but didnt appreciate that when you link two plans the id increments by one to allow for the external task.. so i completly messed it up.

    so what do you think... can it be done using macros, or do i need to resign myself to going bald and taking up drink... 

    best regards

     


    Phil Monckton The Happy Planner
    Wednesday, June 22, 2011 8:12 PM

Answers

  • Absolutely it can be done in macros. It would take time to run, but would work. In your case I would simplify things using macros as follows:

    1. Have a SQL Server or MS Access database made available on the network. 
    2. Have a VBA macro to write all Milestone data from each project to this database. This includes a unique Project number, Task Unique ID and date.
    3. In each project have a new summary task at the top of the schedule called constraints. Add milestones as sub-tasks, one for each external dependency.
    4. In text1 for a constraint task, you now might have "1:50" to represent project 1, task unique id 50. A macro can display all milestones in the database for a selected project for you to select an external milestone and write the Text1 data.
    5. Another macro reads, for each task with a value in Text1, the date from the above database and then resets the date constraint for the constraint milestone. All tasks driven by this dependency should be linked to it.

    Now each project has an accurate link to other projects, updated by running the macro. I would then have yet another macro to automatically create your consolidated project. Insert each project with no link and all resources are automatically consolidated so now you have resource usage across all project as well as a critical path across all projects.

    This method works well for me. Another solution is Project Server because its Depenedency feature already does this (share links via a database).


    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management
    • Marked as answer by Jim Corbin Thursday, June 23, 2011 2:46 PM
    Wednesday, June 22, 2011 8:34 PM
    Moderator

All replies

  • Absolutely it can be done in macros. It would take time to run, but would work. In your case I would simplify things using macros as follows:

    1. Have a SQL Server or MS Access database made available on the network. 
    2. Have a VBA macro to write all Milestone data from each project to this database. This includes a unique Project number, Task Unique ID and date.
    3. In each project have a new summary task at the top of the schedule called constraints. Add milestones as sub-tasks, one for each external dependency.
    4. In text1 for a constraint task, you now might have "1:50" to represent project 1, task unique id 50. A macro can display all milestones in the database for a selected project for you to select an external milestone and write the Text1 data.
    5. Another macro reads, for each task with a value in Text1, the date from the above database and then resets the date constraint for the constraint milestone. All tasks driven by this dependency should be linked to it.

    Now each project has an accurate link to other projects, updated by running the macro. I would then have yet another macro to automatically create your consolidated project. Insert each project with no link and all resources are automatically consolidated so now you have resource usage across all project as well as a critical path across all projects.

    This method works well for me. Another solution is Project Server because its Depenedency feature already does this (share links via a database).


    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management
    • Marked as answer by Jim Corbin Thursday, June 23, 2011 2:46 PM
    Wednesday, June 22, 2011 8:34 PM
    Moderator
  • Hi Rod,

    Nice to finally interact with you,

    ok have database and have already run the extract which extracts the 46000 lines from the plans, each line has a unique key (Merge of category and unique id) the dep codes  a set of references and each one has its own data set, start finish etc

    so in data base i have lots of different reports etc but one of them is show me where text 1 contains data, i then then sort this to show me the "pairs" of like dependencies, i can run reports which show date differences and where orphans occur. so i think we''re pretty much there with what you suggest.

    what i cant see from your answer is how i can Physically link the two activities together, to form an "external link in the consolidation thereby createing a "grey task" in the the source plans, it is this that is key for me as my process is to use the "offline" consolidation as an impact assessment tool where I will use this to perform "what if" analysis and "realtime" change analysis in front of senior management.

    So the macro i would need is with a view showing only where text1 not blank sorted ascending, 1. to find match in text1, 2. find opposite in text4 (I/P v O/P) and then link them, loop for next. and go through the consoldated plan line by line, i can do it manually so i know it works, but need to speed it up, once its done i will import live data (start duration fnish %) into the Offline plan using the file open, excel, merge on unique key to update progress, then analyse for slippage.

    but i cant get the syntax right for match cells(text1) and then not match cells(text4) link.

    any help would be really appreciated, i have tried to use the code you have on pages 215 - 221 of your book  but i'm new to this kind of language so its a bit daunting.

    regards

    Phil


    Phil Monckton The Happy Planner
    Thursday, June 23, 2011 6:29 PM