Timescaled data to Excel

[Fred Newton]

I have read many articles relating to extracting data from
Project/2000 (or 2002) and exporting it to Excel, however not being
well versed in VB and not having access to VB developers, all I do is
self taught or as a result of the articles found on sites such as
this. As a result i'm struggling somewhat.

In a nutshell, my objective is to produce a report of data from
Project/2000, showing the work assigned, by person by task within
group. I've looked at the output from the "Analyze Timescaled Data in
Excel" and, while it goes part way, i'm trying to achieve a
combination of the two reports it creates. I've looked at the code it
uses, what can I say, it leaves me totally bewildered. I've also
picked up a tranch of code from this site, even with the "throw-away"
comment of "all you need do is .....", that had em flummoxed for a
while, but I got there

The format of the report i'm trying to produce should look something
like :

Group Resource Period1 Period2 Period3 Period4

Group1 Name1
Task1 hh:mm hh:mm
Task2 hh:mm hh:mm
Task3 hh:mm hh:mm
Task4 hh:mm hh:mm

Name2
Task1 hh:mm
Task2 hh:mm hh:mm hh:mm
Task3 hh:mm hh:mm
Task4 hh:mm
Group2 Name1
Task1

Where Group1, Name1's Task1 isn't necessarily the same task as Group1,
Name2's Task1 and Group1 Name1 will not be the same as Group2 Name1

With the code below i've achieved a large part of the report, in as
much as I get the Names, Tasks and Work values as I expect, however
the Group Names are not appearing, this is the Group field for a
Resource i'm after. Can anyone offer me a solution, i'm sure it's
simple, I just can't see it.

TIA
Fred Newton
Zurich Financial Services
------
Sub AssignmentInfo2xlsByGroups()

Const objxlApp = "Excel.Application"

Dim xlApp As Object, xlBook As Object, xlRng As Object
Dim tsk As Task, asn As Assignment, tsv As TimeScaleValue, res As
Resource, proj As Project

On Error Resume Next

'Assume Excel may or may not be running.
'First try to find a running copy of Excel
Set xlApp = GetObject(, objxlApp)

'If there isn't one, xlApp will still be Nothing
If xlApp Is Nothing Then
'Excel not found, start new copy
Set xlApp = CreateObject(objxlApp)

'Excel starts without being on Resource Bar
xlApp.Visible = True 'Make visible on Resource Bar
End If
AppActivate "Microsoft Excel"

'Create new Workbook. Add method returns a pointer
'to the new workbook
Set xlBook = xlApp.Workbooks.Add

'Set Rng to point to A1 in first sheet
Set xlRng = xlApp.ActiveCell

'Write and format title
Set proj = ActiveProject 'Using Proj is slightly faster
xlRng = "Monthly Work Report for " & proj.Name
xlRng.Range("A2") = "As of " & Format(Date, "mmmm d yyyy")
xlRng.Range("A1:A2").Font.Bold = True
xlRng.Font.Size = 12

'Move xlRng below titles
Set xlRng = xlRng.Offset(3, 0)

'Create column titles and format
xlRng = "Group Name"
With xlRng.EntireRow
.Font.Bold = True
.WrapText = False
.ColumnWidth = 10
.HorizontalAlignment = xlHAlignCenter
.VerticalAlignment = xlVAlighnCenter
.AutoFit
End With
xlRng.EntireColumn.ColumnWidth = 20
Set xlRng = xlRng.Offset(0, 1)
xlRng.EntireColumn.ColumnWidth = 20
xlRng = "Resource Name"
Set xlRng = xlRng.Offset(0, 1)
xlRng.EntireColumn.ColumnWidth = 50
xlRng = "Task Name"
ColCtr = 3
Set xlRng = xlRng.Offset(0, 1)
'Print Dates
For Each tsv In ActiveProject.ProjectSummaryTask.TimeScaleData( _
StartDate:=ActiveProject.ProjectStart, _
EndDate:=ActiveProject.ProjectFinish, _
Type:=pjAssignmentTimescaledWork, _
timescaleunit:=pjTimescaleMonths, _
Count:=1)
xlRng = tsv.StartDate
Set xlRng = xlRng.Offset(0, 1)
ColCtr = ColCtr + 1
Next

Set xlRng = xlRng.Offset(1, -ColCtr)

'Print Resource Group
For Each Group In ActiveProject.ResourceGroup
xlRng = Project.Group.Name
Set xlRng = xlRng.Offset(0, 1)
'Print Resource Name
For Each res In proj.Resources
If Not (res Is Nothing) Then
xlRng = res.Name
Set xlRng = xlRng.Offset(0, 1)
'Print assignments
For Each tsk In ActiveProject.Tasks
If Not (tsk Is Nothing) Then
For Each asn In tsk.Assignments
If res.Name = asn.ResourceName Then
xlRng = tsk.Name
Set xlRng = xlRng.Offset(0, 1)
ColCtr = 1
For Each tsv In asn.TimeScaleData( _

StartDate:=ActiveProject.ProjectStart, _

EndDate:=ActiveProject.ProjectFinish, _
Type:=pjAssignmentTimescaledWork,
_
timescaleunit:=pjTimescaleMonths,
_
Count:=1)
xlRng = Val(tsv.Value) / 60
Set xlRng = xlRng.Offset(0, 1)
ColCtr = ColCtr + 1
Next
Set xlRng = xlRng.Offset(1, -ColCtr)
End If
Next
End If
Next
End If
Set xlRng = xlRng.Offset(0, -1)
Next
Set xlRng = xlRng.Offset(0, -1)
Next
xlRng.Offset(-1, 0).CurrentRegion.Offset(1, 0).NumberFormat = _
"0.00\h;;"
End Sub

 

[Rod Gill]

Hi,

The Resource Groups collection is not related to ResourceGroup in the
resource sheet. I experimented with it and got very strange results and help
refused to show anything on it.

SO, I recommend you copy all resource names and groups to a spare sheet in
Excel then sort by group and name. Use this sorted list to provide the
sequence for exporting data. If you wanted to, you could sort the resource
sheet by group and name but you would have to renumber to get the sequence
in the For Each loops to change. You could also save the ID number in
Number1 before sorting so you could resort by Number1 to restore the
original sequence at the end of the macro.

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

 

[Fred Newton]

Hi Rod,

Thanks for the pointer, however, during some digging in VB I came
across the Group names I was after, in the Resources.Item(i).Group
field. I've sorted the groups (thanks for the code whoever put it
up), removed duplicates (thanks again)and come up with a resonable
looking report.

My next problem came when I was trying to run against a Master project
with sub-projects. I've found the Subprojects.Count that tells me how
many subprojects there are and the individual Subproject.Item(n) that
expands to give me all the info I need, however the Item(n) always
seems to be one short of the Count value, any ideas as to why ?

Regards
Fred Newton
Zurich Financial Services

 

[Rod Gill]

Many collections start with item 0. SO, a count of 5 is item(0) to item(4)
For most collections you don't need to use item at all. EG
Resources("Resource Name") or Resources(1) work equally well.

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

 

[Jack D.]

Hi Rod,

Thanks for the pointer, however, during some digging in VB I came
across the Group names I was after, in the Resources.Item(i).Group
field. I've sorted the groups (thanks for the code whoever put it
up), removed duplicates (thanks again)and come up with a resonable
looking report.

My next problem came when I was trying to run against a Master project
with sub-projects. I've found the Subprojects.Count that tells me how
many subprojects there are and the individual Subproject.Item(n) that
expands to give me all the info I need, however the Item(n) always
seems to be one short of the Count value, any ideas as to why ?

Regards
Fred Newton
Zurich Financial Services

Items start counting at zero.
if you count Item 0, Item 1 and Item 2 you get a count of 3.
You will see similar behavior in arrays etc.

--
Please try to keep replies in this group. I do check e-mail, but only
infrequently. For Macros and other things check http://masamiki.com/project

-Jack Dahlgren, Project MVP
email: J -at- eM Vee Pee S dot COM


+++++++++++++++++++