excelvbams-projectexcel-import

MS Project get Taskdependencies property via Excel VBA


I am currently creating a macro in Excel VBA to import data from a MS project document.

I included all properties that I want to import in my code but couldn't find the correct for TaskDependencies (i, 15). This property should include "True" if a Predeccessors OR Successors is true - "False" if both are false.

I receive a Runtime Error '438' Object does not support this property or method

Is it maybe the wrong property? Can somebody help me out here?

https://learn.microsoft.com/en-us/office/vba/api/project.task - here are all properties be found afaik.

Thanks in advance

Best regards CKay


Sub ImportDataFromProject()
    Dim objProject As Object 'MS Project application
    Dim objTask As Object 'Task object
    Dim strFileName As String 'File name
    Dim i As Long 'Counter
    Dim fd As FileDialog 'File dialog object

    'Create file dialog object and set properties
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    fd.Title = "W�hlen Sie eine Project-Datei aus"
    fd.Filters.Clear
    fd.Filters.Add "Project-Dateien", "*.mpp"

    'Show file dialog and get file name
    If fd.Show = -1 Then 'User clicked OK
        strFileName = fd.SelectedItems(1) 'Get first selected item

        'Create MS Project instance and open file
        Set objProject = CreateObject("MSProject.Application")
        objProject.FileOpen strFileName

        'Loop through tasks and copy data to Excel
        i = 4
        For Each objTask In objProject.ActiveProject.Tasks
            If Not objTask Is Nothing Then 'Skip blank rows
                Cells(i, 1) = objTask.ID
                Cells(i, 2) = objTask.Name 'Copy task name to column A
                Cells(i, 3) = objTask.Start 'Startdatum
                Cells(i, 4) = objTask.Finish 'Enddatum
                Cells(i, 5) = objTask.Predecessors 'Vorg�nger
                Cells(i, 6) = objTask.Successors 'Nachfolger
                Cells(i, 7) = objTask.Summary ' Sammelvorgang
                Cells(i, 8) = objTask.Milestone 'Meilenstein
                Cells(i, 9) = objTask.Duration 'Dauer
                Cells(i, 10) = objTask.PercentComplete 'Fertigstellung
                Cells(i, 11) = objTask.ConstraintType 'Einschr�nkungstyp
                Cells(i, 12) = objTask.Active 'Aktive Aktivit�ten
                Cells(i, 13) = objTask.FreeSlack 'Freie Puffer
                'Cells(i, 14) = objTask.X
                Cells(i, 15) = objTask.TaskDependencies 'Verknüpfungen
                Cells(i, 16) = objTask.Critical ' Kritisch
                Cells(i, 17) = objTask.ConstraintDate 'Einschr�nkungstermin
                
                i = i + 1 'Increment counter
            End If
        Next objTask

        'Close MS Project file and application
        objProject.FileClose False
        objProject.Quit pjDoNotSave

    Else 'User clicked Cancel or closed the dialog

        MsgBox "Keine Datei ausgew�hlt."

    End If
    End Sub


Solution

  • Half of an answer, more of a way around:

    Since TaskDependencies is the only field which can be filled without pulling it from the project, I decided to create a If-Statement directly in the cells to create a way around:

    If(or(E4>0;F4>0);TRUE)
    

    E= predecessors F= Successors 4= where my list starts

    Since "TaskDependencies gets a collection of dependent (predecessor and successor) tasks", this solution is viable for me. Not the cleanest, but it'll do.