Get list of enterprise projects

[Jason-W]

Hi,

This is what I'm trying to do:

When I go to File->Open I can see the the list of projects
on the server (I have an Admin account). I want to get
the project names that appear there loaded into an array
in Visual Basic so I can open them later to manipulate all
of them and/or export data into Excel.

I know the code I need to manipulate the projects, but I
don't know how to get the list of active Project files
from the Server. Does anyone know what methods /
functions I need to use?

Thanks,
Jason-W

 

[Gérard Ducouret]

Hello Jason,
You can adapt the following VBA procedure...
Hope this helps,

Gérard Ducouret

Dim cn As Object
Dim rst As Object
Dim rst_Task As Object
Dim Cmd As Object

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

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

 

[Jason-W]

Hi,

I didn't realize that connecting to SQL and using
DSN 'stuff' would be involved. Although I understand the
principals of what the code is doing (and seems straight
foward enough) I'll have to go inquire with some of our IT
admins on what changes need to be made.

Thanks for the sample code. It seems like a very good
starting point

--Jason

-----Original Message-----
Hello Jason,
You can adapt the following VBA procedure...
Hope this helps,

Gérard Ducouret

Dim cn As Object
Dim rst As Object
Dim rst_Task As Object
Dim Cmd As Object

Const DSN = "LocalServer" '"NomDSN" = ""
Const STR_USER_IDENT

= "Administrateur" '"NomIdentificationDSN" = ""

Const STR_PASSWORD_IDENT

= "" '"PassWord" = ""

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

Hi,

This is what I'm trying to do:

When I go to File->Open I can see the the list of projects
on the server (I have an Admin account). I want to get
the project names that appear there loaded into an array
in Visual Basic so I can open them later to manipulate all
of them and/or export data into Excel.

I know the code I need to manipulate the projects, but I
don't know how to get the list of active Project files
from the Server. Does anyone know what methods /
functions I need to use?

Thanks,
Jason-W

.