export of grouped data to excel

[Ken]

often export Project data to Excel with a macro that goes through
all the tasks, and subject to a certain set of rules, populates a
spreadsheet with the appropriate task information. I have a problem
now, in that I want to transfer information that is displayed when I
create a Custom Grouping. Basically, I roll up a bunch of data to
each unique string in Text1. I can't figure out how to get at the
summary data, since the rows on which is show do not have task
numbers. Is it possible to get at that data from Project, or do I
have to recreate it in the code before populating the spreadsheet?

Thanks

 

[Rod Gill]

I looked in the Object browser and didn't see anything suitable so I suspect
you cannot read group totals. However, Excel as a number of tools for
creating subtotals. If you export Text1 then the most powerful tool is to
create a pivot table in Excel for the data which is easily done from Project
VBA (record a macro in Excel of you creating the pivot table then adapt the
code to run from Project).

--

Rod Gill
Project MVP

NEW!! Project VBA Book, for details visit: http://www.projectvbabook.com

 

[Jan De Messemaeker]

Hi,

There may be more elegant methods but if you select all the tasks including
the group summaries then walk through activeselection.tasks you will also
find the group summary tasks.
They have the property GroupSummary True.

Hope this helps,

 

[Ken]

Jan

i thought you had a solution for me, but, it seems that my Group Summary
tasks are not being evaluated and picked up when I go through the selected
tasks.

Can you see any reason why the following code would fail to print the
GroupBySummary=True tasks? The debug.print line, which comes before the
t.GroupBySummary IF statement only prints the 5 test tasks. It does not
print the 5 Group Summary tasks.

Am I looping through the selected tasks correctly?

Sub MoveGroupDataToExcel()

Dim t As Task
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook

Dim sheet1 As Object
Dim file1 As Object
Dim j As Integer
Dim i As Integer


Set xlApp = New Excel.Application
xlApp.Visible = True 'Make visible on Resource Bar

AppActivate "Microsoft Excel"

Application.ScreenUpdating = False

If Workbooks.Count = 0 Then
Set xlBook = xlApp.Workbooks.Add
End If

Set file1 = xlApp.Workbooks(1)
Set sheet1 = xlApp.Workbooks(1).worksheets(1)

'get list of unique entries

i = 1

For Each t In ActiveSelection.Tasks
If Not (t Is Nothing) Then
Debug.Print t.Name, t.PercentWorkComplete

If t.GroupBySummary = True Then
sheet1.cells(i, 1).Value = t.Name
sheet1.cells(i, 2).Value = t.PercentWorkComplete
i = i + 1
End If
End If
Next t

End Sub

If I eliminate the GrouBySummary If statement, the 5 test task and no
summary tasks populate spreadsheet.

Thanks for you help so far.

 

[John]

Jan

i thought you had a solution for me, but, it seems that my Group Summary
tasks are not being evaluated and picked up when I go through the selected
tasks.

Can you see any reason why the following code would fail to print the
GroupBySummary=True tasks? The debug.print line, which comes before the
t.GroupBySummary IF statement only prints the 5 test tasks. It does not
print the 5 Group Summary tasks.

Am I looping through the selected tasks correctly?

Sub MoveGroupDataToExcel()

Dim t As Task
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook

Dim sheet1 As Object
Dim file1 As Object
Dim j As Integer
Dim i As Integer


Set xlApp = New Excel.Application
xlApp.Visible = True 'Make visible on Resource Bar

AppActivate "Microsoft Excel"

Application.ScreenUpdating = False

If Workbooks.Count = 0 Then
Set xlBook = xlApp.Workbooks.Add
End If

Set file1 = xlApp.Workbooks(1)
Set sheet1 = xlApp.Workbooks(1).worksheets(1)

'get list of unique entries

i = 1

For Each t In ActiveSelection.Tasks
If Not (t Is Nothing) Then
Debug.Print t.Name, t.PercentWorkComplete

If t.GroupBySummary = True Then
sheet1.cells(i, 1).Value = t.Name
sheet1.cells(i, 2).Value = t.PercentWorkComplete
i = i + 1
End If
End If
Next t

End Sub

If I eliminate the GrouBySummary If statement, the 5 test task and no
summary tasks populate spreadsheet.

Thanks for you help so far.

Ken,
Please allow me to jump in. Group summary lines in Project are not
tasks. They are for presentation purposes only. Therefore you cannot
access group summary lines through VBA. You can verify this by selecting
a set of grouped tasks and then using the immediate window in the VB
Editor to do the following:
Print ActiveSelection.Tasks.Count

If you want to get the data into Excel, you can either copy and paste
(which will pick up the Group totals), or use VBA to export the
applicable tasks and re-create the group summary in Excel.

John
Project MVP

 

[Jan De Messemaeker]

Sorry I misled you.
I did do some tests in the Immediate window and had the impression it
worked; I must have been too optimistic.
Sorry!