CODEWITHSUNDEEP
excelvbams-project
Nikolas
Nikolas

Reputation: 37

Opening all MSProject file in a directory - Excel VBA

I'm trying to open all MS Project files in a specific folder to extract some information of each one and transfer to excel.

Sub OpenProjectFiles()

Dim myMpp As MSProject.Application
Dim FileNameMpp As String, Folder As String


Folder = "C:\Users\nikolasqueiroz\Desktop\VBA Test"
Set myMpp = CreateObject("Msproject.Application")
FileNameMpp = Dir(Folder & "\*.mpp")

Do

myMpp.FileOpenEx Name:=FileNameMpp, ReadOnly:=True
FileNameMpp = Dir

Loop Until FileNameMpp = " "

End Sub

But when I try to open the .mpp file something goes wrong:

enter image description here

Does anyone know how can I fix this?

Upvotes: 1

Views: 511

Answers (1)

Rachel Hettinger
Rachel Hettinger

Reputation: 8442

First of all, the code you posted does not match the code shown in your screenshot. The screenshot code uses the syntax for opening files from Project Server--e.g. prefacing the name of the file with <>\.

Use this code instead:

Sub OpenProjectFiles()

Dim myMpp As MSProject.Application
Dim FileNameMpp As String, Folder As String

Folder = "C:\Users\nikolasqueiroz\Desktop\VBA Test\"
Set myMpp = CreateObject("Msproject.Application")
myMpp.Visible = True
FileNameMpp = Dir(Folder & "*.mpp")

Do While Len(FileNameMpp) > 0

    myMpp.FileOpenEx Name:=Folder & FileNameMpp, ReadOnly:=True
    FileNameMpp = Dir

Loop

End Sub

Notes:

  1. When automating another application, make it visible so you can see & respond to pop-up messages.
  2. The FileOpenEx method should be given the full path.
  3. Test the output of the Dir function at the beginning of the loop as there may not be any files.
  4. Adding the trailing slash in the Folder variable is simpler & cleaner than adding it later (in two places).

Upvotes: 2

Related Questions