none
Macro for automatically enterering the deadline RRS feed

  • Question

  • Hi together,


    I would like to build a macro that take the deadline date from another schedule on the Project Server 2010.

    MasterSchedule:

    The macro shall

    1) If: Text1 column: "Need date 1" and Text2 column: "SubProjectA" then

    2) Open "SubProjectA" from Project Server and search in column Text1 for "Need date 1"

    3) Copy the end date of that Task

    4) Insert this date into the MasterSchedule at the place where he hes found the "Need date 1" into the Deadline colunm

    5) Close SubProjectB without saving

    I have here and there an idea how it could work but my VBA knowledge is to bad for this kind of macros.

    It would be great if someone has at least a rough idea how it could be done.

    Thanks in advance!

    Best regards

    Tobias

    Wednesday, February 15, 2017 9:11 AM

All replies

  • Tobias,

    I'm not quite clear on where the finish date that is found needs to go. Step 2 says to open subprojectA and find the task with the date. Step 4 says to insert that date into the Deadline field of the task where it was found. Okay. Then Step 5 says to close subprojectB. So what does subprojectB have to do with the date that was found in step 2? Did you mean to say that once the date is found in subprojectA, you then want to insert it into the Deadline field of subprojectB? And if so, then where in subprojectB?

    Also, step 1 mentions using Text2 for the subproject name. Why? There is a built-in Subproject File field that has this information.

    Just for reference, if you are running the macro at master level, which it sounds like you are, then you do not need to specifically open any of the subprojects. They are already open, whether in the foreground (i.e. visually expanded in the master) or background (i.e. shown as a summary line only in the master) and you can read and write data to any of the files in the structure. However if the search function (i.e. Find Method) is used to find the desired task/date, then you would need to have the subproject open in the foreground. I wouldn't necessarily use the Find Method to locate the desired date.

    Glad to help but I need to better understand what you are trying to do.

    John

    Wednesday, February 15, 2017 4:58 PM
  • I would enter the name of milestone in the other project in Text1 and as part of the process make sure the name is unique across all projects. Or enter the GUID, but that can change if tasks are cut/paste or copied etc.

    Then I would use ADODB to read the deadline date directly from the Reporting db. Much faster than opening each project. This is relatively straight forward code.


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

    Wednesday, February 15, 2017 10:12 PM
    Moderator
  • Hi John, Hi Rod,

    thanks for your firs feedback, maybe I was not that clear, I hope the attached picture can help.

    First of all: I want (as the Leader of a Software Schedule) the need dates of some milestones from the System Engineering Schedule.

    As a reference (I was thinking) maybe column Text1 should be ok - if not just let me know.

    In the first picture you'll find the Software Schedule with no Deadline dates at all.

    What I want is a deadline date (automatically) from the System Engineering Schedule - second picture.

    So, the macro shall insert in the SW Schedule the deadline dates - this dates comes from the System Engineering Schedule from the column "finish".

    Because of there is no direct linking between these schedules, I would think that the macro has to open the System Engineering Schedule and find out the matches of Text1.

    Or?

    Anything not clear?

    Thanks

    Best regards,

    Tobias

    Thursday, February 16, 2017 6:40 AM
  • Tobias,

    So if I understand correctly the first screen shot, (software schedule), is showing two need dates which I'll assume are for two milestones in the software schedule (SW - SRR V1 and SRDB V1 - SW SRR). The second screen shot (systems engineering schedule) shows two tasks identified with a "Need date 1". If you want to identify the "need" lines in the software schedule and then match those up with the "need" lines in the systems engineering schedule in order to read the finish date of the "need" line in the systems engineering schedule such that it can be written into the Deadline field of the matching line in the software schedule, then which "Need date 1" in the systems engineering schedule is the one to read?

    Also, you say there is no link between the schedules so I'll assume that means you have no cross-projects links. That's good because those links can be troublesome (corruption prone).

    You mentioned in your initial post that you have a master schedule. Are both the software and systems engineering schedules inserted into that master? If so, then what I said in my previous reply about the need to fully open either file still holds but if the software and systems engineering schedules are NOT both inserted in the master then yes, the files would need to be "opened" to get the desired data.

    So, still not quite clear. Remember, you work with your schedules all the time so in your mind it may be very clear what you want/need but to those of us who are not familiar with what you have, we need to understand before we can provide meaningful help.

    John

    Thursday, February 16, 2017 2:50 PM
  • John,

    now we comes more and more to what I want:

    1) In picture 2 the second "Need Date 1" in Text1 is wrong, it shall be "Need Date 2"!

    2) Yes we have a Master Schedule but this is not for interesting here because it's also not linked!

    I hope we are on a good way to get the full picture!

    Best regards,

    Tobias

    Thursday, February 16, 2017 2:59 PM
  • Tobias,

    You're second statement about your master schedule is still not quite clear. You say it (master schedule) is not linked. Does that mean it is a static master instead of a dynamic master? In other words, did you create your master by unchecking the "link to project" option in the lower right corner of the Insert Project window?

    Or by "not linked", do you mean that your have a dynamic master BUT there are no links between tasks in the subprojects (i.e. iter-project links)?

    John

    Thursday, February 16, 2017 4:24 PM
  • What I said before is exactly what you need. Works very fast. No need to open the sub-project as all task data is duplicated in the reporting db for you to read from.

    I have done similar things a number of times before, but using unlinked master files and solutions where project data has been copied to SQL Server for reporting purposes or by Project Server.


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

    Friday, February 17, 2017 4:15 AM
    Moderator
  • Hi guys,

    sorry for the late reply but I was very busy with other things.

    In the meanwhile I tried also here and there with a macro recorder and with my roughly knowledge and I got what I need more or less with this macro:

    Sub GetNeedDates()
    ' Macro GetNeedDates
    ' Macro Recorded 17.02.17 by T.Sturm.
    
    Dim t As Task
    
        FileOpenEx Name:="<>\AIT_Schedule", ReadOnly:=True
        
    
    
    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            If t.Flag20 Then
                On Error Resume Next
                ActiveProject.Tasks.UniqueID(t.Text20).Date1 = t.Finish
            End If
            
    WindowActivate WindowName:="<>\Software_Schedule"
        
            If t.Flag19 Then
                On Error Resume Next
                ActiveProject.Tasks.UniqueID(t.Text19).Deadline = t.Finish
            End If
            
        End If
    Next
     
    
    End Sub
    

    In this case the macro shall:

    1) open the AIT Schedule in read mode

    2) search if in Flag 19 is a yes

    3) if yes then copy the finish date

    4) search for the unique ID (Text19 from the AIT schedule) in the SW schedule

    5) copy the finish date from AIT schedule to Sofware schedule as deadline

    Until this point it seams to work.

    But what I would like to some things in addition:

    1) After opening the AIT schedule I get the message "Links between projects" - can I open the schedule also without the message or at least press "Close" in the macro?

    2) After all I would like to close the AIT Schedule without saving.

    Best regards

    Tobias

    Tuesday, March 7, 2017 7:46 AM
  • Tobias,

    You never did answer my question about the types of links you have but it appears you have cross-project links although I still don't know what kind of master you have.

    Regardless, to eliminate the "links between projects" message, use the LinksBetweenProjects Method.

    As far as closing, use the FileCloseEx pjdonotsave Method.

    Hope this helps.

    Just curious, why did you insert the "On Error Resume Next" in your two loops?

    John

    Tuesday, March 7, 2017 4:19 PM