Project To Excel

[RevKev]

Hello All,

I am trying to take a custom view and copy that to EXCEL. The View has a
simple table on the left, which I can programatically write to EXCEL.
However, the table on the left (TimeScale) which has Cost and Work is giving
me a lot of problems. I cannot seem to figure out how to copy and paste into
EXCEL.
If I target the top, left cell I get an error that there is nothing in the
clipboard. Likewise if I just try to get a few cells, the table turns blue
as if it has been selected, however nothing will copy over.... here is what
I have;
--------------------------------------------------------------------------------
Dim xlRow As Excel.Range
Dim xlCol As Excel.Range

Sub TrasferTimescaleData()

Const intervalType As Integer = pjTimescaleWeeks
Const dataType As Integer = pjWork
Const dataCost As Integer = pjCost

'Link to Excel

Dim Xl As Excel.Application
Dim xlSheet As Excel.Worksheet
Dim xlBook As Excel.Workbook

Set Xl = CreateObject("Excel.Application")
Set Xl = New Excel.Application
Set xlBook = Xl.Workbooks.Add
Set xlSheet = xlBook.Worksheets.Add
xlSheet.Name = "pj"

'Set first cell
Set xlRow = Xl.ActiveCell

'Copy Left Table

'SelectRange 3, 4
'xlSheet.Paste xlRow

'SelectResourceColumn Column:="Name", Additional:=2
'EditCopy
'xlSheet.Paste


'Transfer Table Headings
Dim i As Integer
Dim Heading As String
For i = 1 To ActiveSelection.FieldNameList.count
xlSheet.Cells(1, i) = ActiveSelection.FieldNameList(i)
Next

'Copy Time Scale Data
Dim TSValues As TimeScaleValues
Dim noColumns As Integer, startCol As Integer
TimescaleEdit intervalType, intervalType
DetailStylesRemoveAll
DetailStylesAdd Item:=dataType
DetailStylesAdd Item:=dataCost
Set TSValues =
ActiveProject.ProjectSummaryTask.TimeScaleData(ActiveProject.ProjectStart,
ActiveProject.ProjectFinish, intervalType)
noColumns = TSValues.count

PaneNext
SelectTimescaleRange Row:=1, StartTime:="Sun 10/1/06", Width:=5, Height:=8

'Tansfer Timescale Dates
Dim TSV As TimeScaleValue
i = 0
For Each TSV In TSValues
i = i + 1
xlSheet.Cells(1, 1) = Format(TSV.StartDate, "mm/dd/yy")
Next

Xl.Visible = True
EditCopy
xlSheet.Paste
End Sub

 

[Jack Dahlgren]

RevKay,

Don't bother with copying cell by cell. Project is NOT excel.

instead you should be doing something like

for each task in active project.tasks
'copy task name to excel
xlRow.vlaue = task.name
'move to next column
loop through all the timescaled values (types are pTaskTimescaledWork
and pjTaskTimescaledCost) for the task and write to excel.

-Jack Dahlgren

 

[RevKev]

jack,

Thank you for replying...
I have inserted this code:

Set tsvs = t.TimeScaleData(StartDate:=t.ActualStart, EndDate:=t.ActualFinish)

For Each t In ActiveProject.Tasks

For Each tsv In tsvs
xlRow.Value = pjTaskTimescaledCost
dwn 1
xlRow.Value = pjTaskTimescaledWork
dwn 1
Next tsv

Next t

 

[Jack Dahlgren]

A few things:

First the "set tsvs" should occur for each task - it needs to be inside your
"for each" loop otherwise the same data will be pushed over and over for
each task.

Second: What data are you expecting?

What units do you want it to report in? The default units for tsv's are
weeks.

-Jack Dahlgren

 

[RevKev]

Jack...
Thank you for the extra help!

I have set the tsvs inside the For Loop and still get the same thing...
here is what data I am expecting, maybe this will help. Thank you for
pointing me in the right direction!
2nd Quarter
Apr May
WORK 247.75 233.83h
COST $13,499.96 $12,741.54
WORK 91.4h 86.27h
COST $5,296.44 $4,998.89
WORK 21.65h 20.43h
COST $1,359.55 $1,283.17

 

[RevKev]

Jack and All...

I have narrowed down my problem only to pose a new issue...
I am now getting the WORK and COST data for quarters that I want, however;
1.) it is not formatted the way I want which I can handle if I can get 2
taken care of. I am trying to format in the example in the last post by
quarter, any pointers would be great!!!
2.) with the below code I am looping through an individual resource,
however, what I NEED to do is loop through all the resources in a specified
GROUP. Any pointers would be great! Here is what I have:

Set tsvs = ActiveProject.Resources("resource").TimeScaleData( _
StartDate:=ActiveProject.ProjectStart, _
EndDate:=ActiveProject.ProjectFinish, _
Type:=pjResourceTimescaledWork, _
TimeScaleUnit:=pjTimescaleQuarters, count:=1)

For Each tsv In tsvs
If tsv <> "" Then
xlRow = Val(tsv.Value) / 60 & " WORK"
dwn 1
End If
Next tsv

' '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Set tsvs = ActiveProject.Resources("resource").TimeScaleData( _
StartDate:=ActiveProject.ProjectStart, _
EndDate:=ActiveProject.ProjectFinish, _
Type:=pjResourceTimescaledCost, _
TimeScaleUnit:=pjTimescaleQuarters, count:=1)

For Each tsv In tsvs
If tsv <> "" Then
xlRow = "$" & Val(tsv.Value) / 60 & " COST"
dwn 1
End If
Next tsv

|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

 

[RevKev]

Hello All,

I am now to this point.. I can get the data desired if I specify a resource.
What I need is to be able to gather data from a user defined (Enterprise)
field. I just can't figure out how to access that property to be able to set
the tsvs. Here is what i have:

Set tsvs = ActiveProject.Resources("resource").TimeScaleData( _
StartDate:=ActiveProject.ProjectStart, _
EndDate:=ActiveProject.ProjectFinish, _
Type:=pjResourceTimescaledWork, _
TimeScaleUnit:=pjTimescaleQuarters, count:=1)

SelectResourceField Row:=0, Column:="Enterprise Resource Outline Code6"

For Each tsv In tsvs
' Dim x As String

If tsv <> "" Then
xlRow = "WORK"
Set xlRow = xlRow.Offset(0, 1)
xlRow = Val(tsv.Value) / 60
Set xlRow = xlRow.Offset(1, -1)
End If
Next tsv

' '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Set tsvs = ActiveProject.Resources("resource").TimeScaleData( _
StartDate:=ActiveProject.ProjectStart, _
EndDate:=ActiveProject.ProjectFinish, _
Type:=pjResourceTimescaledCost, _
TimeScaleUnit:=pjTimescaleQuarters, count:=1)

For Each tsv In tsvs
If tsv <> "" Then
xlRow = "COST"
'Set xlRow = xlRow.Offset(1, 0) 'cost is correct
Set xlRow = xlRow.Offset(0, 1)
xlRow = "$" & Val(tsv.Value)
Set xlRow = xlRow.Offset(1, -1)

 

[Jack Dahlgren]

You can run the TimeScaleData method on a resource or a resource collection.
One easy way to get a resource collection is to go to resource sheet, filter
by whatever criteria you want, then selectall.

dim filteredResources as Resources
'filter by group
'select all
set filteredResources = ActiveSelection.resources

set tsv = filteredResources.TimeScaleData(....

-Jack Dahlgren

 

[Jack Dahlgren]

Use the FieldNameToFieldConstant method to convert the field name into a
constant then use GetField

http://msdn2.microsoft.com/en-us/library/bb213545.aspx

example: myResource.getfield("whatever your field is called", pjResource)

-Jack Dahlgren