none
Simple way to write timephased data ? Task1; 100$; January 2011 RRS feed

  • Question

  • Hello!

     

    I have a lot of experience with VBA Excel and already wrote code for MS Project 2007. But something I really find hard to understand is to work with timephased data!

     

    I am able to export timescalevalues to Excel (with Rod Gill´s great book), but now I want to write Timephased Data for certain tasks and months.

     

    What would be the easiest way to write timephased data, when month, cost and task are avaible, e.g. ‘For Task1: Write 100$ to Costs in Month January!

     

    Have a nice weekend!

     

    Christian Berck

    Friday, September 10, 2010 11:59 AM

Answers

  • Hi Christian,

    Writing timescaled data on monthly level is done using this function:

    Assignment.TimeScaleData(StartDate, EndDate, pjAssignmentTimescaledCost, pjTimescaleMonths, 1).Item(1).Value = newValue

    where StartDate is the first day of the month and EndDate is the last day of the month. Given year and month, you can define StartDate and EndDate as:

    StartDate = DateSerial(bYear, bMonth, 1)
    EndDate = DateAdd("d", -1, DateAdd("m", 1, StartDate))

    Keep in mind that you cannot update Cost timephased on task level. You will need to assign a Cost resource to the task and update timephased cost on assignment level. To find the correct task, you will need to loop through the Task collection (or do this upfront and store task id's in an array; searching in an array is faster than looping over the Task collection every time).

    I hope this helps,
    Hans


    My EPM blog: Projectopolis
    • Marked as answer by ChristianBerck Wednesday, September 15, 2010 11:06 AM
    Friday, September 10, 2010 1:14 PM
    Moderator
  • Hi,

    Thank you for the mention of that "great book"!

    Hans is correct in that you cannot update task timephased cost. If you can't do it manually in Project, then usually Project VBA can't either.

    You can update actual costs, provided the Project always calculates actual costs flag is cleared under Options, schedule tab.


    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management
    • Marked as answer by ChristianBerck Wednesday, September 15, 2010 11:06 AM
    Friday, September 10, 2010 8:24 PM
    Moderator

All replies

  • Hi Christian,

    Writing timescaled data on monthly level is done using this function:

    Assignment.TimeScaleData(StartDate, EndDate, pjAssignmentTimescaledCost, pjTimescaleMonths, 1).Item(1).Value = newValue

    where StartDate is the first day of the month and EndDate is the last day of the month. Given year and month, you can define StartDate and EndDate as:

    StartDate = DateSerial(bYear, bMonth, 1)
    EndDate = DateAdd("d", -1, DateAdd("m", 1, StartDate))

    Keep in mind that you cannot update Cost timephased on task level. You will need to assign a Cost resource to the task and update timephased cost on assignment level. To find the correct task, you will need to loop through the Task collection (or do this upfront and store task id's in an array; searching in an array is faster than looping over the Task collection every time).

    I hope this helps,
    Hans


    My EPM blog: Projectopolis
    • Marked as answer by ChristianBerck Wednesday, September 15, 2010 11:06 AM
    Friday, September 10, 2010 1:14 PM
    Moderator
  • Hi,

    Just this small precision. To locate a task, when you know the name, a loop is not necessary. The following will do:

    Set Mytask=activeproject.tasks(The Name)

    Friday, September 10, 2010 4:37 PM
    Moderator
  • Hi,

    Thank you for the mention of that "great book"!

    Hans is correct in that you cannot update task timephased cost. If you can't do it manually in Project, then usually Project VBA can't either.

    You can update actual costs, provided the Project always calculates actual costs flag is cleared under Options, schedule tab.


    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management
    • Marked as answer by ChristianBerck Wednesday, September 15, 2010 11:06 AM
    Friday, September 10, 2010 8:24 PM
    Moderator
  • Thanks for your help! Now I edit the actual cost with following code:

    with xlapp
    vCost = .Cells(zeile, 7)
    
    Set vTsv = vTsk.TimeScaleData(vDate, vDate, pjTaskTimescaledActualCost, pjTimescaleDays)
    vTsv.Add 
    
    end with
    

    Works fine! :-)

    Wednesday, September 15, 2010 11:05 AM