PainNext

[jdb]

Hi all,

I know the preferred method of exporting tsv data to excel is with the
timescaledata function, but there are a number of reasons why I need to
copy/paste. First, the project file is huge (over 5000 tasks) and using the
timescaledata method takes close to an hour:

For row = 1 To ActiveProject.Tasks.count
xlSheet.Cells(row, 1) = ActiveProject.Tasks(row).Text18
xlSheet.Cells(row, 2) = ActiveProject.Tasks(row).Text24
xlSheet.Cells(row, 3) = ActiveProject.Tasks(row).Text27
xlSheet.Cells(row, 4) = ActiveProject.Tasks(row).Text22
xlSheet.Cells(row, 5) = ActiveProject.Tasks(row).Text15
xlSheet.Cells(row, 6) = ActiveProject.Tasks(row).Text14
xlSheet.Cells(row, 7) = ActiveProject.Tasks(row).Name
xlSheet.Cells(row, 8) = ActiveProject.Tasks(row).ResourceNames
xlSheet.Cells(row, 9) = ActiveProject.Tasks(row).Text23
xlSheet.Cells(row, 10) = ActiveProject.Tasks(row).Work
xlSheet.Cells(row, 11) = ActiveProject.Tasks(row).Duration
xlSheet.Cells(row, 12) = ActiveProject.Tasks(row).Start
xlSheet.Cells(row, 13) = ActiveProject.Tasks(row).Finish

Set tsvPF = ActiveProject.Tasks(row).TimeScaleData( _
"7/1/2008", "6/30/2009", TimescaleUnit:=pjTimescaleMonths)
For col = 13 To tsvPF.count + 13
If tsvPF(col - 13).Value <> "" Then
xlSheet.Cells(row, col).Value = tsvPF(col - 13).Value / 60
End If
Next
Next

The people who will be running this export tool won't want to wait an hour
every time the have to run this report. Second, there is a complex filter in
place that is based on hidden, sensitive (read inaccessible) fields. The
timescaledata function doesn't seem to be effected by filters but I could
copy/paste the correct data from the view. Finally, the data must be
exported to a spreadsheet that was created by copy/pasting the work details
so there are a number of formulas that need to line up with certain rows in
the data.

Long story short: does anyone know the secret to changing panes in the task
usage view so I can copy/paste the work detail data (the PaneNext function
doesn't work)?

 

[Rod Gill]

The method you are trying is fraught with complications and loopholes that
might break your code. By using activeselection.tasks you can loop thru
selected tasks. To dramatically speed up the export, create an Array
variable that you copy all data to then paste the array in one go to Excel.
What takes the time is the copy between programs. This method should reduce
60 minutes to 1 minute or less!

You will need to carefully build the array to handle all fields and tasks
and carefully select exactly the same number of cells to paste into in
Excel.
--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

 

[jdb]

Thank you for the array and activeselection recommendations. For the record,
though, is there a way to do this with selecttimescalerange and foreground
processing? What are the cons of doing it that way?

Thanks again,
David

 

[Rod Gill]

Many. You can't control what the user might do. If they change to another
program at the wrong moment you will cause chaos! In my book it should never
be considered even if it is possible. Recording a macro gives the following
code:

ViewApply Name:="Resource &Usage"
WindowActivate WindowName:="Project1", TopPane:=True
PaneNext
SelectTimescaleRange Row:=1, StartTime:="6 Apr '09", Width:=12,
Height:=3
EditCopy

I still recommend not using this approach.

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

 

[jdb]

Mr. Gill,
Again, thanks for dealing with an issue that I know you object to. The
problem I'm having is that I can't get the PaneNext function to do what the
macro recording seems to suggest it should do. When I run procedures like
the one you recorded, I get the Task information (left pane) copied instead
of the time scale data (right pane). The PaneNext function doesn't seem to
change panes. Is there a particular way to invoke it or some precondition
that I need to get it to work? Is there another way to bring the time scale
data into focus?

Again, thank you very much. I know it hurts

 

[Rod Gill]

If that code doesn't work, then you're out of luck. You will need to make
sure things are setup exactly right and no one even thinks of clicking
anything once the macro starts!

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

 

[jdb]

Does the PaneNext function work for you? Can you programmatically change the
active pane in a combination view? Did the macro you recorded allow you to
copy data from the next pane or did you get the first pane's data on the
clipboard?

 

[Rod Gill]

If its recorded, it works!

I'm at the stage here where I know the route you are taking is fraught with
problems, then far more problems when you try to run the code regularly
live. As such I really do not want to get involved in case there is any
chance of you even thinking "but you said this code should...". Any
manipulation of the screen when users can interrupt it is not supportable in
any production environment and may cause damage to data in other programs.
Please re-consider using the timescaledata method and storing data in arrays
for speed.

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

 

[jdb]

If its recorded, it works!
I have a possible exception to this rule. I recorded several macros that
suggested using the PaneNext function and then tried to run them from the VB
IDE. None of them reproduced the desired results (i.e. - copying the time
scale data instead of the task usage data). What I believe is happening is
that the PaneNext function only works when Project is in focus. The Activate
functions don't seem to bring the Project window to the front of the Z order,
so the PaneNext signal (also F6) is ignorred.

I'm at the stage here where I know the route you are taking is fraught with
problems, then far more problems when you try to run the code regularly
live. As such I really do not want to get involved in case there is any
chance of you even thinking "but you said this code should...". Any
manipulation of the screen when users can interrupt it is not supportable in
any production environment and may cause damage to data in other programs.
Please re-consider using the timescaledata method and storing data in arrays
for speed.

You are right, of course. I am having a ridiculous time trying to get this
to work in testing. Thank you for your activeselection and array
recommendations.

 

[Rod Gill]

Whew! Good luck with the other way. The trick is to get an array working
with just information for one or two tasks first then scale up, don't try to
make the whole thing go at once. The array dimension and the area you
pre-select in Excel before copying is crucial.

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com