Sunday, May 21, 2006

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

Application.ScreenUpdating = True
End Sub


Thursday, May 11, 2006

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 = 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"

Set rs = Nothing
Set conn = Nothing

MsgBox "done"

Exit Sub

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


Wednesday, May 03, 2006

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