Transfer Data to Excel

[jackal]

I can manually transfer selected cost data from MS Project to MS excel.
However, when I attempt to do this programmatically it is unsuccessful. I
can get a macro to run successfully using the PasteSpecial method in Excel

CFWorksheet.Activate
Range(Cells(5, NCol), Cells(5, NCol + NMonths)).Select
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False

but it results in a blank cell.

 

[Rod Gill]

What happens if you just specify the top left cell? You will get all data or
nothing, so specifying the whole area risks getting the area a different
size from the number of cells copied in Project, hence getting a blank cell
as the result.

 

[jackal]

This is the part of the MS Project code that copies the information

SelectTimescaleRange Row:=2, StartTime:=StrStDate, Width:=NMonths,
Height:=1
EditCopy

It appears to be work partly, but creates what appears to be an html object.

 

[Rod Gill]

The most reliable and configurable way to export time phased data from
Project is using the timescaledata method from Tasks. Resources or
Assignments. You can then copy one cell at a time exactly where you want in
Excel.

Alternatively, if you have Project 2002 or 2003 you can use oledb to read
data directly from a .mpp file from Excel VBA. See the pjoledb.htm file in
one of project's program folders for details.

 

[jackal]

This seemed to work, but then it stopped transferring data. I put a watch on
the TSV value and discovered it was generating an unknown error and sometimes
the data on the TASK sheet would become corrupted. What can cause this error
to occur.
Dim StrStartDate As String
Dim PrjTaskCost()
Dim TSV As TimeScaleValues, NumMonths As Long
StartDate = MyApp.ActiveProject.ProjectStart
EndDate = MyApp.ActiveProject.ProjectFinish
StrStartDate = Mid(WeekdayName(Weekday(StartDate)), 1, 3) + " " +
Format(StartDate, "mm/dd/yy")
MyApp.ViewApply Name:="Tas&k Usage"
MyApp.PaneNext
MyApp.SelectTimescaleRange Row:=1, StartTime:="Wed 1/1/03", Width:=1,
Height:=2096000
MyApp.SelectTimescaleRange Row:=2, StartTime:=StrStartDate, Width:=1,
Height:=1
MyApp.TimescaleEdit MinorUnits:=2, MinorLabel:=10, MinorTicks:=True,
Separator:=True, MajorUseFY:=True, MinorUseFY:=True, TierCount:=2

Set TSV = MyApp.ActiveCell.Task.TimeScaleData(StartDate, EndDate, _
Type:=pjTaskTimescaledCost, TimescaleUnit:=pjTimescaleMonths)
ReDim PrjTaskCost(1 To TSV.Count)

For NumMonths = 1 To TSV.Count
If TSV(NumMonths).Value = "" Then
PrjTaskCost(NumMonths) = 0
Else
PrjTaskCost(NumMonths) = TSV(NumMonths)
End If