With VBA from Excel: Open Project, extract resource list and copy it to a worksheet, close project.

[Tony]

Well, I'm stumped again and in need of serious assistance.

I'm building an Excel add-in for creating project plans, which then are
exported to MS-Project for tracking. But I need to have the resource list
in an Excel worksheet before the plan can be built. And that's where I'm in
trouble. The rest of the add-in actually works, including the piece that
exports the plan to MS-Project (2003). But I'm stuck on the part that
automates the retrieval of the resource list from Project.

The code below shows how far I've been able to get on my own. It actually
works, every second time that it runs, and it fails every second time that
it runs. I get a message that the application server doesn't exist or is
not available. But I can't figure out what's going on, and the available
documentation is, well, excessively challenging for me (read that as I don't
know Project's object model and I can't find readable documentation). Can
somebody here straighten me out? I would be entirely grateful

Tony


Sub GetResourceList()
Dim objMSProject As MSProject.Application
Dim R As Resources
Dim curPath As String
Dim fileToOpen As Variant
Dim thisExcel As Excel.Application
Dim Temp As Long
Dim Names As String
Dim rSheet As Worksheet

curPath = CurDir

fileToOpen = Application.GetOpenFilename("Microsoft Project Files
(*.mpp), *.mpp")

If fileToOpen <> False Then

Set thisExcel = Excel.Application

Set objMSProject = New MSProject.Application
objMSProject.Visible = True
objMSProject.FileOpen Name:=fileToOpen
objMSProject.Projects(fileToOpen).Activate

Set R = ActiveProject.Resources

For Temp = 1 To R.Count
Names = R(Temp).Name & ", " & Names
Next Temp
Names = Left$(Names, Len(Names) - Len(ListSeparator & " "))

End If

objMSProject.DisplayAlerts = False
'objMSProject.FileCloseAll pjDoNotSave
'FileExit pjDoNotSave
Quit savechanges:=pjDoNotSave
'objMSProject.Quit pjDoNotSave

Set objMSProject = Nothing
thisExcel.Visible = True
ChDir curPath

MsgBox Names

End Sub

 

[Tony]

My thanks to Scott Button for a pointer to the fix for this. Just in case
anyone here comes across the problem, here's the pointer.

http://www.highdots.com/forums/microsoft-project-developer/why-am-i-getting-and-2893172.html

It seems that when making a reference to anything inside Project, from VBA
code external to Project, the reference needs to be "fully qualified."

So, in my own code below,

Dim objMSProject As MSProject.Application
Set objMSProject = New MSProject.Application
...
Set R = ActiveProject.Resources
should read
Set R = objMSProject.Resources

and
Names = Left$(Names, Len(Names) - Len(ListSeparator & " "))
should read
Names = Left$(Names, Len(Names) - Len(objMSProject.ListSeparator
& " "))

Go figure.

Tony