How automatically make Excel cells equal to Project cells?

user_16653283 0
Viewed 74

I have 2 documents, an Excel document and a Microsoft Project document. I want to add an "update" button in the Excel document that will make certain cells equal to certain cells in the Project file.

To some degree, I'm trying to do the opposite of this question: How can I make a macro in Excel workbook tab to open MS Project and copy reference cells

Here's the button macro I have so far: (In this example, one of the tasks/columns in the Project file is "ID", and the desired value I'm trying to make Cell (4,7) equal to exists on another column on the same ID row in the Project file. Having trouble figuring out how to do this.)

Sub Update()

projApp As MSProject.Application

Set ProjApp = GetObject(, "MSProject.Application")

projApp.Visible = False

projApp.FileOpenEx "C:\files\project.mpp"

ActiveWorkbook.Worksheets("Inputs").Cells(4,7) = projApp.Find Field:= "ID", Test:="equals", Value:="5748"

End Sub
ms-project vba excel
1 Answers

This code will open a Project file, search for a task by ID, then transfer data from that task to the Excel file. The important thing here is that the Find method returns True/False and not a reference to the found task.

Sub Update()

Dim projApp As MSProject.Application
Dim iOpened As Boolean

On Error Resume Next
Set projApp = GetObject(, "MSProject.Application")
If projApp Is Nothing Then
    Set projApp = CreateObject("MSProject.Application")
    iOpened = True
End If
projApp.Visible = True

projApp.FileOpenEx "C:\files\project.mpp"


If projApp.Find(Field:="ID", Test:="equals", Value:="5748") Then

    Dim t As MSProject.Task
    Set t = projApp.ActiveCell.Task
    ActiveWorkbook.Worksheets("Inputs").Cells(4, 7) = t.Finish
    
End If

projApp.FileCloseEx pjDoNotSave
If iOpened Then
    projApp.Quit pjDoNotSave
End If

End Sub

Notes:

  • This code does not depend on whether the Project application is already open or not (GetObject vs CreateObject).
  • Until the code works flawlessly, it's best to ensure the automated application is visible (Project, in this case).
user_10566320
Related