The code below will do it. The code is intended to run in a standard module. The code assumes there is a worksheet with a codename of Resources. It blanks out any existing data (leaves formatting) and writes headers for the three columns.
It prompts the user to locate the Project file to use. It has to open the file to do the work, then it closes it.
It retrieves the total work for all resources, using the three columns you listed above. I didn't understand what you meant by the following, because it didn't seem to sync with the other part of your description and I don't know what screen you are looking at when you do this:
If what I have provided is not what you're looking for then you will need to provide a lot more detail, including screen shots of what your description refers to, as I suggested in post #2. Include the sample data from your project file, and an image of what you want the result to look like in your Excel file.
Modify to suit.
VBA Code:
Option Explicit
Dim MSP As Object ' Project application
Private Sub ExtractResources()
Dim SourceProject As Object
Dim i As Long
Set MSP = CreateObject("MSProject.Application")
Set SourceProject = OpenProject()
With Resources
.Activate
.Cells.ClearContents
.Cells(1, "A") = "Enterprise Unique ID"
.Cells(1, "B") = "Name"
.Cells(1, "C") = "Work (Hours)"
For i = 1 To SourceProject.Resources.Count
.Cells(i + 1, "A") = SourceProject.Resources(i).EnterpriseUniqueID
.Cells(i + 1, "B") = SourceProject.Resources(i).Name
.Cells(i + 1, "C") = SourceProject.Resources(i).Work / 60 ' work is in units of minutes, convert to hours
Next i
End With
MSP.FileExit ProjectConstants.pjDoNotSave
End Sub
' Open Project and prompt the user for a Project file to open
' Return value is the Project object
' Project is left open, minimized
'Public Function openProject(prompt As String) As Project
Public Function OpenProject() As Object ' Project
' Create the Project application object. This prevents errors if
' executing this code again in the same run of Excel as opposed to
' simply using ExcelExport_Apps.MSProject
' Set ExcelExport_Apps.MSP = CreateObject("ExcelExport_Apps.MSProject.Application")
'MsgBox prompt:=prompt, Title:="Open MS Project File"
MSP.FileOpen
On Error GoTo NoProject
Set OpenProject = MSP.activeproject
On Error GoTo 0
MSP.AppMinimize
Exit Function
NoProject:
MSP.FileExit
Set OpenProject = Nothing
If Err.Number = 424 Then
' File not found
MsgBox "No Project selected, exiting."
Else
MsgBox "Unexpected error attempting to open Project file" & vbCrLf & _
"Exiting." & vbCrLf & _
Err.Number & " " & Err.Description
End If
Exit Function
End Function
There is another module called ProjectConstants with this code:
VBA Code:
Option Explicit
Public Const pjDoNotSave = 0
