Retrieving Data

After you set an object variable to the top-level object of an application, you can use the application's objects, properties, and methods to retrieve data. The following code searches a Microsoft Project file for information and returns the information to a range on Sheet1 in the active Microsoft Excel workbook.

Dim proj as Object
Dim nameoftask As String
Dim writetime As Integer, edittime As Integer, incorptime As Integer
Dim busytime As Integer, i As Integer, mecount As Integer
Dim othercount As Integer, timespent As Integer

Set proj = GetObject("c:\winproj\MyProject.mpp", "msproject.project")
mecount = 0
othercount = 0

For i = 1 To proj.tasks.Count
    If proj.tasks(i).Resources.Count > 0 Then
    With proj.tasks(i)
        If .Resources(1).Name = "Jane Smith" Then
            timespent = .Duration
            Select Case .Name
                Case "write"
                    writetime = writetime + timespent
                Case "incorp. tech review"
                    incorptime = incorptime + timespent
                Case "edit incorp.", "review/edit merged art"
                    edittime = edittime + timespent
                Case "art to designer", "hand-off to production"
                    busytime = busytime + timespent
                Case Else
                MsgBox "Error " & .Name
            End Select
        End If
    End With
    End If
Next i

With Worksheets("sheet1")
    .Range("B1") = writetime
    .Range("B2") = incorptime
    .Range("B3") = busytime
    .Range("B4") = edittime

    .Range("A1") = "Writing"
    .Range("A2") = "Adding Changes"
    .Range("A3") = "Other"
    .Range("A4") = "Editing"
End With

proj.Application.Quit