Loop error when importing from Excel to MS Project

Question

I have a tool that reads an Excel file and converts it into an MS Project project plan. It also prints a list of tasks (uniqueIDs) back into the spreadsheet, which are used to match the Excel / project tasks. This part works fine, I'm trying to update it so the Excel file can be used to update an existing plan.

The below code works fine for all but two project tasks and then the loop breaks. It also overwrites the first two tasks instead of updating only the matched task using the uniqueID.

 dim statements
 Set prApp = New MSProject.Application

    'If fileToOpen <> False Then

    prApp.FileOpen "c:\users\faizal\desktop\Project1.mpp"

    Set CurrProject = prApp.ActiveProject

    With CurrProject

        Set lshtProjStage2 = ActiveWorkbook.Worksheets("Project Stage - Gate 2")

        lshtProjStage2.Activate


        ' Default place to start.

        llngRowCounter = 10

        llngTaskCounter = 0

        lintBoldCellCount = 0


        lsCellContent = lshtProjStage2.Range("B10").Value

        lblnSkipAddTask = False


        lblnIndentNextCell = False


        lsPreviousCellContent = ""

        llngPerviousCellColour = -4142

        llngPerviousTaskIndentLvl = 0

        lblnPerviousCellBold = False


        lshtProjStage2.Range("G" & Trim(CStr(llngRowCounter))).Select

        lsTaskName = ActiveCell.Value


        lshtProjStage2.Range("C" & Trim(CStr(llngRowCounter))).Select

        start1 = ActiveCell.Value

        llngTaskCounter = llngTaskCounter + 1


        ' Going to loop through cells A10 until we reach "Service Readiness" in column B which is currently on row 28.

            Do While lsCellContent <> ""

            lsCellContent = lshtProjStage2.Range("B" & Trim(CStr(llngRowCounter))).Value

            lshtProjStage2.Range("B" & Trim(CStr(llngRowCounter))).Select



                If llngRowCounter >= 10 Then

                lsPreviousCellContent = lshtProjStage2.Range("B" & Trim(CStr(llngRowCounter - 1))).Value

                llngPerviousCellColour = lshtProjStage2.Range("B" & Trim(CStr(llngRowCounter - 1))).Interior.ColorIndex

                lblnPerviousCellBold = lshtProjStage2.Range("B" & Trim(CStr(llngRowCounter - 1))).Font.Bold

                End If


            lshtProjStage2.Range("C" & Trim(CStr(llngRowCounter))).Select

            start1 = ActiveCell.Value


            lshtProjStage2.Range("L" & Trim(CStr(llngRowCounter))).Select

            guid = ActiveCell.Value


            If start1 = "" Then

                lblnSkipAddTask = True

            Else

                lblnSkipAddTask = False

            End If

            c1 = 11


            For Each t In CurrProject.Tasks

                If lblnSkipAddTask = False Then

                ' find the excel file unique id in the Project file based on uniqueid and update

                ' actual start, actual finish, perecent complete and duration

                    If t.UniqueID = guid Then

                    t.Name = start1

                    't.Finish = fin


           'need to exit the code once found to the next line in the excel sheet

            Exit For

                    End If

                End If

            Next t

            c1 = c1 + 1

            llngRowCounter = llngRowCounter + 1

            lsCellContent = lshtProjStage2.Range("B" & Trim(CStr(llngRowCounter))).Value

            ' Check if cell content is <> "" if so carry on else goto Column B.

            If Trim(lsCellContent) = "" Then

                lsCellContent = lshtProjStage2.Range("C" & Trim(CStr(llngRowCounter))).Value & " "

            End If

            ' Check if in column B of this row we have "Service Readiness"

            If lshtProjStage2.Range("C" & Trim(CStr(llngRowCounter))).Value = "Service Readiness" Then

                lsCellContent = ""

            End If

            Loop


    CurrProject.SaveAs

    MsgBox ("Test Text" & CStr(datecount))

CleanExit:

    Exit Sub

    End With

End Sub

Rachel Hettinger Rachel Hettinger · Accepted Answer · 2015-09-23T21:02:06

Look carefully at the Task Unique IDs (or as you call them GUIDs) that you have stored in column L. Are the Task Unique IDs for the first two tasks in there twice?
Only the task name is updated in this code and it is updated with the value from column C (start); that looks to be incorrect.
Your code indentation is off which can lead to the code appearing to do something it's not--fix your indentation.
The placement of the End With statement is wrong--move it just after the Loop line.
If you want to update actual values be sure to use the task properties t.ActualStart and t.ActualFinish.

Loop error when importing from Excel to MS Project

1 Answers