Can't read MS Project 2013 tasks into Excel 2016 VBA

Question

I'm trying to write an Excel 2016 VBA macro that will read the data from a Microsoft Project 2013 Summary Project (Its a project that contains all my active projects) and summarize the data into a summary sheet in Excel.

Here is the code I have so far:

Sub ExtractFromMsProject(ProjectPath As String)

  Dim ProjectApp As MSProject.Application
  Dim EachProject As MSProject.Project
  Dim ProjectFile As MSProject.Project
  Dim SubProjectFile As MSProject.SubProject
  Dim SubProjectIndex As Long

  On Error Resume Next
  Set ProjectApp = GetObject(, "MSProject.Application")
  If ProjectApp Is Nothing Then
    Set ProjectApp = New MSProject.Application
  End If
  ProjectApp.DisplayAlerts = False
  For Each EachProject In ProjectApp.Projects
    If ProjectPath = EachProject.FullPath Then
      Set ProjectFile = EachProject
      Exit For
    End If
  Next
  If ProjectFile Is Nothing Then
    If ProjectApp.FileOpenEx(Name:=ProjectPath, ReadOnly:=True) Then
      Set ProjectFile = ProjectApp.ActiveProject
    Else
      MsgBox "Unable to open the source project file '" & ProjectPath & "'."
      Exit Sub
    End If
  End If
  ProjectApp.Visible = True
  For Each SubProjectFile In ProjectFile.Subprojects

  Next
' For SubProjectIndex = 0 To ProjectFile.Subprojects.Count - 1
'   Set SubProject = ProjectFile.Subprojects(SubProjectIndex)
' Next

  ProjectApp.FileCloseEx pjDoNotSave
  ProjectApp.Quit

End Sub

It works great until I get to:

For SubProjectIndex = 0 To ProjectFile.Subprojects.Count - 1

At that line, I get an error message:

"Automation Error. Library not registered"

I've tried to do some Google searching, but everything I found is for an older version of Office/Project.

Any help would be greatly appreciated.

You've declared SubProject as a Task object, not a SubProject object. Also, SubProject is a keyword; choose a non-keyword for your variable names. That said, try 'For Each SubProj in ProjectFile.SubProjects'. — Rachel Hettinger
I fixed the declaration of SubProject from a Task object to a SubProject object as well as updating the variable name of SubProject to SubProjectFile per your suggestion and I'm still getting the same error. Any other ideas? — Kyle
Take a look at this page, it seems to be relevant: Removing Outdated References to Project... — Rachel Hettinger

JackW327 JackW327 · Accepted Answer · 2016-04-22T23:30:10

Subproject has been a type for a while. It is a member of Subprojects. So, you can't iterate through the Subprojects collection with a task object.

I would have expected either a type error or an issue with the object being set to "None". That combined with the error message you are getting is making me think that you may have an older version of the MS Project library referenced by your VBA environment. Under Tools > References what you need is the "Microsoft Project 15.0 Object Library".

One other thing. The tasks in the summary project are the inserted project summary tasks. I have never been able to iterate through all the tasks by trying to iterate through the summary project tasks unless the sub projects are unlinked from their source (LinkedToSource = False). Depending on what you are doing it may be just as efficient to iterate through a File System Object for the folder, open each project, extract your data, next ... To do that you will need to include the Microsoft Scripting Runtime in the references too.

Hope that helps. If this answers the question please flag it as such.

Can't read MS Project 2013 tasks into Excel 2016 VBA

1 Answers