Group Info

[Sivart]

First post here. I have used my very strong Google-fu for almost ten hours no and haven't found an answer.

I am working for a Japanese company that has some odd reporting requests. I have made ways to meet this requirement but I am looking for a better way.

I have categories in a text column. When I group by these categories I want to extract an array with two pieces of info. Posting code from home by memory so it is close but not exact.

Dim tmparray() as variant
dim t as task
dim ts as tasks
Dim i as integer

I=0
groupby "text1"
outlinelevel1 'so it only shows group headers
selectall
set ts =activeselection.tasks
for each t in ts
If t is not nothing then
Tmpname=t.name
Tmppercent=t.percentcomplete
tmparray(i)= tmpname & "|" & tmppercent
I=i+1
Endif
next t



i understand this code may not be perfect I can clean it up in the morning. The problem is it only does the tasks and skips the group headers.

I also tried looping selectrow with a rowcounter and it jumped all over the place.

The one time I got it to select the right cell it would only return "True" for both cells not the data contained.

Hopefully this makes sense what I am trying to do. I cannot post the file due to NDA and security requirements on my computer(can't be online even)

If I can get the data into a string or array I can take it from there.

Any help is appreciated and I apologize again for not being able to provide better info.

[Sivart]

I figured out one way to do it though I know it is a bastardized way to do it. I found where someone else asked this for the 2007 version and never received an answer so it may help others in the future. By doing select all then picking activecell it starts on the first row everytime and runs until the next row is an error. If anyone has any better methods I would love to know I have been doing VBA for about 10 years now in Excel, Access, Word, and Outlook in that order of proficiency and am totally self taught so programming in Project the last week has been an interesting learning curve.

Sub testgrouptitle()
Dim c As String
Dim TmpArray() As Variant


'set view
    Application.GroupApply "test"
    OutlineShowTasks OutlineNumber:=pjTaskOutlineShowLevel1


    
    
r = 0
SelectAll
Do
    c = Application.ActiveCell.Task.Name & "|" & Application.ActiveCell.Task.PercentComplete
    ReDim Preserve TmpArray(r)
    TmpArray(r) = c
    r = r + 1
    Application.SelectCellDown
On Error GoTo endloop 'will error on last row if blank
    c = Application.ActiveCell.Task.Name
Loop


endloop:


'test results
    For i = LBound(TmpArray) To UBound(TmpArray)
        MsgBox TmpArray(i)
    Next i
    
'reset view
    FilterClear
    Application.GroupClear
    OutlineShowTasks OutlineNumber:=pjTaskOutlineShowLevelMax




End Sub

[Sivart]

Fully integrated it today after teaching myself how to make a multidimensional array that is adjustable. Hopefully this may help someone else in the future.

[sapfid2016]

Thankyou for this. Was struggling with this for sometime.

But I am unable to setvalue to a custom field in this row. Can you please help me with the same ?

I want to set the Number field %target to a calculated value

Dim fldCode As Long

Application.ActiveCell.Task.GetField(FieldNameToFieldConstant("Dependency") ) ---- Works
fldCode = FieldNameToFieldConstant("%Target")
Application.ActiveCell.Task.SetField fldCode, 75 ---- Fails saying wrong argument,

Would be great if you could help me with this.

[Sivart]

Without having the program in front of me I would say manually input a number then using step by step walk through of your VBA code pull that field out and see what it returns to VBA. Possibly if it is a percent you may need to enter .75 as the value, not 75. It could be too that this is not an updatable field but I am by no means an expert programmer, I am just excellent at thinking way outside the box. If that doesn't work then let me know and I will dig into it when I have some free time.

[sapfid2016]

Assuming the error is due what is explained in one of the article.

The group by field is a summary field and it cannot be edited , but should be auto-calculated.


How can we calculate the summary fields and group by fields in VBA using formula.

My need is ... I have target % for each task. At the summary and group by level I want to calculate the weighted average,, basically do what % complete does.

But as understand there is bug in 2013 that the formula dosnt work for summary and group by .


Is there any way to generate graph using VBA by reading %complete and other two custom fields ?


Thanks for you help in advance.

[Sivart]

I believe the previous post has it right. I wasn't 100% sure what was being asked last night as it was way past my bedtime and I haven't thought of this in months. If there is an issue with calculating groupings in 2013 then i don't know about that issue I use groups all the time on 2013 and have never had an issue with calculations being wrong. If I am constantly changing data I just force a recalculate and it shows the new data. As for editing a grouping header that is not possible without going through the editing of the source info that feeds it. Sorry if this doesn't help you any.