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

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

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/micro...d-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


"Tony" <[email protected]> wrote in message
news:[email protected]...
> 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
>