none
Is there a VBA command that can fetch me the list of projects in my working store? RRS feed

  • Question

  • Is there a VBA command that can fetch me the list of projects in my working store?  I am using Project Professional 2007.

    I would like to get this list, then perform an action on the project (e.g., Open).


    \Spiro Theopoulos PMP, MCITP. Montreal, QC (Canada)

    Tuesday, June 5, 2012 6:03 PM

Answers

All replies

  • Hi there,

    I dont believe there is a VBA method available but you could call the PSI from VBA to do this. Example below:

    http://social.technet.microsoft.com/Forums/pl-PL/project2010custprog/thread/465a4f45-6557-4325-b0cc-6a43e6bd41e4

    use the workingstore rather than the publishedstore.

    Thanks

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com

    Tuesday, June 5, 2012 8:54 PM
    Moderator
  • An easier way is to use OLEDB to read the project data from the reporting db. Reading from the other dbs is not supported by Microsoft as they reserve the right to change the db schema at any time.

    Straight from my book:

    Sub LoopThroughAllProjects()
    Dim Conn As New ADODB.Connection
    Dim rs As ADODB.Recordset
    'Create Connection to the .mdb file
    	Set Conn = New ADODB.Connection
    	Set rs = New ADODB.Recordset
    	Conn.ConnectionString = "Provider=sqloledb;" _
    		& "Data Source=YourSqlServerName;" _
    		& "Initial Catalog=YourReportingDatabaseName;" _
    		& "Integrated Security=SSPI;"
    	Conn.Open
    
    'Open Recordset with all projects in. 
    	rs.Open "Select ProjectName " _
    		& "FROM dbo.MSP_EpmProject_UserView " _
    		& "WHERE (ProjectPercentComplete < 100) " _
    		& "ORDER BY ProjectName" , Conn
    		
    'List projects in the Immediate Window
    	Do Until rs.EOF
    	    Debug.Print "Project Names:"
                Debug.Print rs!ProjectName
    	    rs.MoveNext
    	Loop
    
    'Tidy up
    	rs.Close
    	Conn.Close
    End Sub

    You need to add a reference to the Microsoft ActiveX Data Objects 2.8 Library and obviously edit teh connection string to use your server and reporting db names.


    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management



    Sunday, June 10, 2012 1:12 AM
    Moderator
  • Hi Spiro,

    All answers so far asume you are using Projet Server, which is not obvious from your question. To retrieve all projects in a folder use the Dir function. The exact way how to uses well explained in the help.

    Greetings,

    Sunday, June 10, 2012 12:36 PM
    Moderator
  • Jan,

    I tried using the Dir function as described in the VBA help, (i.e. successive calls with no path will cycle through the files), and it didn't work. The first call (i.e. with the path), gave a null and each successive call kept giving the second file name, over and over.

    And I agree there is no mention of using Project Server in the original post. But I do wonder what Spiro means by "working store".

    John

    Sunday, June 10, 2012 3:17 PM
  • I agree, no mention of Project Server but I assumed "Working store" was referring to the Project Server Draft database..

    Hopefully now there are options with or without Project Server :)

    Paul 


    Paul Mather | Twitter | http://pwmather.wordpress.com

    Sunday, June 10, 2012 4:12 PM
    Moderator
  • Hi John,

    Well, I made it work several times (not in 2010 I admit but in 2000, 03 and 07). There is one condition however that is not explicitly mentioned in the help. In the first call you have to give the full folder address. That means that if you have given a server address a symbolic name like Y:\ that will not work, you need the full address like \\ICTRA03\Base\Projects\.

    It also doesn't work whe the files are under sharepoint.

    Greetings,

    Sunday, June 10, 2012 6:29 PM
    Moderator
  • Jan,

    I had Project 2010 open when I tested it a couple days ago. I did not try earlier versions. My path name is straightforward:

    C:\Users\John\Documents\*

    I wonder if this is another glitch in VBA for Office 2010 apps.

    Update:

    I found the problem. The stupid help file for VBA is wrong, once again. For Project 2010 running under Windows 7, the VBA help topic for the Dir Function states:

    To iterate over all files in a folder, specify an empty string:

    Dir("")

    Well, that's wrong. It works fine if you give no argument to the function (i.e. Dir by itself). And that's what the VBA help file in previous version states.

    Foiled once again.

    John


    Sunday, June 10, 2012 8:53 PM
  • Hi John,

    You are missing a dot.

    The following works even in 2010:

    dir("D:\Cursussen MSP\Project2010\En_V2010\*.*")

    Or

    dir("D:\Cursussen MSP\Project2010\En_V2010\*.mpp")

    That indeed is not explained in the help!

    Greetings,

    Monday, June 11, 2012 11:19 AM
    Moderator
  • Jan,

    Yes I understand. I was just showing an illustration of the path. My main point was the misinformation in the "help" file. Indeed the *.mpp extension should be used to find the list of Project files in the given folder path.

    John

    Monday, June 11, 2012 2:21 PM
  • That is correct, I am trying to read the project list from the working store (not the published store) of Project Server 2007.

    \Spiro Theopoulos PMP, MCITP. Montreal, QC (Canada)


    Monday, June 11, 2012 2:44 PM
  • Hi there,

    Then calling the PSI from VBA using the example in the link provided as a starter is certainly one option for you :)

    Thanks

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com

    Monday, June 11, 2012 2:48 PM
    Moderator