Pass array from MS Project to MS Excel

[Sarah]

All,

I have a timescale array (I think it's an array) in MS Project that looks like this:
Set TSV = ActiveCell.Resource.TimeScaleData(txtFromDt.Value,
txtToDt.Value, _
pjResourceTimescaledWorkAvailability, pjTimescaleDays)

I need to get this information into a table in Excel, and I don't
really understand much about arrays. I need the Resources listed down
the side (the rows), the date range listed across the top (the
columns), and the TimescaledWorkAvailability in the appropriate
intersecting cells. If anybody knows how to do this, I would
appreciate the help.

Thanks!
Sarah
sarah_kiko@(removethis)cinfin.com

 

[JackD]

Sarah,

It is not clear what exactly you want but let's take an example of a chart
with weeks as columns and resources as rows.Here is the pseudo-code. You can
start here and should be able to get it working. I'd do it, but I don't have
time to debug it so I'm not going to start.

First, find a start and finish date (maybe use project start and finish or
maybe just start now and end in a year - your choice)
then write out the weeks in the column headers (see my website for an
example of writing column headers in the "export heirarchy to excel")
Use a couple of variables for the start and finish dates of each week -=
myStart, myFinish (you will increment these going from the start to the
finish for each resource)
move back to the first column and down a row
for each myResource in activeproject.resources
write the resource name
move to the right one cell
get the TSV for the
myResource.TimeScaleData(myStart, myFinish,
pjResourceTimescaledWorkAvailability, pjTimescaleWeeks, 1)
set the value of the excel cell to that,
increment myStart and myFinish by a week
move to the right one cell
do it again and again until you reach whatever point it is you want to stop
at.
return to the first column
go down one row.
go to the next resource...

-Jack

 

[John]

Sarah,
I see you decided to try my suggestion and write a VBA macro to export
your data. If Jack's pseudo-code is a little to general, write me
direct. I have a macro that essentially does what you want although it
will require some modification to export the WorkAvailability data you
want.

John