Example 6 - Create an outlined schedule from an Excel spreadsheet

Public Sub CreateOutlinedSchedule()

' this function updates the schedule using data from a spreadsheet table

' it refers to sheet 2 of the current workbook

Dim numberoftasklines As Integer

Dim numberofsymbols As Integer

Dim x As Integer

Dim x2 As Integer

Dim TaskNumber As Integer

Dim earliestday As Date

Dim latestday As Date

Dim newdate As Date

Dim temp As Date

Dim outlineleve As Integer

'Create a new Milestones Professional schedule

Set objMilestones = CreateObject("Milestones")

'Start using the new schedule object

With objMilestones

 ' Activate Milestones Professional Schedule

.Activate

'initialize earliestday and latestday variables.  Use these later to set the schedule

'start and end dates

earliestday = "12/31/2099"

latestday = "1/1/1900"

'load in a template  If an error message occurs at this point it means that the template is

'not located in the user's default template A standard template named default.mtp is installed with Milestones Professional.  When the New File icon is used to create a new schedule, default.mtp is used.  Save any schedule template using the name default.mtp to have new schedules start with certain format. folder

.Template "ExcelTemplate1.mtp" 'visit our programmer's page to get this template

   'Loop through and build the schedule using information from the spreadsheet

For TaskNumber = 1 To 17

 SkipStartDate:

        On Error GoTo SkipEndDate

        temp = Worksheets("Sheet2").Cells(TaskNumber + 1, 4)

        temp = Format(Worksheets("Sheet2").Cells(TaskNumber + 1, 4), "mm/dd/yy")

        If temp > Format("1/1/90", "mm/dd/yy") Then

        .AddSymbol TaskNumber, temp, 2, 1, 2

        End If

        'compare task start date to current schedule start/end date

        newdate = (Worksheets("Sheet2").Cells(TaskNumber + 1, 3))

        If newdate < earliestday Then

        earliestday = newdate

        End If

        If newdate > latestday Then

        latestday = newdate

        End If

        'compare task start date to current schedule start/end date

        newdate = (Worksheets("Sheet2").Cells(TaskNumber + 1, 4))

        If newdate < earliestday Then

        earliestday = newdate

        End If

        If newdate > latestday Then

        latestday = newdate

        End If

SkipEndDate:

.Refresh

        Next

    .SetTitle1 "EXCEL SCHEDULE EXAMPLE"

    .SetTitle2 "Milestones Professional"

    .SetTitle3 "OLE Automation"

    .SetStartDate earliestday

    .SetEndDate latestday

    .Refresh

    .KeepScheduleOpen

End With  

Exit Sub

End Sub