OLE Automation Excel example


		Excel OLE Automation example #1

Here's a simple example of how you might use Milestones Professional's OLE Automation to take information from an Excel spreadsheet and generate an outlined schedule.

To try this example

The Spreadsheet:

The Milestones Professional Schedule:

The Excel Visual Basic Program:

Here's the Visual Basic Program that generates an outlined Milestones Professional schedule using the data in the Excel spreadsheet. The Milestones OLE calls are displayed in blue.

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 outlinelevel 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 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 personal templates folder

.Template "ExcelTemplate1.mtp"

'Loop through and build the schedule using information from the spreadsheet
     For TaskNumber = 1 To 17
       .PutCell TaskNumber, 1, Worksheets("Sheet2").Cells(TaskNumber + 1, 1)
       .SetOutlineLevel TaskNumber, Worksheets("Sheet2").Cells(TaskNumber + 1, 2).Value
       OutlineLevel = Worksheets("Sheet2").Cells(TaskNumber + 1, 2).Value
       If OutlineLevel < 2 Then
         GoTo SkipEndDate
       End If
       On Error GoTo SkipStartDate
       .AddSymbol TaskNumber, Format(Worksheets("Sheet2").Cells(TaskNumber + 1, 3), "mm/dd/yy"), 1, 1, 2

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

To try this example:

If you would like to try this example, click here to download a Winzip file which contains:

- MilestonesOLEExcelExample1.xls - The Excel spreadsheet with data and program

- ExcelTemplate1.mtp - The Milestones Professional template used by the program

If you would like to try this example, do this:

1		Download the MilestonesOLEExcelExample1.zip file
2		"Unzip" the file to a folder on your computer.
3		Copy the Template (ExcelTemplate1.mtp) to your Milestones Professional personal templates folder.
4		Start Excel and open the ExcelExample.xls file.
5		In Excel, choose Macro on the Tools menu, then choose Macros.
6		Click to select the "CreateOutlinedSchedule" macro.
7		Click the run button.

(This download is recommended for those familiar with Visual Basic and Microsoft Excel).

Click here to visit the Milestones Professional Programmer's page.

KIDASA Software, Inc. Website