Copying MSProject information to Excel

Question

I have a macro-enabled Excel workbook that opens a series of MSProject files, applies filters to a specific column, and copies portions of the visible range to Excel. The code for the copy action looks like this:

For Each Task In ActiveSelection.Tasks
    If Not Task Is Nothing Then
        TargetWS.Cells(Row, 3) = Task.PercentComplete / 100
        TargetWS.Cells(Row, 4) = Task.Name
        TargetWS.Cells(Row, 5) = Task.Start
        TargetWS.Cells(Row, 6) = Task.Finish
        TargetWS.Cells(Row, 7) = Task.BaselineFinish
        Row = Row + 1
    End If
Next Task

Essentially, I am looping through every row in the filtered range and copying each column one at a time. As you can imagine, this takes a long time.

My hope is to replace this iterative method with the standard set of actions I would use in Excel VBA: define first & last rows, then use one copy action for each column I want. This would greatly reduce the number of copy actions required to complete the task, which should provide a speed increase.

In Excel VBA, the code I want would look something like this, having defined the last row:

TargetWS.Range("A2:" & LastRow).Copy Destination:= (destination cells)

I know how to find the last visible task in Project, but am unfamiliar with range selection. Could someone fill in the gaps?

Thanks!

Answer 1

Your current method takes a sound approach, so instead of changing the method, try improving the performance.

The slowest part of your code right now isn't that you are looping through the tasks one-by-one, it's that you are writing to Excel cell-by-cell. The first step you can take is to write all data for a single task at one:

TargetWS.Range("C" & Row & ":G" & Row) = Array(Task.PercentComplete / 100, _
                                               Task.Name, Task.Start, Task.Finish, _
                                               Task.BaselineFinish)

Once you are comfortable with that, then you can move on to writing blocks of data at a time. To do this, store the data in a 2-dimensional array and only write it to Excel when you are done looping through the tasks. (Note, if you have many thousands of tasks, you may need to write the data in smaller chunks.)

Also, make sure you have turned calculation off in Excel. This will improve performance as can turning off screen updates. Just make sure you reset both application settings when your code is completed (even it if finishes with an error).

One last tip, avoid naming variables the same as objects (e.g a Task object named Task).