none
Disable MS Project automatic changing of start and finish dates of a task based on hours of the task.(Macro) RRS feed

  • Question

  • Hello Gurus,

    I am creating mpp files using data from a excel data. macro written in the excel file is used to create the mpp files.

    below i have quoted sample code for ref.

    Set pjApp = CreateObject("MSProject.Application")
    pjApp.FileNew 
    Set newproj = pjApp.ActiveProject
    ......
    ......
    newproj.Tasks.Add (task_name)
    newproj.Tasks.(mpp_row_number).type = "2" ' value 2 for fixed work type
    newproj.Tasks(mpp_row_number).Start = task_start_date
    newproj.Tasks(mpp_row_number).Finish = task_end_date
    newproj.Tasks(mpp_row_number).Hours = work_hours
    newproj.Tasks(mpp_row_number).ResourceNames = resource_name
    ....
    ....
    pjApp.FileSaveAs mpp_file_save_path & mpp_file_name & ".mpp"
    pjApp.FileClose
    pjApp.Quit

    My problem is that, I am not getting the right start and finish date for a task in the generated mpp file. it is automatically changing either start or finish date based on the hours assigned to the task. If i am not assigning the hours of the task then it is working perfect. All my task are of type "fixed work"

    my requirement is that, i have a start date, finish date and total hours for the task. resource has to work that much hours for the given start an finish date.

    example

    case 1. 

    start: 01-Jan-2014 

    end : 31-Jan-2014

    Hours : 180 hours

    Here the resource has work 180 hours from 01-Jan-2014 to 31-Jan-2014.

    case 2. 

    start: 01-Mar-2014 

    end : 31-Mar-2014

    Hours : 80 hours

    Here the resource has work 80 only hours from 01-Mar-2014 to 31-Mar-2014.

    Somebody please help to disable the automatic change of start and finish date of a task based on hours assigned. I am stuck up with the issue.

    Any support is appreciated.

    Sunday, November 2, 2014 2:36 PM

All replies

  • Syamku,

    There are a couple of things wrong with your approach. The main issue is that you are attempting to control all three parts of Project's work equation. The work equation is:

    Duration = Work/units

    You are attempting to set the duration by specifying both the start and finish dates. You also want to set the work value for one resource (unit).

    By default Project sets the duration of a new task to one day. If you enter the start date, a start-no-earlier-than constraint will be set and Project will automatically calculate the finish as the same day, but at 5:00 pm. If you then enter a different finish date, Project will set a finish-no-later-than constraint and automatically move the start date to be 8 hours, (remember the duration is still at the default 1 day), before the finish date. That's the first issue. How do you solve it? By entering a start date and a duration. Do NOT enter a finish date, Project will calculate that using the work calendar. You will still end up with a start-no-earlier-than constraint on the task so you may need to deal with that separately since constraints do not allow Project to create a dynamic schedule.

    Next, for your desired scenario (i.e. fixed task time span and fixed work), you need to set the task as a fixed duration type, not fixed work. That will allow you to have the duration you desire along with the work value you desire. Project will automatically linearly spread that work value over the entire duration, (i.e. the unit assignment level will NOT be 1, reference the above work equation). However, you can manually (or through code) adjust the work spread (i.e. contour) to, for example, have a single resource work a normal 8 hour day for however many days represent the work value anytime during the duration span. For example, let's say your duration is 3 weeks but the work content is only 40 hours. You could adjust the assignment to have the full 40 hours of work all done in the first week, the last week, or any 5 day span therein.

    That's how it works. Hope this helps.

    John

    Sunday, November 2, 2014 4:44 PM
  • Hi John,

    I appreciate your effort in correcting me. I understand my mistakes. I will change the coding and will post the feedback.

    Thank you.

    Monday, November 3, 2014 5:37 AM
  • Syamku,

    You're welcome and thanks for the feedback. If you feel I answered your question, please mark my initial response at the answer.

    John

    Monday, November 3, 2014 3:08 PM