How to iterate all rows and columns through a loop in MS project

2
votes

Am working on Ms project file and currently am having trouble in below two areas

1) Now am trying to iterate all rows and columns through a loop for ex:-

Task      ResourcesName      Start 

products    xxxx              10/3/2017

projects    yyyy              11/04/2017

I can get first column value(Task) name using below code

dim var = ActiveProject.Tasks(x + 1).Name

but I need to traverse to resources name and start column and I need to store all the values in variables

2) second question is I observed that in my MPP file that for some columns names are like

  1. Task
  2. ResourcesName
  3. Start etc

But, If I try to store the column values by using below code

For each oSubTasks in Activeproject.Task    
    dim var1 = oSubTasks.GetField(FieldNameToFieldConstant("Task"))    
next

The values are like Text1,Text2 etc

My question is that how can i get the actual displayed values from MPP file columns

2
vbams-project

2 Answers

2
votes

It appears that perhaps you are using vb.net rather than vba since you state this works:

dim var = ActiveProject.Tasks(x + 1).Name

If that is the case, then make sure you use Early Binding so you have IntelliSense (more info) when you are writing your code. That way you declare oSubTask As MSProject.Task and when you type oSubTask followed by a period you get a drop-down list of the properties and methods.

Secondly, you state that you want to get the "actual displayed values". For string fields such as task Name and Resource Names, there is no difference between what is stored and what is displayed. However, for date fields this is not the case. To get a task's Start date as a date value, use: oSubTask.Start. But to get the string representation of the date as displayed, use: oSubTask.GetField(pjTaskStart).

Here is a VBA sample:

Dim oSubTask As Task
Dim taskName As String
Dim taskRes As String
Dim taskStart As Date
Dim formattedStart As String

For Each oSubTask In ActiveProject.Tasks
    taskName = oSubTask.Name
    taskRes = oSubTask.ResourceNames
    taskStart = oSubTask.Start
    ' to see how the date is displayed, use GetField
    formattedStart = oSubTask.GetField(pjTaskStart)

    ' do something with these values
Next
1
votes

This is an example how to loop through your Ms-Project tasks, and read the Task.Name and Task.Start from them (into an array).

Dim oSubTasks As Task
Dim oName(1000) As Variant
Dim oStart(1000) As Date
Dim i As Long

i = 1
For Each oSubTasks In Activeproject.Tasks
    oName(i) = oSubTasks.Name
    oStart(i) = oSubTasks.Start
    i = i + 1
Next oSubTasks