将MSProject信息复制到Excel [英] Copying MSProject information to Excel

问题描述

我有一个启用宏的Excel工作簿，该工作簿将打开一系列MSProject文件，将过滤器应用于特定列，并将可见范围的一部分复制到Excel.复制操作的代码如下:

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.

我希望将这种迭代方法替换为我将在Excel VBA中使用的一组标准操作:定义first&最后一行，然后对我想要的每一列使用一个复制操作.这样可以大大减少完成任务所需的复制操作的数量，从而可以提高速度.

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.

在Excel VBA中，我想要的代码看起来像这样，已经定义了最后一行:

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)

我知道如何在Project中找到最后一个可见的任务，但是不熟悉范围选择.有人可以填补空白吗?

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

谢谢！

推荐答案

您当前的方法采用了一种合理的方法，因此不要更改方法，而应尝试改善性能.

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

当前代码中最慢的部分不是您要一个接一个地循环执行任务，而是要逐个单元地写入Excel.您可以采取的第一步是一次写入单个任务的所有数据:

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)

一旦您对此感到满意，就可以一次写入数据块.为此，将数据存储在二维数组中，仅在完成任务循环后才将其写入Excel.(请注意，如果您有成千上万的任务，则可能需要将数据写成较小的块.)

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.)

此外，请确保已在Excel中关闭了计算.由于可以关闭屏幕更新，因此可以提高性能.只需确保在代码完成后重置两个应用程序设置即可(即使错误完成也可以重置).

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).

最后一个技巧是，避免将变量命名为与对象相同的名称(例如，名为Task的 Task 对象).

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

这篇关于将MSProject信息复制到Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！