Here’s a simple example of how Milestones Professional’s OLE Automation can be used to reformat a Microsoft Project schedule for printing on a single page on a large format plotter.

The Microsoft Excel spreadsheet:

The Milestones schedule:

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

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).

Here is a more advanced example of how you might use Milestones Professional’s OLE Automation interface with Excel.

Produces a schedule for each sheet in the Excel Workbook.Each sheet represents a condominium building project.

The Excel worksheet (shown below the schedule), includes:

• contractor name
• the type of contract (hook up gas, cabinets, …)
• Contract total $
• $ paid (used) to date
• % of work complete
• The program calculates the % of money remaining and compares it to the %of work remaining.
• The % of work remaining and % of money remaining are displayed as a pie symbol.
• The “performance indicator” column gives a quick way of telling whether the subcontract is in trouble (red), doing ok (yellow) or doing very well (green). The colors are based on how the % of work remaining compares to the % of money remaining.

The schedule

To try this example

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

MilestonesOLEExcelExample2.xls – The Excel spreadsheet with data and program
ExcelTemplate2.mtp – The Milestones Professional template used by the program

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

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

The Spreadsheet (partial)

The Milestones Professional Schedule:

To try this example:

1. Download the MilestonesOLEExcelExample3.zip file
2. “Unzip” the file to a folder on your computer.
3. Copy the Template (ExcelTemplate3.mtp) to your Milestones Professional Personal template 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).

Note: The DataGraph and ValueSet are defined in the template and are automatically generated using the numbers in the “Mail Qty” Column. The numbers are spread over the duration of the task’s time frame.

This example creates a spreadsheet from a Milestones Professional schedule.

The Excel Visual Basic Program:

Here’s the Visual Basic Program that generates a spreadsheet from an existing Milestones Professional schedule. This is a simple example which you may use as a starting point for building your own program.

Dim objmilestones As Object
Dim numberofrows As Integer
Dim filename As String

Sub CreateSpreadsheet()
Dim numberofrows As Integer
Dim taskrow As Integer
Dim taskname As String
Dim date1 As String, date2 As Date

'get the last file name used from the registry
filenamex = GetSetting("milestones", "testprogramfilename", "milesfilename")
If filenamex = "" Then filenamex = "c:test.mla"

filename = InputBox("Enter the name of the Milestones File you want to use", "Test VB Program", filenamex)
If filename = "" Then GoTo quitprogram

'save the new file name to the registry
SaveSetting "milestones", "testprogramfilename", "milesfilename", filename

'open the file, creating the Milestones object
Set objmilestones = GetObject(filename)

'activate milestones
With objmilestones
.Activate

'find out how many task rows there are on the schedule
numberofrows = .GetNumberoflines
If numberofrows < 1 Then
MsgBox "Milestones file doesn't have any information"
GoTo quitprogram
End If

'loop for each row on the schedule
For taskrow = 1 To numberofrows

' how many symbols are there?
numberofsymbolsontaskline = .GetNumberofSymbolsInLine(taskrow)
If numberofsymbolsontaskline < 1 Then GoTo nosymbols date1 = .getsymbolproperty (taskrow, 1, "Date") Worksheets("Sheet1").Cells(taskrow + 1, 2).Value = date1 If numberofsymbolsontaskline > 1 Then
date2 = .getsymbolproperty(taskrow, 2, "Date")
Worksheets("Sheet1").Cells(taskrow + 1, 3).Value = date2
Else ' put the startdate in the end date field if there is only one symbol
Worksheets("Sheet1").Cells(taskrow + 1, 3).Value = date1
End If

nosymbols:
taskname = .getcelltext(taskrow, 1)
If taskname <> "" Then Worksheets("Sheet1").Cells(taskrow + 1, 1).Value = taskname

Next taskrow

quitprogram:

.keepscheduleopen
End With

End

End Sub

To try this example:

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

1. Download the MilestonesOLEExcelExample4.zip file
2. “Unzip” the file to a folder on your computer.
3. Start Excel and open the MilestonesOLEExcelExample4.xls file.
4. In Excel, choose Macro on the Tools menu, then choose Macros.
5. Click to select the “CreateSpreadsheet” macro.
6. Click the run button.
7. When prompted, enter the name of an existing Milestones Professional schedule.

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

This example uses OLE Automation to generate schedule for a specific resource, taken from an Excel spreadsheet:

• User runs the VBA macro “MakeMiles”
• A form is populated with available Resource Names from the spreadsheet.
• User selects the desired resource name
• “MakeMiles” creates a Milestones schedule of that resource’s tasks.

The Milestones Professional Schedule:

To try this example:

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

1. Download the MilestonesOLEExcelExample5.zip file
2. “Unzip” the file to a folder on your computer.
3. Start Excel and open the MilestonesOLEExcelExample5.xls file.
4. In Excel, choose Macro on the Tools menu, then choose Macros.
5. Click to select the “MakeMiles” macro.
6. Click the run button.

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