none
Excel 2007 extract data from MS Project 2007 RRS feed

  • Question

  • I recently upgraded from MS Project 2003 to 2007.  Now my excel 2007 tool does not work.  In Excel I used to pull data from MS Project 2003 with an ODBC connection and a SQL statement.  Below is the connect string I used in my Excel macro.  Now that MSP2007 does not have ODBC, How do I  accomplish something similar from Excel 2007 macro trying to extract data from MS Project 2007?

    Dim Conn As adodb.Connection
        Dim vWasFileName As String
       
        vWasFileName = Application.GetOpenFilename(filefilter:="Project Files (*.mpp),*.mpp", Title:="Find 'WAS' IMS Project file to report on")
        If Dir(vWasFileName) <> "" Then
            Set Conn = New adodb.Connection
            Conn.connectionstring = "Provider=Microsoft.Project.OLEDB.11.0;" & "PROJECT NAME=" & vWasFileName
            Conn.Open
            If Err Then
                MsgBox "Could not open file" & vbCrLf & "Error: " & Err.Description, vbCritical + vbOKOnly
            Else
                WasFilename.Value = vWasFileName
                Conn.Close
            End If
        Else
            MsgBox "WAS file input requried."
        End If
    End Sub

    Thanks...Dean

    Wednesday, September 28, 2011 5:57 PM

Answers

All replies

  • Unfortunately Proejct 2007 and 2010 no longer support oledb access of .mpp files. You have to open the file and read the data into Excel that way.

    Alternatively you need a macro in Project to save data you want to report on to a database then read into Excel from the database.


    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management
    Wednesday, September 28, 2011 7:45 PM
  • Rod,

     

    Thanks for the reply.  Just to clarify, are you suggesting that the way I have to get data out of MSP2007 now is with the "export" function of project? 

     

    Thanks,

     

    Dean

    Thursday, September 29, 2011 6:08 PM