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

Question

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:

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?

Does this happen with every project server file? How about with .mpp files saved locally? — Rachel Hettinger, Jul 17 '18 at 18:32
@RachelHettinger yes all server files. No, local .mpp files are not affected. — Greg Viers, Jul 18 '18 at 17:33

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

0

Here are some suggestions to try:

Set projapp.Visible = True to help debug issues.
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.
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.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jul 17 '18 at 15:22

Rachel Hettinger

7,927
2
21
31

Regarding 1, I saw no change adding that line. – Greg Viers Jul 17 '18 at 17:50
I rewrote the code to use CreateObject instead of New, and got the same error. – Greg Viers Jul 17 '18 at 17:51
I made the change you mentioned in (3) to qualify references, with no change. – Greg Viers Jul 17 '18 at 17:51
Unless there is a pop-up box when you open Project manually (e.g. selecting a Profile) the only difference between using CreateObject and opening manually & using GetObject would seem to be time; so try using Excel's Wait method to pause a second or so before trying to open the file. – Rachel Hettinger Jul 17 '18 at 18:31
I added a 20 second wait - should be more than long enough since the plan opens in about 4. Same error. – Greg Viers Jul 17 '18 at 18:35

score 0 · Answer 2 · answered Aug 09 '18 at 13:52

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")

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

2 Answers2