Project2k3: Highlight completed task

I got a question on how to highlight the row of a completed task in MS Project to Red color.

To accomplish this, you need to write a VBA macro and run this macro after you finish updating your project.

Here is the macro that does the work.

Sub MarkCompletedTask()
    Dim startRow As Integer
    Dim tsk As task
    Dim today As Date
   
    startRow = 1
    today = Date
   
    SelectRow Row:=startRow, RowRelative:=False
    Set tsk = ActiveCell.task
    If tsk Is Nothing Then
        Exit Sub
    End If
   
    Application.ScreenUpdating = False
   
    Do While True
        If tsk.PercentComplete = 100 Then
            Font Color:=1
        Else
            Font Color:=16
        End If

        startRow = startRow + 1
        SelectRow Row:=startRow, RowRelative:=False
       
        Set tsk = ActiveCell.task
        If tsk Is Nothing Then
            Exit Do
        End If
    Loop
   
    Application.ScreenUpdating = True
End Sub

PS2003 : Save all Server Projects locally

How to automate the process of dumping all projects in Project Server database into MPP file.
Here is the VBA macro that does that. Paste the following macro into MS Project's VBA editor and run it.

Sub ExportAllProjects()
    Dim conn As ADODB.Connection
    Dim connStr As String
    Dim rs As ADODB.Recordset
    Dim sql As String
    Dim projName As String
    Dim savePath As String
    Dim outFile As String
   
    savePath = "c:\projectfiles\"
   
    connStr = "provider=sqloledb; data source=db_server_name; "
    connStr = connStr & quot;initial catalog=projectserver; "
    connStr = connStr & user id=sa; password=password"
   
    sql = "SELECT PROJ_PROJECT "
    sql = sql & " From MSP_PROJECTS "
    sql = sql & " WHERE PROJ_TYPE = 0 "
    sql = sql & " AND PROJ_ADMINPROJECT=0 "
    sql = sql & " AND PROJ_VERSION='Published' "

    Set conn = New ADODB.Connection
    conn.Open connStr
   
    Set rs = conn.Execute(sql)

   
    While Not rs.EOF
        projName = rs("PROJ_PROJECT") & ".Published"
        outFile = Replace(rs("PROJ_PROJECT"), "<>\", "") & ".mpp"
       
        FileOpen Name:=projName
        FileSaveAs Name:=savePath & outFile, FormatID:="MSProject.MPP"
        FileClose
        rs.MoveNext
    Wend
   
    rs.Close
    Set rs = Nothing
    conn.Close
    Set conn = Nothing
   
    MsgBox "done"
   
    Exit Sub
   
Err_Handler:
    MsgBox Err.Description
    If rs.State = adStateOpen Then rs.Close
    If conn.State = adStateOpen Then conn.Close
   
    Set rs = Nothing
    Set conn = Nothing
End Sub

PS2003: Update Other Fields in Timesheet view.

In the Web Access Timesheet View (Accessed via the Tasks top navigation bar), team member by default can only update the % Work Complete and Remaining Work column.

It is quite often that people ask whether they can update other field. One of the common requests is for team member to update Actual Start date.

Here is how you do it...


http://workspaces.gotdotnet.com/jyps