method 'fileopenex' of object '_MSProject' failed when automating MS Project from VBA

0
votes

I have an excel Macro which is supposed to open up MS Project and make a change to some custom fields and then close it again. Excel and Project are both version 2010, and the project is stored on MS Project Server 2010.

When I open MS Project manually and then detect it with VBA, everything works. But when I use VBA to open a new instance of Project (because I haven't manually opened MS Project), it throws an error:

enter image description here

Here are the two versions of the code:

Sub open_project_with_error()

    Dim projapp As MSProject.Application, prj As Project
    Set projapp = New MSProject.Application
    projapp.FileOpenEx Name:="<>\Name of my project", ReadOnly:=False
    Set prj = Projects("Name of my project")
  ' Do more things...
End Sub

Sub open_project_without_error()

    ' Manually open MS Project application before running this VBA
    Dim projapp As MSProject.Application, prj As Project
    Set projapp = GetObject(, "MSProject.Application")
    projapp.FileOpenEx Name:="<>\Name of my project", ReadOnly:=False
    Set prj = Projects("Name of my project")
  ' Do more things...
End Sub

Naturally, I expected that the problem was with the statement New MSProject.Application. However, this sub works perfectly, as expected:

Sub open_project_and_display_about()

    Dim projapp As MSProject.Application, prj As Project
    Set projapp = New MSProject.Application
    Debug.Print projapp.About()

End Sub

Which means that the error is only triggered by the combination of New MSProject.Application and New MSProject.Application.

Any way to resolve it?

2
vbams-projectms-project-server-2010
Does this happen with every project server file? How about with .mpp files saved locally? – Rachel Hettinger
@RachelHettinger yes all server files. No, local .mpp files are not affected. – Greg Viers

2 Answers

0
votes

Here are some suggestions to try:

  1. Set projapp.Visible = True to help debug issues.
  2. CreateObject opens and returns a reference to an object, so use it instead of manually opening Project and then using GetObject which just returns a reference to an already-opened object.
  3. Qualify references to MSProject objects, e.g. Dim prj as MSProject.Project and Set prj = projapp.Projects(....

FWIW I am not able to replicate this issue with project pro 2013 opening files from project server.

0
votes

I use this to automatically open MSP files from Excel, to check if the application is already open or not

Function GetX(Str As String) As Object
    Dim app As Object
    On Error Resume Next

    Set app = GetObject(, Str)
    If Err.Number <> 0 Then
        Set app = CreateObject(Str)
        app.Visible = False
        app.Clear
    End If

    Set GetX = app
End Function

then call function in module

Set ProjApp = GetX("MSProject.Application")
projapp.FileOpenEx Name:="<>\Name of my project", ReadOnly:=False
Set prj = Projects("Name of my project")