1

I was writing a simple script in VBA for batch updating project info posted on Project Web App. I've stumbled on a problem which seems unsolvable to me though, opening projects programatically through FileOpenEx method is working only on a fraction of them.

I found out when opening files manually through MS Project's file explorer that most of the projects are hidden until you pick "Show list of all projects" option, which appears on the top of the list of .mpp files to choose from. After picking this option, all projects unavailable to open through macro show up after a few seconds of loading.

I have no clue on how to tackle this problem, maybe connecting to the SharePoint database in a different, more direct way would help? I'm posting below a few lines of code responsible for opening the projects.

    Dim myMPP As MSProject.Application
    Set myMPP = CreateObject("Msproject.Application")
    myMPP.Visible = True
    
    Dim projectName, fpath As String
    projectName = "ExampleProject"
    fpath = "<>\" & projectName
    myMPP.FileOpenEx Name:=fpath, ReadOnly:=True, openPool:=pjDoNotOpenPool
    Set mpp = myMPP.ActiveProject
Ken White
  • 123,280
  • 14
  • 225
  • 444
Ryszard Andrzejewski
  • 21
  • 2
  • You might try putting the `FileOpenEx` in a loop to try opening the files repeatedly (with a limit, of course) to see if it's a matter of needing more time to "see" available projects. What is the error you get when it fails? – Rachel Hettinger Apr 21 '22 at 01:34
  • Basically, it skips the whole method without opening the project file. As soon as I open the faulty project manually by choosing it from the extended list, it's working as it should on the second try because it can be picked from the shorter list. – Ryszard Andrzejewski Apr 21 '22 at 03:07
  • Something about the names? Or have you brought these files over from another computer and have an ID problem? – Solar Mike Apr 21 '22 at 05:23
  • Files im trying to access are published on PWA. Names of files are correct as they're fetched from OData feed, the problem lies within the desktop client I guess. Every time I open the file dialog containing projects 75% percent of them are not there, but doubleclicking first item in the file dialog fetches the rest until the dialog is closed. I have no clue if that's related to outdated project version or some weird PWA settings – Ryszard Andrzejewski Apr 21 '22 at 05:47
  • @RyszardAndrzejewski I am able to replicate this issue and have not yet found a way around it. The last time I automated opening files from project server was more than 10 years ago... Looking at that code I used the format: "\filename" where dsn was the name of an odbc connection I had set up on my computer. – Rachel Hettinger Apr 21 '22 at 17:34
  • And from the [docs](https://learn.microsoft.com/en-us/office/vba/api/project.application.fileopenex): The Name parameter can contain a file name string or an ODBC data source name (DSN) and project name string. The syntax for a data source is \Projectname. The less than (<) and greater than (>) symbols must be included, and a backslash ( \ ) must separate the data source name from the project name. DataSourceName itself can either be one of the ODBC data source names installed on the computer or a path and file name for a file-based database. – Rachel Hettinger Apr 21 '22 at 17:34
  • 1
    The workaround which worked for me was opening MS Project manually before running any macros. Thank You for help. – Ryszard Andrzejewski Apr 22 '22 at 11:46

1 Answers1

1

Solution to the problem was opening MS Project manually before running any macros. Instances of Project opened programatically seem to be able to operate only on cached projects.

Ryszard Andrzejewski
  • 21
  • 2