Need to open all projects from Project Server

[Scott Hillje]

I need to be able to cycle through all of the projects on project
server and open each one of them. Currently I have a work-around
process, where I have listed all of the project names in a text .ini
file. My macro opens the .ini file, takes in all of the project
names, then cycles through a FOR loop using the command:

FileOpen Name:=InputTextLine, ReadOnly:=True,
FormatID:="MSProject.MPP"

Rather than utilize a .ini file as a source for all of the Project
Names, which is manually intensive, is there a way that I can have the
macro automatically retrieve the list of all of the files on Project
Server? GetOpenFileName only works with local and network drives, and
does not work with Project Server.

Thanks,
-Scott

 

[Rod Gill]

Hi,

You can use PDS to get names. This works well, because if the logged on
person doesn't have authority to even see a they won't get the name in the
list. See www.msdn.microsoft.com for PDS sample code.

Best source of read only data in the Project Server db (published projects
only) are the MSP_VIEW tables. Try MSP_VIEW_PROJ_PROJECTS_ENT

--
Rod Gill
Project MVP
For Microsoft Project companion projects, best practices and Project VBA
development services
visit www.projectlearning.com/

 

[Gérard Ducouret]

Hello Scott,
Please try the following VBA procedure which read one SQL Server database
via ADODB to pick up some statistics about each project, then it writes a
..txt file.

Option Explicit
' - Nom du projet
' - Responsable : Manager
' - Auteur : Author
' - Nombre de tâches : NumberOfTasks
' - Date d'état : StatusDate
' - CBTP : .ProjectSummaryTask.BCWS
' - CBTE : .ProjectSummaryTask.BCWP
' - CRTE : .ProjectSummaryTask.ACWP
' - % avancement : .ProjectSummaryTask.PercentComplete
' - % travail : .ProjectSummaryTask.Work
Dim cn As Object
Dim rst As Object
Dim rst_Task As Object
Dim Cmd As Object

Const DSN = "LocalServer" '"NomDSN" = "xxx"
Const STR_USER_IDENT = "Administrateur" '"NomIdentificationDSN" =
"xxxx"
Const STR_PASSWORD_IDENT = "" '"PassWord" = "xxxxx"
'


Sub Lecture()
Dim sQuery As String
Dim sPathFile As String, i As Integer

' Ouverture de connection à SQL Server par l'intermédiaire d'un DSN
système
Set cn = CreateObject("ADODB.Connection")
cn.Mode = adModeRead 'adModeReadWrite (Lecture seule!)
cn.ConnectionString = "DSN=" & DSN & ";UID=" & STR_USER_IDENT & " ;PWD="
& STR_PASSWORD_IDENT & ";"
cn.Open

'1ere Requête :
' Récupération des enregistrements de la table des projets de la base de
données pointé par le DSN
sQuery = "SELECT * FROM MSP_PROJECTS"
Set rst = CreateObject("ADODB.Recordset")
Set rst = cn.Execute(sQuery, , adCmdText)

sPathFile = "c:\InfoProj.txt"
Open sPathFile For Output As #1
Print #1, ""

With rst
While Not .EOF
i = i + 1
Print #1, "********************"
Print #1, "Nom du projet : " & ![PROJ_NAME]
Print #1, "Nom de l'auteur : " & ![PROJ_PROP_AUTHOR]
Print #1, "Nom de la compagnie : " & ![PROJ_PROP_COMPANY]
Print #1, "Date début du projet : " & ![PROJ_INFO_START_DATE]
Print #1, "Date d'état du projet : " & ![PROJ_INFO_STATUS_DATE]
Print #1, "Date fin du projet : " & ![PROJ_INFO_FINISH_DATE]
Print #1, "Nom du Manager : " & ![PROJ_PROP_MANAGER]
Print #1, "Heure de début par défaut projet : " &
![PROJ_OPT_DEF_START_TIME]
Print #1, "Heure de fin par défaut projet : " &
![PROJ_OPT_DEF_FINISH_TIME]

' 2e Requête :
' Lignes à insérer pour calculer le nombre de lignes:
Dim rst_Project As Object
sQuery = "SELECT MSP_PROJECTS.PROJ_NAME as ProjectName, count
(MSP_TASKS.Proj_ID) AS NumberOfTasks FROM MSP_TASKS INNER JOIN MSP_PROJECTS
ON MSP_TASKS.PROJ_ID = MSP_PROJECTS.Proj_ID WHERE(((MSP_TASKS.TASK_ID) Is
Not Null)) AND MSP_PROJECTS.PROJ_ID = " & ![Proj_ID] & " GROUP BY
MSP_PROJECTS.PROJ_NAME, MSP_TASKS.PROJ_ID "

Set rst_Project = CreateObject("ADODB.Recordset")
Set rst_Project = cn.Execute(sQuery, , adCmdText)
If Not rst_Project.EOF Then
Print #1, "Nombre de tâches : " & rst_Project![NumberOfTasks]
End If
rst_Project.Close
Set rst_Project = Nothing

'3e Requête : données d'avancement
sQuery = "select TASK_ACWP, TASK_BCWP, TASK_BCWS ,
TASK_PCT_COMP, TASK_PCT_WORK_COMP FROM MSP_TASKS WHERE PROJ_ID = " &
![Proj_ID] & " AND TASK_OUTLINE_LEVEL = 0"
Set rst_Task = CreateObject("ADODB.Recordset")
Set rst_Task = cn.Execute(sQuery, , adCmdText)
If Not rst_Task.EOF Then
Print #1, "Coût réel du travail effectué : " &
rst_Task![TASK_ACWP]
Print #1, "Coût budgétisé du travail effectué : " &
rst_Task![TASK_BCWP]
Print #1, "Coût budgétisé du travail prévu : " &
rst_Task![TASK_BCWS]
Print #1, "Pourcentage achevé : " & rst_Task![TASK_PCT_COMP]
Print #1, "Pourcentage du travail achevé : " &
rst_Task![TASK_PCT_WORK_COMP]
'
End If
.MoveNext
Wend
rst_Task.Close
Set rst_Task = Nothing

.Close
End With
Set rst = Nothing
Print #1, "********************"
Close #1
MsgBox "Fini : " & i & " plannings dans la Base <<LocalServer>>", , "B58
: Gérard DUCOURET : PragmaSoft ®"

End Sub