Copy values from Task Usage View Right Side

[stinkrod35]

I am working on a script to report time phased tasks grouped by a
custom outline code in Excel. I have grouped them in a Task Usage view
which leads to a grouped summation for each task. I want time scaled
values of custom groupings. If there is a better way, I could not find
it. This problem is still annoying either way...

First I go to my custom Task Usage View and export all of the Task
names and outline codes in the view. No Problem there:

For Each T In ActiveSelection.Tasks
xlApp.ActiveSheet.Cells(i, cWBSCode).Activate
xlApp.ActiveCell.Value = T.Text1

Next, I go through the group summations in the left pane to get a few
values like cost, actual cost, etc. Again no problem:
For i = 1 To NumTasks
xlApp.ActiveSheet.Cells(i + 1, cCost).Activate
SelectTaskCell Row:=(i * 2 - 1), Column:="Cost",
RowRelative:=False
xlApp.ActiveCell.Value = ActiveCell.Text

When I try to copy the time phased data from the right side, I run
into problems. I recorded a macro of selecting a cell on the right
side, and pasting it to Excel:

SelectTimescaleRange Row:=3, StartTime:="1/1/06", Width:=1, Height:=1
EditCopy

I pasted that into Excel and it worked fine. When I run it as a Maco,
it pastes the last selected cell on the left side:

SelectTimescaleRange Row:=(i * 6 - 4), StartTime:=FY07, Width:=1,
Height:=1
EditCopy
xlApp.ActiveSheet.Paste

The (i*6-4) part is because I have three rows of time phase data for
every row (cost, baseline, actual). That much works because I see the
right cells get highlighted.

Is there any good reason why this works when I do it by hand, but not
when I script it, using the exact recorded macro?

Also, when I clicked between the left and right sides of the usage
view, "NextPane" was recorded. According the help documentation, that
only has to do with the top and bottom panes, so I don't know why I
was seeing that. I tried it with "Next Pane" and without, and had the
same results.

I think the root of my problem is that I can't see what the
SelectTimeScaleRange method does. According to the documentation it
selects timescale data cells in the usage view. This doesn't seem to
change the ActiveSelection, and I can't find any effect anywhere in
the Application object when I do this. Do I need to make this
selection the ActiveSelection or add it to the ActiveSelection (both
of which also sound wrong)?

Running Standard 2003 SP 2 BTW. Any thoughts? I am stumped.

 

[Rod Gill]

Hi,

You need to use the TimeScaleData property for each Task, Resource or
Assignment to export the timed data in the yellow area of a usage view. I
don't think you can get at the group summary data, but Excel can group data
as well, so use Excel Subtotals, Group or Pivot tables to get what you want.

There are samples of Timescale code in various blogs, web sites (and my
book!) so search for Project timescaledata for some sample code

Whilst the basics are simple, getting exactly what you want will take some
trial and error.

--

Rod Gill
Project MVP

Project VBA Book, for details visit:
http://www.projectvbabook.com

NEW!! Web based VBA training course delivered by me. For details visit:
http://projectservertraining.com/learning/index.aspx

 

[stinkrod35]

Thanks Rod.

I know I could do it that way (and I never would have figured that out
without the help of postings on this group) but in this case, that
approach seems much less elegant (if that's possible!). If my approach
is not possible because I don't understand something or there is a
bug, then I guess I have no choice other then to redo all calculations
in Excel rather than just report what is already done in the view but...

I already have everything that I need the way that I need it in the
task usage view filtered and grouped the way I want it. I can export
everything from there into Excel except the time phased values. But I
can copy and paste them by hand. And I can select them in the macro
using SelectTimescaleRange, but the wrong cell is pasted.

Why can I select a TSV with a mouse click, then Edit->Copy, then Paste
into Excel but I can't use a same macro to do the same thing?

Is there ever a case where anyone has used the SelectTimescaleRange
method successfully? Where could I see a change in the application
object after SelectTimescaleRange?

 

[Rod Gill]

Personally I always use Timescaledata as it is more precise and powerful in
the things it can do. Copy and paste is to imprecise.

Doing this manually I had to make the time scaled yellow area active before
running the SelectTimescaleRange command. Then copying and pasting into
Excel worked.

With the first cell active I pressed F6 to make the time scaled area active.
In VBA it's PaneNext

--

Rod Gill
Project MVP

Project VBA Book, for details visit:
http://www.projectvbabook.com

NEW!! Web based VBA training course delivered by me. For details visit:
http://projectservertraining.com/learning/index.aspx


----------------------------------------------------------------------------------------------------


Thanks Rod.

I know I could do it that way (and I never would have figured that out
without the help of postings on this group) but in this case, that
approach seems much less elegant (if that's possible!). If my approach
is not possible because I don't understand something or there is a
bug, then I guess I have no choice other then to redo all calculations
in Excel rather than just report what is already done in the view but...

I already have everything that I need the way that I need it in the
task usage view filtered and grouped the way I want it. I can export
everything from there into Excel except the time phased values. But I
can copy and paste them by hand. And I can select them in the macro
using SelectTimescaleRange, but the wrong cell is pasted.

Why can I select a TSV with a mouse click, then Edit->Copy, then Paste
into Excel but I can't use a same macro to do the same thing?

Is there ever a case where anyone has used the SelectTimescaleRange
method successfully? Where could I see a change in the application
object after SelectTimescaleRange?

 

[stinkrod35]

I would use Timescaledata if it worked for grouped data. As far as I
can tell it only works for tasks, resources, and assignments. I'll
take a little impression cutting and pasting rather than have to
regroup all of this in Excel, which does not seem to work with outline
codes as easily.

Like I said before, I know doing it manually works and PaneNext has no
effect in the macro. Project will show PaneNext if you record a macro
while doing the copy and paste. PaneNext does not have the same effect
in a macro. It will switch to the bottom pane if there is one in the
view, but it will not make the left or right pane active as F6 does. I
don't think there is a way to do it in a macro. PaneNext switches
between top and bottom panes according to the help, and that's all it
seems to do.

If that is true, I have no choice but to redo a bunch of grouping and
summing in Excel that I can see on the screen and cut and paste
manually in Project. That is very disappointing. (Rant begins here)
Just like the fact that I have to do all of this in the first place
because Project does not provide an easy way to show and roll up a
project in a product oriented WBS outline and a process oriented
outline. I have to pick one or the other and then find out that I have
to do all of the summing in Excel for the one I didn't enter as the
Outline. That makes OutlineCodes 1-20 much less useful. I can see
everything I want using grouping, but there is no way to get to that
data in an automated fashion. Boo Project.