export timescale resource/project data to excel

[Laura McA]

I want to export Resource Utilization information to Excel.
I want to see Reource Name, with each project assignment by month (columns)
with %Allocated.

I have tried a number of maps and export options but can't seem to figure it
out.

 

[John]

I want to export Resource Utilization information to Excel.
I want to see Reource Name, with each project assignment by month (columns)
with %Allocated.

I have tried a number of maps and export options but can't seem to figure it
out.

Laura,
You are posting in the developer newsgroup but the methods you describe
sound like you are not using VBA. You can certainly get there without
VBA but it won't be the most efficient method. For example, you want to
export two types of data - static (Project field) and timescaled (%
allocated). To export the static data, use an export map. To export the
timescaled data, use the "Analyze timescaled data in Excel"
utility/add-in. Then combine the two Excel Worksheets into one.

On the other hand if you are trying to use VBA, the TimeScaleData Method
is what you want, probably by assignment since you also want to see the
project (I assume you have a master file with inserted subprojects). I
would set up a loop for all resources and then loop through each
assignment of each resource. Use the Project Property and TimeScaleData
Method to extract the desired data. The basic code might look something
like this:

Sub ResAllocation()
ViewApply Name:="resource sheet"
SelectResourceColumn
[open and activate Excel]
Set Area = ActiveSelection.Resources
For Each r In Area
If Not r Is Nothing Then
Who = r.Name
For Each A In r.Assignments
What = A.Project
HowMuch = A.TimeScaleData(StartDate, _
EndDate, pjAssignmentTimescaledPercentAllocation, _
pjTimescaleMonths)
[dump into Excel on the fly]
Next A
End If
Next r
End Sub

Hope this helps.
John
Project MVP

 

[Laura McA]

This might sound stupid but...what are the steps for me to trigger this
export? How do I trigger the VB code?

I want to export Resource Utilization information to Excel.
I want to see Reource Name, with each project assignment by month (columns)
with %Allocated.

I have tried a number of maps and export options but can't seem to figure it
out.

Laura,
You are posting in the developer newsgroup but the methods you describe
sound like you are not using VBA. You can certainly get there without
VBA but it won't be the most efficient method. For example, you want to
export two types of data - static (Project field) and timescaled (%
allocated). To export the static data, use an export map. To export the
timescaled data, use the "Analyze timescaled data in Excel"
utility/add-in. Then combine the two Excel Worksheets into one.

On the other hand if you are trying to use VBA, the TimeScaleData Method
is what you want, probably by assignment since you also want to see the
project (I assume you have a master file with inserted subprojects). I
would set up a loop for all resources and then loop through each
assignment of each resource. Use the Project Property and TimeScaleData
Method to extract the desired data. The basic code might look something
like this:

Sub ResAllocation()
ViewApply Name:="resource sheet"
SelectResourceColumn
[open and activate Excel]
Set Area = ActiveSelection.Resources
For Each r In Area
If Not r Is Nothing Then
Who = r.Name
For Each A In r.Assignments
What = A.Project
HowMuch = A.TimeScaleData(StartDate, _
EndDate, pjAssignmentTimescaledPercentAllocation, _
pjTimescaleMonths)
[dump into Excel on the fly]
Next A
End If
Next r
End Sub

Hope this helps.
John
Project MVP

 

[Laura McA]

What is the syntax for the Start and End Date variables as shown below? Can
I define it to be a rolling 6 month view -- like today + 6 mo. as an end?

Also, what is the command associated with your comment [dump into Excel
on the fly]

I am new to VB

I want to export Resource Utilization information to Excel.
I want to see Reource Name, with each project assignment by month (columns)
with %Allocated.

I have tried a number of maps and export options but can't seem to figure it
out.

Laura,
You are posting in the developer newsgroup but the methods you describe
sound like you are not using VBA. You can certainly get there without
VBA but it won't be the most efficient method. For example, you want to
export two types of data - static (Project field) and timescaled (%
allocated). To export the static data, use an export map. To export the
timescaled data, use the "Analyze timescaled data in Excel"
utility/add-in. Then combine the two Excel Worksheets into one.

On the other hand if you are trying to use VBA, the TimeScaleData Method
is what you want, probably by assignment since you also want to see the
project (I assume you have a master file with inserted subprojects). I
would set up a loop for all resources and then loop through each
assignment of each resource. Use the Project Property and TimeScaleData
Method to extract the desired data. The basic code might look something
like this:

Sub ResAllocation()
ViewApply Name:="resource sheet"
SelectResourceColumn
[open and activate Excel]
Set Area = ActiveSelection.Resources
For Each r In Area
If Not r Is Nothing Then
Who = r.Name
For Each A In r.Assignments
What = A.Project
HowMuch = A.TimeScaleData(StartDate, _
EndDate, pjAssignmentTimescaledPercentAllocation, _
pjTimescaleMonths)
[dump into Excel on the fly]
Next A
End If
Next r
End Sub

Hope this helps.
John
Project MVP

 

[John]

This might sound stupid but...what are the steps for me to trigger this
export? How do I trigger the VB code?

Laura,
Sorry I missed your posts yesterday. I try to check back on responses I
have given but sometimes I miss one.

It wasn't clear from you original post whether you had VBA experience.
My example code was based on the assumption that you did know a little
about VBA and just needed a push in the right direction. Now that I
understand you are new to VBA there are two approaches you can consider.
If you want to learn how to write VBA macros with Project, there is an
excellent tutorial you can check out. Go to the MVP website at:
http://www.mvps.org/project/links.htm
At the bottom of the page you will find a link to "Project 98 Visual
Basic Environment Training Materials". Even though it says it for
Project 98, the material is equally applicable to all current versions
of Project. You can also look at some example code on fellow MVP Jack
Dahlgren's website at: http://masamiki.com/project/macros.htm
Once you have reviewed the training materials and practiced with some
simple code, you can tap the resources of this newsgroup for questions.

I mentioned a second option for VBA. If you don't really care to learn
VBA yourself, you can always have someone else do the code for you. If
you would like to take this route, you can post an offer on this
newsgroup.

Hope this helps.
John
Project MVP

 

[Laura McA]

I wish there was a simpler, self-service option but thanks for the
information. I would like to learn VBA but cannot afford to take the time, I
really just need to perform a one time export. I guess I will explore the
newsgroup options... thx

 

[John]

I wish there was a simpler, self-service option but thanks for the
information. I would like to learn VBA but cannot afford to take the time, I
really just need to perform a one time export. I guess I will explore the
newsgroup options... thx

Laura,
The VBA code to do what you want isn't that difficult but if you just
need a "one shot" export, it might be just as easy to use Copy/Paste
from Project to Excel.

John
Project MVP