none
Loop Through All Files on Project Server 2010 Using VBA? RRS feed

  • Question

  • Hello -

    I'm having trouble figuring out how to use VBA to open the first file on Server, download some data from it into Excel, close the first file on Server, open the second file, download some data from it into Excel ... and do the same thing for all files saved on Server.

    I've got code that enables me to download the data from ONE file.  The VBA code then successfully closes the file, and then opens a file -- but it's the file it's already downloaded the first time around.  How do I use VBA to grab the next file (and the next and the next) on the Server list?

    I've researched this problem, but so far have not been able to find the solution.

    Help is greatly appreciated.

    Eve

    P.S.  I don't know SQL nor how to access the Reporting Database.


    Saturday, June 18, 2016 9:43 PM

Answers

  • Here you go, below is a sample of code from my VBA 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=YourDatabaseName;" _
            & "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
            
    'Open and Publish each project
        Do Until rs.EOF
            FileOpen "<>\" & rs!ProjectName
            Application.PublishProjectPlan
            FileClose pjDoNotSave
            rs.MoveNext
        Loop
    
    'Tidy up
        rs.Close
        Conn.Close
    End Sub
    

    You will have to edit the code to replace YourSqlServerName with your Project Reporting database Server Name and database name. Then Add a reference to the ActiveX Data Object 2.8.

    Also, code above re-publishes, so replace publish code with yours.


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    • Marked as answer by EBBECC Sunday, June 19, 2016 8:58 PM
    Sunday, June 19, 2016 2:10 AM
    Moderator

All replies

  • Here you go, below is a sample of code from my VBA 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=YourDatabaseName;" _
            & "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
            
    'Open and Publish each project
        Do Until rs.EOF
            FileOpen "<>\" & rs!ProjectName
            Application.PublishProjectPlan
            FileClose pjDoNotSave
            rs.MoveNext
        Loop
    
    'Tidy up
        rs.Close
        Conn.Close
    End Sub
    

    You will have to edit the code to replace YourSqlServerName with your Project Reporting database Server Name and database name. Then Add a reference to the ActiveX Data Object 2.8.

    Also, code above re-publishes, so replace publish code with yours.


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    • Marked as answer by EBBECC Sunday, June 19, 2016 8:58 PM
    Sunday, June 19, 2016 2:10 AM
    Moderator
  • Thank you for your quick reply!  I so appreciate it.

    I successfully located my SqlServerName, but have stumbled at "MyDatabaseName" -- I presume this is the name of the Reporting Database?  I tried the default (which I believe is) "ProjectService," but no dice.  Any pointers as to where I can locate the name of the Reporting Database?

    I appreciate your help.

    Eve

    P.S.  Before I posted my initial question, I bought your book.  Will arrive Monday!

    Sunday, June 19, 2016 12:23 PM
  • The only source is your database or Project Server administrator. They're the one who gave it its name.

    You're a scholar, a gentleman and a great judge of whiskey (as an old Irish acquaintance of mine used to say), enjoy the book and happy programming!


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Sunday, June 19, 2016 8:52 PM
    Moderator
  • Thanks, Rod -- I figured it out by establishing a connection to the Server Database through Excel!  (I was just about to delete my post when I saw you had responded.)

    I got the code to work -- thank you! -- but I had to remove the line regarding ProjectPercentComplete.  It didn't like that at all, and as I do a good job of archiving completed projects, it didn't impact the function of the routine.

    Can't wait to read your book.  And although I am a scholar/professor, I am no gentleman.

    ;)

    Thanks for all your help!

    Eve


    Sunday, June 19, 2016 8:58 PM