VBA-按周分配从MS Project提取剩余工时 [英] VBA-Extracting from MS Project Remaining Work by Assignment by Week

问题描述

在查看可能的PjAssignmentTimscaledData选项(here)时，我需要提取的缺少的是剩余的工作字段。有没有人能够想出如何使用VBA根据剩余工作提取每周作业？

我的VBA中有一个部分是：

ass.TimeScaleData(tsk.Start，tsk.Finish，pjAssignmentTimscaledActualWork，pjTimscaleWeek)

但我想我可以取代

pjAssignmentTimscaledActualWork

与

pjAssignmentTimscaledRemainingWork

但这不起作用。

我是在要求一些根本不存在的东西，还是以一种倒退的方式看待整个操作？

推荐答案

不幸的是，pjAssignmentTimescaledRemainingWork不存在(这很烦人)。若要获得时间分段的剩余工作，您需要将从pjAssignmentTimescaledWork获得的值减去从pjAssignmentTimescaledActualWork获得的值。以下是一些示例代码，用于获取所选任务的时间分段剩余工时：

Public Sub GetTimeScaledRemainingValues()

    Dim a As Assignment
    Dim t As Task
    
    Dim totalTSVs As TimeScaleValues
    Dim actualTSVs As TimeScaleValues
    
    Dim totalTSV As TimeScaleValue
    Dim actualTSV As TimeScaleValue
    
    Dim remainingValue As Double
    
    For Each t In Application.ActiveSelection.Tasks
        For Each a In t.Assignments
            'get the total TSV values and store in a variable
            Set totalTSVs = a.TimeScaleData(t.Start, t.Finish, pjAssignmentTimescaledWork, pjTimescaleWeeks)
            'get the actual TSV values and store in a variable
            Set actualTSVs = a.TimeScaleData(t.Start, t.Finish, pjAssignmentTimescaledActualWork, pjTimescaleWeeks)
            
            'Loop through the total TSVs and try to find and substract the actual values
            For Each totalTSV In totalTSVs
                'Use this loop to find the actual TSV that has the same start date as the total TSV value we are currently looking at. These values will cover the same period
                For Each actualTSV In actualTSVs
                    If actualTSV.StartDate = totalTSV.StartDate Then
                        'If the actual value is zero the property returns an empty string, so we have to check for this because we cannot subtract an empty string from a number, it will cause a VBA error.
                        If actualTSV.Value <> "" Then
                            'subtract the actual value from the total value to get the remaining
                            remainingValue = totalTSV.Value - actualTSV.Value
                        Else
                            remainingValue = totalTSV.Value
                        End If
                        
                        'the Value property of TSV returns in minutes. divide by 60 to get the remaining hours
                        remainingValue = remainingValue / 60
                        
                        Exit For
                    End If
                Next actualTSV
                
                'print out the remaining value information
                Debug.Print "There are " & remainingValue & " remaining hours of " & a.ResourceName & " on " & t.Name & " between " & totalTSV.StartDate & " to "; totalTSV.EndDate
                
            Next totalTSV
        Next a
    Next t

End Sub

以下是我的输出示例：

从2021年3月7日到2021年3月14日，安全工程师在创建安全需求可跟踪性矩阵(SRTM)方面还有16个小时的剩余工作时间

这篇关于VBA-按周分配从MS Project提取剩余工时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！