Parse Current View's Tasks and Group Headings with VBA

[Chris]

Good Day,

I have a MS Project 2002 file that is comprised of many sub Projects. I
have views setup that include groupings. I would like to write a VBA app
that parses through all of the tasks as they are displayed in the Current
View, including the Group headings. I know I can do a:

Sub ViewTasks
Dim aTask As Task
....
SelectAll
For Each aTask In ActiveSelection.Tasks
MsgBox aTask.Name
Next
End Sub

However, this will not give me the Group Headings. For example, if I Group
By the field "Duration", the View might look like:

Duration: 1 Day
Task #1
Task #4
Task #7
Duration: 2 Days
Task #2
Task #6
Duration: 3 Days
Task #3
Task #5

where the Duration lines are the group headings. Is there a way to parse
this exactly as it appears on the screen including the Group Headings.

Thanks in advance.
Chris

 

[Rod Gill]

Hi,

I don't think you can read group headings. The best you can do is export to
Excel, then sort and subtotal there.

--
For VBA posts, please use the public.project.developer group.
For any version of Project use public.project
For any version of Project Server use public. project.server

Rod Gill
Project MVP
For Microsoft Project companion projects, best practices and Project VBA
development services
visit www.projectlearning.com/

 

[John]

Chris/Rod,
I took a quick look at the VBA help file and there are group objects,
methods and properties. I didn't dig into the detail but there surely
must be a way to get the elements of the Group heading even if the
headings have to be constructed indirectly (i.e. there is always a way).

John

 

[Rod Gill]

Probably simplest thing to do is record a macro and look at the properties
of objects recorded.

--
For VBA posts, please use the public.project.developer group.
For any version of Project use public.project
For any version of Project Server use public. project.server

Rod Gill
Project MVP
For Microsoft Project companion projects, best practices and Project VBA
development services
visit www.projectlearning.com/

 

[Chris]

Thanks for your replies.

Because I need to access the rolled up data that MSP calculates in the Group
rows, all I can think of doing is:

a) Setting the View showing the Grouped data
b) SelectAll Copy
c) Estabish an Excel session
d) Paste and work in Excel. This does grab the Group row details.

Since there are in the neighbourhood of 7000 tasks it's pretty slow doing it
this way, especially the initial copy. I would rather send the info over
task by task, as it would be much faster. But I can't find a way to grab
the group rows. I've done some recording to see how much of the Group
properties are available, and don't really find anything useful. I'll let
you know if I have any luck.

Chris

 

[John]

Chris,
First, did you study the VBA Group objects, methods and properties
thoroughly (I haven't) to know that they cannot provide the data you
need? If they don't, here is the approach I take.

Sometimes recording a macro will get a "leg up" on what code to use to
perform a particular procedure. However a recorded macro runs slow (more
noticeable for large files) because it is operating in the foreground.
That is, it selects objects and operates on them much the same way a
user does when manually performing the procedure. Foreground processing
is generally identified by "selection" type statements in the code (e.g.
SelectAll). A faster approach involves working directly with Project
objects (e.g. For each t in ActiveProject.Tasks). This type of code
operates in what I call the background, and is faster and more efficient
because it doesn't involve time consuming graphic manipulation of screen
elements.

Ok, so that's my view of the VBA world. How does that help? When the
Project (Excel or whatever) object model does not provide the access I
need to do a particular procedure, I use the object information it will
provide and then supplement it with mathematical, logical and/or text
manipulation algorithms in the code. In your case the group lines you
are trying to export have a text part and a numerical part. Ask two
questions. What makes up the text part and what makes up the numerical
part? Break those parts down to whatever level is readily available via
Project's object model. For example, for the text part, manipulation of
other readily available text may be necessary to "construct" what you
need. I find the Mid and Instr functions to be invaluable for doing
this. For the numerical part, if the values are sums of other readily
available numerical data, reconstruct the sum using mathematical
manipulation in the code. For static data (i.e. non-timescaled) there
are a plethora of Methods and Properties to extract the available data.
For timescaled data, use the TimescaleData Method.

When it comes to exporting the data to Excel, using copy and paste is
analogous to foreground processing. Rather, I use direct manipulation of
Project and Excel objects in background processing. The basic code I use
for starting Excel is:

'Open Excel for data dump
Dim BookNo As Integer
Dim s As Object
Dim c As Object
Dim Xl As Object
On Error Resume Next
'Check for existing instance of Excel; if not running, start Excel
Set Xl = GetObject(, "Excel.application")
If Err <> 0 Then
On Error GoTo 0 'clear error function
Set Xl = CreateObject("Excel.Application")
If Err <> 0 Then
MsgBox "Excel application is not available on this workstation" _
& Chr(13) & "Install Excel or check network connection",
vbCritical, "Fatal Error"
Exit Sub
End If
End If
'Clear error trap and add a new workbook for each successive run
On Error GoTo 0
Xl.Workbooks.Add
BookNam = Xl.Workbooks.Name
'Keep Excel in the background until spreadsheet is done (speeds transfer)
Xl.Visible = False
Xl.ScreenUpdating = False
Xl.DisplayAlerts = False
[then follow with code to write the Project data to Excel]

If I have to do extensive manipulation of Project data (e.g. employ
complex algorithms, sorting, formatting, etc.) I first gather the
Project data, run through the manipulation algorithms and then put the
results into matricies. Then after Excel has been opened (even though it
is not visible) I download the matricies and format the data in Excel
all in one fell swoop. When the export is complete, I make Excel visible
and end the macro.

The above process is basically how the "analyze timescaled data in
Excel" add-in operates except it doesn't use matricies (it doesn't do a
lot of data manipulation) and it first dumps the data to a temporary
text file before reading the text file into Excel.

Wow, what a way to skirt a direct answer to your problem! Hopefully
though I have given you some ideas for developing an efficient VBA for
getting what you need. One thing I have found is that Project by itself
is like eating at a fast food restaurant - you can get whatever is on
the menu. However, Project (or any other app that supports macros) with
VBA is like eating at Alice's restaurant - "You can get anything you
want at Alice's restaurant". (obviously I've been typing too long.

John

 

[Todd Scarola]

Chris,

Here is a sorting utility I use to perform a similiar task. Pass it a
collection of all the tasks you want to sort and the fieldId that you wish to
sort by (in this case Duration). The resulting collection of tasks can then
be parsed in order. Hope this helps

-Todd

Management Analytics, Inc.
http://www.ManagementAnalytics.com


'----------------------------------CODE STARTS HERE-----------------------
Public Function SortTasks(ByVal Tasks As Collection, Field As PjField) As
Collection

Dim CF As TFS_Utilities.Filter
Set CF = New TFS_Utilities.Filter

Dim Sorted As Collection
Set Sorted = New Collection

Do Until Tasks.Count = 0
Dim MinValue As Task
Set MinValue = Tasks(1)

Dim MinIndex As Integer
MinIndex = 1

Dim i As Integer
For i = 1 To Tasks.Count

If CF.GetFieldValue(Tasks(i), Field) < CF.GetFieldValue(MinValue,
Field) Then
MinIndex = i
Set MinValue = Tasks(i)
End If
Next

Sorted.Add MinValue
Tasks.Remove (MinIndex)
Loop
Set SortTasks = Sorted
End Function

'----------------------------------CODE ENDS HERE------------------