Jonathan Yong

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

Labels: project