Slow copying in Project

[egun]

I have a macro I use to export Project data to Excel. It has several
options, among which is an option to show or hide summary tasks related to
the filtered tasks. When I choose not to show summary tasks, the VBA code

SelectTaskField Row:=0, Column:=theFieldNames(1), RowRelative:=False, _
Width:=theSel.FieldNameList.Count - 1,
Height:=theSel.Tasks.Count - 1
EditCopy

runs very quickly, usually less than a second. When I show the summary
tasks, the copy command takes many times longer, more than just the increase
in the number of rows being copied. You can verify this manually by applying
a filter with and without summary tasks, selecting all rows, and copying the
data to the clipboard.

Anyone know why the disproportionate amount of time to copy the data with
summary rows? Any workarounds that might speed up the process?

Thanks!

 

[John]

I have a macro I use to export Project data to Excel. It has several
options, among which is an option to show or hide summary tasks related to
the filtered tasks. When I choose not to show summary tasks, the VBA code

SelectTaskField Row:=0, Column:=theFieldNames(1), RowRelative:=False, _
Width:=theSel.FieldNameList.Count - 1,
Height:=theSel.Tasks.Count - 1
EditCopy

runs very quickly, usually less than a second. When I show the summary
tasks, the copy command takes many times longer, more than just the increase
in the number of rows being copied. You can verify this manually by applying
a filter with and without summary tasks, selecting all rows, and copying the
data to the clipboard.

Anyone know why the disproportionate amount of time to copy the data with
summary rows? Any workarounds that might speed up the process?

Thanks!

egun,
The code runs slow because it is using foreground processing. Selecting
cells and copy/paste is a very inefficient way to export data
(graphically intense). A much better and faster solution is to use
background processing wherein Project objects and/or properties are
queried and exported directly (i.e. in the background).

It might be easier to help if you explained what you are trying to do
rather than how you want to do it. Obviously your "how" is slow and
inefficient. With a bigger picture, we could probably suggest a much
better approach.

Just for reference, you might want to take a look at fellow MVP Jack
Dahlgren's website on VBA. The address is:
http://masamiki.com/project/macros.htm
In particular take a look at his macro called "Export hierarchy to
Excel".

Hope this helps.
John
Project MVP

 

[egun]

John, thanks for your reply. See comments below.

Just for reference, you might want to take a look at fellow MVP Jack
Dahlgren's website on VBA. The address is:
http://masamiki.com/project/macros.htm
In particular take a look at his macro called "Export hierarchy to Excel".

I have looked at this site, and his export code is similar to what I created
at first - processing one task at a time and dumping it to the Excel
worksheet. I am doing something similar, but find it slow when there are a
lot of tasks to export.

Here is what I am doing:

The user of the macro has an Excel file open with a tab that specifies two
things that drive the macro: the field names the user wants to export from
Project, and a series of filters to be applied to the Project data prior to
exporting. The macro creates a new view with the specified fields, creates a
new filter and applies it, and for each filter, the macro creates a new
worksheet in the Excel file and dumps the data to that sheet. It's sort of a
batch processing setup for extracting data for multiple teams on a periodic
basis. I even export the outline hierarchy by using Excel's 'InsertIndent'
function (rather than using multiple columns - I think it looks better).

I am searching for ways to speed the macro up, and I thought that selecting
the whole filtered set of tasks and then copying and pasting would be
quicker, since you're working with large blocks of data at once. As I said
before, it is much faster when the summary rows are filtered out. It's
REALLY slow when the summary tasks are included (ShowSummaryTasks:=True), and
all that extra time is spent in the Copy command. I do the same copy/paste
operations manually and see the same difference. It's order-of-magnitude
different! I'll keep looking for a better technique.

 

[JackD]

Since the lag is in the copy and paste how about when you do the "select
all"
you simply set a task collection to the selected tasks and then process them
in the background
Something like this:

set myTasks = activeselection.tasks
for each task in myTasks
'write task info to excel
next task

 

[John]

John, thanks for your reply. See comments below.


I have looked at this site, and his export code is similar to what I created
at first - processing one task at a time and dumping it to the Excel
worksheet. I am doing something similar, but find it slow when there are a
lot of tasks to export.

Here is what I am doing:

The user of the macro has an Excel file open with a tab that specifies two
things that drive the macro: the field names the user wants to export from
Project, and a series of filters to be applied to the Project data prior to
exporting. The macro creates a new view with the specified fields, creates a
new filter and applies it, and for each filter, the macro creates a new
worksheet in the Excel file and dumps the data to that sheet. It's sort of a
batch processing setup for extracting data for multiple teams on a periodic
basis. I even export the outline hierarchy by using Excel's 'InsertIndent'
function (rather than using multiple columns - I think it looks better).

I am searching for ways to speed the macro up, and I thought that selecting
the whole filtered set of tasks and then copying and pasting would be
quicker, since you're working with large blocks of data at once. As I said
before, it is much faster when the summary rows are filtered out. It's
REALLY slow when the summary tasks are included (ShowSummaryTasks:=True), and
all that extra time is spent in the Copy command. I do the same copy/paste
operations manually and see the same difference. It's order-of-magnitude
different! I'll keep looking for a better technique.

egun,
I second Jack's suggestion for creating an object of the selected set. I
use that method all the time in my macros. I also use another method
which may or may not help with your export. If I need to do manipulation
on the Project data, I read the data, manipulate it and dump it into an
array or group of arrays. Then when I open Excel, all I have to do is
dump the array in whatever Worksheet and format I need. It all runs in
the background and is very fast. Not bad considering my workstation is a
Mac running an emulated PC.

John
Project MVP

 

[Rod Gill]

The fastest response you can get is reading data directly from the .mpp file
using oledb. In one of Project's program folders you will find a file called
prjoledb.htm It has details and examples of code for you. Note each version
of Project has different capabilities here, but they are all read only.

 

[egun]

Thank you all for your replies! I have used the 'create an array and dump'
method before, and it is really fast. I'll give that a try, and look at some
of the other suggestions too, for future reference.

I appreciate your help.

Eric