none
For Each Task in Tasks fails RRS feed

  • Question

  • I have a macro that exports activities to a csv.  A particular schedule there are 592 items in the ts collection. When the for loop hits ts(589), error 91 'Object variable or With block variable not set' is thrown. it's as though the 589th item is not a real task even though it is in task collection. Would could cause this?

        iResponse = MsgBox("Export visible/filtered tasks only", vbYesNoCancel)
        
        Select Case iResponse
        
        Case vbYes
            SelectAll
            Set ts = ActiveSelection.Tasks
        Case vbNo
            Set ts = ActiveProject.Tasks
        Case vbCancel
            Exit Sub
        End Select
        iTaskCount = ts.Count
        iLoop = 0
        'Open the output file
        OutputFileName = ActiveProject.Path & "\" & "ACT_" & Format(Now(), "yyyy-mm-dd_hhmmss") & ".csv"
        Open OutputFileName For Output As #1
        
     
        For Each t In ts
            If Not (Trim(t.Text23) = "" Or Trim(t.Text8) = "" Or Trim(t.Text9) = "" Or Trim(t.Text24) = "") Then
                sOutputLine = _
                    Chr$(34) & t.Text23 & Chr$(34) & "," & _
                    Chr$(34) & t.Text8 & Chr$(34) & "," & _
                    Chr$(34) & t.Text9 & Chr$(34) & "," & _
                    Chr$(34) & t.Text24 & Chr$(34) & "," & _
                    Chr$(34) & t.Text6 & Chr$(34) & "," & _
                    Chr$(34) & t.Text7 & Chr$(34) & "," & _
                    Chr$(34) & t.UniqueID & Chr$(34) & "," & _
                    Chr$(34) & t.Name & Chr$(34) & "," & _
                    Format(t.BaselineStart, "mm/dd/yyyy") & "," & _
                    Format(t.BaselineFinish, "mm/dd/yyyy") & "," & _
                    Chr$(34) & t.Text16 & Chr$(34)
                Print #1, sOutputLine
            End If
        Next t
        MsgBox OutputFileName & " successfully created!", vbInformation, "ACT File Export"

    Friday, October 28, 2016 1:52 PM

Answers

  • ColFrank,

    You haven't given us enough information about the offending task to properly diagnose.

    Empty rows in a project do get indexed in the tasks collection, and they will throw errors.  I would suggest that you enclose the current If-Then block inside another conditional: "If Not t is Nothing Then".

    Absent an obvious empty line, I would suspect a possible "external" task.  External tasks are created and inserted by Project into the tasks collection of the active project to represent linked tasks from other projects.  They possess some - but not most - attributes of regular tasks, and testing for a non-existing attribute can throw an error.  Interestingly, external tasks representing links between two projects are never visible when the two projects are opened together in a linked Master-Sub structure, though they are always present in the Tasks collection.  The ID numbers in an otherwise unfiltered task list simply jump over the offenders.

    Let us know how it goes. t

     

    • Proposed as answer by John - Project Saturday, October 29, 2016 12:11 AM
    • Marked as answer by ColFrankFitz Tuesday, November 1, 2016 8:12 PM
    Friday, October 28, 2016 4:49 PM

All replies

  • I found the offending "task", but I don't know how to fix:

    https://snag.gy/XheU4N.jpg

    Friday, October 28, 2016 2:07 PM
  • Can't delete the "task" either. This fails with same error

    Sub Del2446()
        ActiveProject.Tasks(589).Delete
    End Sub

    Friday, October 28, 2016 2:23 PM
  • ColFrankFitz,

    Your delete procedure doesn't work because you are deleting task ID 589 which isn't necessarily the same as the unique ID. This is how you would address the task

    ActiveProject.Tasks.UniqueID(589).Delete

    I suggest you try a couple of things. First, expand your whole file and then do a sort by Unique ID. If you have a rogue task in the file it will show up and you can manually delete it.

    If that doesn't work, your file may be corrupt. Save to XML and then re-open the XML in Project. Note, you will lose any custom formatting with this process.

    Hope this helps.

    Update: My bad, I completely missed the obvious. Most likely the task in question is simply a blank line which some users like to insert for "readability". Both Tom and Rod hit the nail on the head with their recommendation to insert the "If Not T is Nothing Then" statement.

    John



    Friday, October 28, 2016 4:27 PM
  • ColFrank,

    You haven't given us enough information about the offending task to properly diagnose.

    Empty rows in a project do get indexed in the tasks collection, and they will throw errors.  I would suggest that you enclose the current If-Then block inside another conditional: "If Not t is Nothing Then".

    Absent an obvious empty line, I would suspect a possible "external" task.  External tasks are created and inserted by Project into the tasks collection of the active project to represent linked tasks from other projects.  They possess some - but not most - attributes of regular tasks, and testing for a non-existing attribute can throw an error.  Interestingly, external tasks representing links between two projects are never visible when the two projects are opened together in a linked Master-Sub structure, though they are always present in the Tasks collection.  The ID numbers in an otherwise unfiltered task list simply jump over the offenders.

    Let us know how it goes. t

     

    • Proposed as answer by John - Project Saturday, October 29, 2016 12:11 AM
    • Marked as answer by ColFrankFitz Tuesday, November 1, 2016 8:12 PM
    Friday, October 28, 2016 4:49 PM
  • As Tom said. You need "If not t is nothing then" immediate after your for next statement.

    Rod Gill
    Author of the one and only Project VBA Book and VBA developer.
    www.project-systems.co.nz

    Friday, October 28, 2016 9:51 PM
    Moderator
  • That did the trick. Thanks
    Friday, November 4, 2016 9:29 AM